Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


How to Roll up Values from Child Records to Parent Records via Workflow in Microsoft Dynamics CRM

Post Author: Joe D365 |

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.

Workflow in Microsoft Dynamics CRM

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.

Roll up Values from Child Records to Parent Records

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!!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

3 comments on “How to Roll up Values from Child Records to Parent Records via Workflow in Microsoft Dynamics CRM”

  1. This is a useful post that I've got several places that I'm currently using external processes that need to be removed.

    One thing you don't consider in the workflow, which may be by design, is what happens if you un-tick the [Roll-up Quota] checkbox. In that case I think you'd want to decrement the parent record by the amount in [Quota TEMP] and then set [Quota TEMP] to zero.

    Cheers,
    Adrian

  2. i am trying , as i am not a developer or any kind of professional >>>> its still hard for me to understand .Do i need to actually customize the acount from to add the quota option. I am using dynamics CRM ONLINE .Can you explain me me about going through Goal Management . I was trying using administrator guide for quotas but it gave an error "
    " Quotas will not be available in the next release. We recommend that you use goal management instead ."

    How do i go about it .
    Regards
    Rohan Stanny lopes

  3. Hi,
    I would just like to add one remark for CRM2011: If you bulk edit the child records, then the end result will not be correct...
    I just tested this with the quick creation of 5 child records, which had a "volume" of "100". After the creation, the "total volume" on my parent was correctly "500".
    But if i bulk edit the 5 child records to change the volume to 1000 each, my "total volume" on my parent ends with "2300", although all 5 workflows were all executed...
    I don't think there is a workaround for this, since this is all dependent on how workflows are "simultaneously" executed by the system?

PowerObjects Recommends