Have more fun with workflows in Microsoft Dynamics CRM 2011. This handy tip on how to roll up values from child records to parent records will help save you custom coding and utilize the power of your CRM.
Say you have a numeric or currency field on an entity where there is a Parent Child relationship (Accounts for example) and you need to calculate or Rollup values from the Child Records to the Parent Record. This can certainly be accomplished via some custom coding; but what if you are not a programmer? Well, you’re in luck with the addition of a hidden field and a single workflow, this can be accomplished.
In this example, we have a field on the Account record called Quota. The goal is to have the Quota field on the Parent Record be the sum of the Quota for all Child Records. Initially, this seems fairly straight forward; however, we need to account for the scenario where the Quota of a Child record is modified. If the Quota field is changed from $10,000 to $5,000, we need to Decrement the Parent Record by $5,000. Since the previous value is no longer available to us in a Workflow, this is a little tricky. We need to create a field that will hold the Previous Value ($10,000) prior to when it was changed to $5,000.
Here are the step by step instructions on how to accomplish rolling up the values to the Parent Record:
Step 1 Create a field on the Account Record to hold the “old” value. In this example, we named it “Quota TEMP”. We also created a “Two Option” (bit) field to allow us the ability to Enable or Disable Rolling Up the totals to the Parent Account.
The Quota TEMP field must remain Visible on the Form until after the workflow is built. Once you’ve tested and confirmed workflow is working properly, I recommend “hiding” the field on the form as users will not need to see it.
Step 2 Create a Workflow on the Account Entity that will execute when a “Record Is Created” OR when either the ‘Quota’ or ‘Roll Quota to Parent’ fields are changed.
Now, add Conditional Step to check if the ‘Roll Quota to Parent’ = Yes.
If Yes, then we need to Decrement the Quota Field of the Parent Record by the value in the “Quota TEMP” field (aka the “Old” value).
Next, we need to Increment the Quota Field of the Parent Record by the “New” value in the “Quota” field.
Last, we need to set the value of the Quota TEMP field of the Child Account to equal the current value of the Quota field. (This step will allow the workflow to access the “previous” value when the Quota field is changed.)
Here is an outline of the steps of the workflow. Notice that in the first two Update Steps, we are updating the “Parent Record”, and in the last Update Step, we are updating the “Child Record”.
Here is an example of what will happen when there are 2 child records:
- Parent Quota starts out at Zero.
- Two Child Records are created/edited with a Quota of $10,000 each and the “Roll Quota To Parent” field is checked.
- The Workflows Execute and Increment the Parent Quota by $10,000, then sets the Quota TEMP field on the Child record to $10,000 for future use. This results in the Parent Quota being set to $20,000.
- Next, a user edits one of Child Records Quota to $5,000.
- Again, the Workflow executes and Decrements the Parent Quota by the value in the Quota TEMP Field (old value) of $10,000, then Increments the Parent Quota by $5,000 resulting in a new Parent Quota value of $15,000.
NOTE: If you are implementing this process in a New Deployment or where the Quota data is not currently in CRM, this process will work perfectly.
If you have existing data, we recommend that you contact the CRM Experts at PowerObjects to discuss the best way to implement this process.
I hope you found this post helpful! Happy CRM’ing!!