cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewtKris1
Advocate I
Advocate I

Calculate sum of subgrid items and populate parent field within web form/page.

Hello - I have a web form page with several subgrids, each of them representing a budget category where the user can add several items. I would like to sum the total of each subgrid (budget category) and then have that category total show in a field within the parent form, on the same page. I would then like to take the total of all subgrids and provide a budget total. 

 

I have tried the following and have tried to achieve via power automate, with no luck on either. I have a project deadline coming up very quickly and it seems like this should be easier than I'm making it out to be.

 

javascript I've tried:

function AssignSumValue(){

var total_amount = GetSum("Personnel");

Xrm.Page.getAttribute$('#crd9a_gm_solicitations_sumofpersonnelcost').setValue(total_amount);

}

function GetSum(Personnel){

var sum = 0.0;

var subgrid_rows = Xrm.Page.getControl(Personnel).getGrid().getRows();

subgrid_rows.forEach(function (record){

sum += parseFloat(record.getData().getEntity().getAttributes().get$('#crd9a_gm_solicitations_budget_personnelcost').getValue());

});

return(sum)

}

 

subgrid_example1.pngbudgettotal_example1.png

1 ACCEPTED SOLUTION

Accepted Solutions

Thank you for responding. Ultimately, I went with another solution because I do need the fields to calculate instantly. I appreciate your time.

View solution in original post

4 REPLIES 4
NewtKris1
Advocate I
Advocate I

@OliverRodrigues any ideas? 😬

OliverRodrigues
Super User
Super User

Hi @NewtKris1 have you looked into calculated/rollup columns? Calculated and rollup columns (Microsoft Dataverse) - Power Apps | Microsoft Learn

this might give you the calculation that you need, however this won't happen sync, it would take some time for the data to refresh

there are a few tricks to enforce the calculation via plug-in development

if you need this information to be available in the parent record, I wouldn't be looking at JavaScript, I think the solution is server-side




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Pages Super User | MVP


Oliver Rodrigues


 

Thank you for responding. Ultimately, I went with another solution because I do need the fields to calculate instantly. I appreciate your time.

Fubar
Solution Sage
Solution Sage

Just for anyone else that finds this thread.  It can be done in JavaScript (but in many scenarios it may be better not to).

I am in the process of fixing up issues left by another Vendor where they attempted to do this but completely stuffed it up and approached it incorrectly, so cannot reveal all the details at the moment, but what you need

  • On the main form (not the subgrid meta data)
  • create a subgrid loaded jquery block (possibly not needed if you need it to trigger on intiial load of the main form)
  • inside that block get the node that is the subgrid Table tag
  • still inside that block create a MutationObserver (inside the mutation observer call another JS function that makes an ajax call to get the new totals) - this is what picks up changes to the subgrid, if you add a new item etc the Table that is the subgrid gets reloaded so triggers a DOM mutaion.
  • Also in my scenario I have to accommodate skipping the intial form load (as this triggers the mutation observer)

The code would be something like the below

 

(document).ready(function () {	
	var initialFormLoad = true;
	
	$('#subgridname').on("loaded", function () {
	   // will trigger on Add, Update, Delete and intitial population
	  var nodeToObserve = <get the table node of the subgird here>;
	  var observer = new MutationObserver(function(mutations) {
  	     if(!initialFormLoad){				
		YourRecalcFunctionHere();
	     }			
			
	  });

	  observer.observe(nodeToObserve, {
	    childList: true,
  	    subtree: true,
	    attributes: false,
  	    characterData: false,
	  });
  
	  initialFormLoad = false;  // initial subgrid load on form load complete.
    });
	
});

 

  •  

 

 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (3,983)