Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / Total amount of a dyna...
Microsoft Dynamics 365 | Integration, Dataverse...
Answered

Total amount of a dynamic filtered data in a sub-grid

(0) ShareShare
ReportReport
Posted on by EBMRay 873

Hello everyone,

In the opportunity table, we have a sub-grid that will show the Opportunity Line item records.
For each product, we have different prices and below the grid, there is the Opportunity Product Amount calculation that will do the sum of all the products.

I have added a field on the opportunity form called "Filtered Total Amount" as follow:
Grid-with-all-dataaa.png

What I want to achieve is the below:

I want to filter the grid based on the product name, and upon filtering the data the "filtered total amount" field should be the sum of the filtered products.

For example: When I filter by Product Name which is "D365 Online Professional", the filtered total amount should automatically do the sum of the filtered data and show the calculated result.
Filtered-Grid-Data-Calculation.gif

I need to do this kind of calculation because in the OOB functionality I didn't find any way to calculate the sum of the product based on a filtered grid view.

How can we calculate the total amount and set the value on the field based on any filter applied for a product to calculate the sum from within the sub-grid?

Is it possible to achieve the above scenario? Please provide some helpful resources to build this solution and I would highly appreciate it if you can provide an example or a sample code.

Any help is highly appreciated.

Best regards,

EBMRay

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Total amount of a dynamic filtered data in a sub-grid

    Hi EBMRay,

    Even if we set the "cr884_filteredtotalamount" field to null when saving the record or onLoad of the opportunity form, the value of this field would also be changed to the total amount of opportunity lines.(The subgrid onload event would be triggered later than the onLoad of the opportunity form.)

    About your consideration, I think this is no need . Because this value is used to display the current total amount of opportunity lines. When the subgrid is first  loaded, there is no product filtered, so it should be the total amount of  all the opportunity lines. And it would not be used for your business, so It doesn't matter what value is stored.

    If you still want to do this, you could create another field(For example, named "cr884_savedfilteredtotalamount") to store in the Dataverse. When the record opportunity is saved, assign the value of  "cr884_filteredtotalamount" to "cr884_savedfilteredtotalamount" by onSave event or workflow. But still that opinion, it's no need.

  • EBMRay Profile Picture
    EBMRay 873 on at
    RE: Total amount of a dynamic filtered data in a sub-grid

    Hi

    Thank you so much for providing a sample code.

    I replicated the same as mentioned and it is working perfectly.

    Will it be possible to reset or set the value to null of the "cr884_filteredtotalamount" upon saving the record or onLoad of the opportunity form?

    A case that might happen is that a user will filter the grid, do some modifications on the form and save it. When he get back one day to this record he will be confused about the total value saved on filteredtotalamount.

    Looking forward to your response.

    Best regards,

    EBMRay

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Total amount of a dynamic filtered data in a sub-grid

    Hi EBMRay,

    You could bind an onload event to the subgrid. Please try this code, it could get all you attributes in your subgrid and you need to change your field at line 19.

    function onload(executionContext){
    formContext = executionContext.getFormContext();
    //get gridControl
	gridControl = formContext.getControl("opportunityproductsGrid");
	gridControl.addOnLoad(myFunction);
}

function myFunction(){
	let myGrid = gridControl.getGrid();
	var myRows = myGrid.getRows();
	var filtered_total_amount;
	myRows.forEach(function (row, index){
	    // get all attributes in a row
		var attributes = row.getData().getEntity().attributes;
		// get each attribute in a row
		attributes.forEach(function (attribute, index){
			console.log("Name:"   attribute.getName()   " , Value:"   attribute.getValue());
			//change to your field name
			if(attribute.getName() == "[Opportunity line amount's logical name]"){
				filtered_total_amount  = attribute.getValue();
			}
		})
	})
	formContext.getAttribute("cr884_filteredtotalamount").setValue(filtered_total_amount);
}

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans