Rollup Summary field is a great tool in Salesforce, but as other things it have limitation.
The total rollup fields per objects is currently 25 (can be increased by support to 40 in some cases) and it can be used only in master-detail relationship, which might make it part of the design while deciding the object model.
There are some great apps that extends the standard capabilities and allow to overcome the limitations. 2 of the most commons are DLRS (Declarative Lookup Rollup Summary) and Rollup Helper.
Recently I came across similar case while developing app. In my case I provided tool for building custom process for update/create records, and while setting the record values user can use formula with functions. I wanted to provide complex function - rollupCalculation - where user will input the relevant parameters and the tool will do the rest.
It appear that such calculation is not really complicated as it seems at first. You might need to invest some time in the user setup- can either ask from users to input directly field/object API names or you can build special component that show the labels.
But eventually for the calculation you basically only need few inputs:
1.Child object to aggregate.
2.Lookup field from the child to the parent.
3.Aggregation function. Can be either Count/Sum/AVG/MAX/MIN
4.Field for summary, if the function is not Count.
5.Optional: where criteria for filtering the child records.
6.Parent object/field to store the result.
Your logic for calculation might look something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | public static void calcualteRollup( String childObjectName, String aggregateFunction, String aggregateField, String parentFieldLookup, String additionalCriteria, String parentFieldForResult, list<sObject> parentRecordList){ //Set function + agg field for query String funcWithField = aggregateFunction + '(' + (aggregateFunction == 'Count' ? 'Id' : aggregateField) + ')'; //get parent Ids String inValues = ''; for(sObject rec : parentRecordList){ inValues += '\'' + rec.Id + '\','; } String groupSoql = 'select ' + parentFieldLookup + ',' + funcWithField + ' from ' + childObjectName + ' where ' + parentFieldLookup + ' IN (' + inValues.removeEnd(',') + ')' + (String.isBlank(additionalCriteria) ? '' : ' and (' + additionalCriteria +')') + ' group by ' + parentFieldLookup; System.debug('groupSoql: ' + groupSoql); //Store the records input in map to easy the access map<Id, sObject> recordsMap = new map<Id, sObject>(); for(sObject rec : parentRecordList){ recordsMap.put(rec.Id, rec); } for(AggregateResult ag :database.query(groupSoql)){ recordsMap.get(String.valueOf(ag.get(parentFieldLookup))).put(parentFieldForResult, ag.get('expr0')); } update parentRecordList; } |
Some issues require more attention and special treatment, like records without any aggregation result (in some cases you will want to set the parent field as zero and maybe in other set it to null), or support large volume of records (both the records to be updated and the child records to count which might require batch process), but as you can see from above code the general logic is not that complicated. Sometimes when you need only 1-2 custom rollups you can do it by yourself
When/if it become too complicate you can always use 1 of the external apps shown below or try the following tool which have much other capabilities expect the rollups.
No comments:
Post a Comment