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

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):

OnAdd workflow information

 

OnAdd update opportunity

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):

 OnDelete workflow information

OnDelete update opportunity

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

About these ads
  1. -e
    March 23, 2010 at 6:34 AM

    nice. i like simple and clean that uses core tools…way to easy for some of us to just take the “coding” route… :)

    • March 23, 2010 at 6:43 AM

      I like the simple route here too because it makes it a lot easier for the client to maintain as well! Thanks for the comment.

  2. PIERRE
    May 5, 2010 at 8:24 PM

    I tried your steps. My only issue is my original values show no value in the workflow yet there is a value on the child record form. Any ideas why the original value (to update the parent) does not have a value and therefore is not decrementing?

    P

    • May 5, 2010 at 11:58 PM

      Hi Pierre,

      Do you have fields on your child record form that hold the original values? I created some fields on the child record form to hold the original values and when the form opens I added some jScript to copy the field values into these orignal value fields. That way when new values are entered into the fields I have the original values in those other fields that I populate when the form opens. Try not hiding the original value fields so you can see whether they are populated with the original values when the form opens.

      Was that helpful?

      Ron

    • Mike
      July 21, 2010 at 1:49 AM

      It may be as simple as setting ForceSubmit = true on your hidden original-value fields, to ensure their values are saved on form submission.

  3. Rich Mephan
    October 13, 2010 at 5:12 AM

    Guys,

    Can this not be done in a slightly simpler way ? Using only two workflows and less jScript ?

    Workflow 1 – Fires on record create and when Record Fields change –
    Decrement Parent by “Original Value” – (Would be Zero when fired on the initial create)
    Increment Parent by “Value”
    Update Child – Set “Original Value” = “Value”

    Effectively this Workflow is then doind the job of updating the parent on both a create and edit.

    Workflow 2 – Fires on Delete is as per the original example ….
    jScript to Hide “Origianl Values” as per the original example ….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 495 other followers

%d bloggers like this: