In MS CRM, Use Workflow To Roll-Up Child Table Values To Parent

Summing values from a child table to the parent

I was recently working on an MS CRM Online project where I had to roll up some field values from products attached to an opportunity up to the opportunity itself (i.e., summing values from child table records up to a parent table).

Why use workflows?

And, since this project was a Microsoft Dynamics CRM Online project (i.e., not on-premise), I had to use workflows.  If this was a locally hosted CRM  project I probably would have used some code and not even explored using a  workflow to solve my problem. 

I’m actually quite happy this turned out to be an on-demand (online) project versus an on-premise project because it is making me use the MS CRM product in different ways. 

Three events to account for

It ended up being quite simple to sum up record values to the opportunity level using workflow.  I quickly realized I had three events  that I had to account for in order to do the roll-up: (1) adding products to an opportunity, (2) modifying products on an opportunity, and (3) deleting products on an opportunity.

Kicking off the workflow

The kick off of the workflow for all three events occurs at the Opportunity Product level, not the Opportunity level (i.e., at the child table level, not the parent).

The “adding” and “deleting” events

The event of “adding” a product to an opportunity and “deleting” a product on an opportunity was quite simple.

Here are the screen shots for the workflow on “adding” a product to an opportunity (click each image to see them in full size):

 

The key item is “increment by”

The key item to note in the screen shot above is that the “Operation” was set to “Increment by”, not “Set to”.  This will increase the opportunity’s commitments field by the opportunity product’s commitments field, and the same for the fees field.

And, here are the screen shots for the workflow of “deleting” a product from an opportunity (click each image to see them in full size):

 

The key items is “decrement by”

The key item to note in the screen shot above is that I am using the “Decrement by” operation on the fields, not the “Set to” or “Increment by” like in the previous example. 

The “modifying” event

The workflow to account for “modifying” the values of a product assigned to an opportunity is a bit more difficult.  I’m just going to talk thru the “modifying” example (i.e., no screen shots) because it should be clear with the two examples I gave above.

On the Opportunity Product form I added two fields.  These fields are for holding the original values of the commitments and fees fields before they are changed by the user.  When the Opportunity Product form opens I copy the values from the proper commitments and fees fields into these original value fields.  And, I add some jscript on the form opening to hide the two original value fields.

Then, for the “modifying” workflow I have it start whenever the commitments or fees are modified on the opportunity product.

When they are modified my workflow has two steps.  The first step updates the opportunity by decrementing the commitments and fees at the opportunity level by the original commitments and fees amount at the opportunity product level.

The second set updates the opportunity by incrementing by the commitments and fees at the opportunity product level (i.e., the newly modified values on the opportunity product).

Simple.  Clean.  Worked.

by Ron De Giusti