Dynamics CRM 2011: Rolling up subgrid totals to a form field

MSCRM does require a little bit of coding when we try to add form field values. A common scenario is the addition of two inputted field values to make a third calculated value. This is easily achieved by using the method "Xrm.Page.getAttribute('ATTRIBUTE NAME').

For example the following method, when invoked in a form's OnSave event will add two floating point fields "new_firstvalue" and "new_secondvalue" and set the value of the field "new_calculatedthirdvalue":

function calculateThirdValue ()

{

var FirstValue = Xrm.Page.data.entity.attributes.get("new_firstvalue").getValue();

var SecondValue = Xrm.Page.data.entity.attributes.get("new_secondvalue").getValue();

varCalculatedThirdValue = parseFloat(FirstValue )+parseFloat(SecondValue);

Xrm.Page.data.entity.attributes.get("new_calculatedthirdvalue").setValue(varCalculatedThirdValue);

}

Lately I needed to do something a little different. I wanted to roll up values from a subgrid view embedded in my form. So after a little experimentation, I came up with this method which can be re-used for different subgrid columns. So in my OnLoad even I invoke a method like this:

function calculateRolledUpTotals()

{

//Calculate total of column 1

var col1Total = aggregateGridFields('new_column1');

Xrm.Page.getAttribute('new_column1total').setValue(parseInt(col1Total ));

Xrm.Page.getAttribute('new_column1total').setSubmitMode('always'); ...

}

and this re-usueable method then totals the subgrid values for the column pass to is by name:

function aggregateGridFields (columnName)

{

var gridControl = document.getElementById('queryset').control;

var ids= gridControl.get_allRecordIds();

var aggCount=0;

for(i = 0; i < ids.length; i++)

{

var cellValue = gridControl.getCellValue(columnName,ids[i]);

if (parseInt(cellValue))

{

aggCount+=parseInt(cellValue, 10);

}

}

return aggCount;

}

1. Justin says:

Only problem is this only calculates what's currently showing in the grid. How do we calculate ALL items, not just those on the current page?

2. Shaun says:

Hi, what does 'queryset' refer too?

Is it the name of the entity your working on, or the name of the entity is sub grid refers too?

3. DermotB says:

Hi Shaun, queryset is a bit like a returned dataset in ADO, so it's the set of records in the subgrid – and then the loop totals the relevant value.

@Justin – sorry I didn't reply back when you made that comment – but off the top of my head I think you'll need a database query to pull all the records.
{"ft_lang_label":"__label__en","ft_lang_prob":0.7571573,"math_prob":0.77767503,"size":2348,"snap":"2019-43-2019-47","text_gpt3_token_len":539,"char_repetition_ratio":0.10921501,"word_repetition_ratio":0.0062305294,"special_character_ratio":0.22146508,"punctuation_ratio":0.16067146,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.95118994,"pos_list":[0,1,2,3,4,5,6],"im_url_duplicate_count":[null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-10-21T19:17:41Z\",\"WARC-Record-ID\":\"<urn:uuid:9ea93ee0-bf4b-4a9a-99db-db9021a2f18c>\",\"Content-Length\":\"63339\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:cc3f6ffd-8c81-4c8f-860f-1fb1acee7078>\",\"WARC-Concurrent-To\":\"<urn:uuid:333224b6-21b7-4b3c-903a-675099e2868c>\",\"WARC-IP-Address\":\"23.39.182.93\",\"WARC-Target-URI\":\"https://blogs.msdn.microsoft.com/dboyle/2012/07/05/dynamics-crm-2011-rolling-up-subgrid-totals-to-a-form-field/\",\"WARC-Payload-Digest\":\"sha1:NGEVAA3GQQ3KUBLN4SCH52FIKQPWMTQL\",\"WARC-Block-Digest\":\"sha1:QHBYODJCBEI5USYQUWLJWXIIA7WGNTQE\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-43/CC-MAIN-2019-43_segments_1570987781397.63_warc_CC-MAIN-20191021171509-20191021195009-00023.warc.gz\"}"}