Calculating Aggregates with Dynamics CRM 2013 Real-Time Workflows

Real-time workflows are one of the most important new features in Dynamics CRM 2013. And one important new feature available only in real-time workflows is the option of running an automatic workflow before or after certain triggering events. These options are available for the record status changes, record is assigned, and record fields change events. It might be hard to tell in the following figure, but the other two triggers don’t give you a choice: record is created only has an After option, and record is deleted only has Before.

 

Dynamics CRM Before

 

 arrow

 

 

Before and After options available for these triggers only

 

 

These new options have lots of applications, and one important one is in the calculation of aggregates, like running totals on a parent record rolled up from numeric fields on child records.

Why does it Matter?

In Dynamics CRM 2011, the Before option wasn’t available for any of the triggers. So why is that important? It turns out that there are lots of situations when it’s valuable to know what something was before it changed. For example, if a record is being reassigned it might be just as important to notify the previous owner as the new owner, but in Dynamics CRM 2011 the only clean way to do that was by writing a custom plug-in.

Another business requirement where it’s important is when you need to calculate running totals on a parent record, based on data contained on child records. Let’s consider an xRM application of Dynamics CRM, to satisfy some common event/registration management business requirements.  

Suppose you have an Event custom entity with a 1:N to a Registration entity, and you want to track totals on Event records for Registration Counts and Registration Fees. According to the business users, there are two important situations that should trigger the calculation of the totals on the event records:

  1. An open registration is closed. A registration record can be closed for two possible reasons:
    1. It can close as Confirmed, in which case we want to update the total fields on the Event record.
    2. It can close as Canceled, in which case we do not update the total fields.
  2. A previously closed registration is re-opened. After it’s closed, a registration can be re-opened, and whether the Event record should be updated depends on how the record was previously closed:
    1. If the registration was closed as Confirmed, we need to back out its previous contribution to the registration count and registration fees fields on the event record.
    2. If it was closed as Canceled, it didn’t contribute to the total fields, so we do not want to update the event record.

For the discussion that follows, the most important technical detail is how we implement the distinction between “confirmed” and “canceled” registration records. For these requirements, I’d use the system Status Reason field on the Registration entity, which you can think of as having an N:1 relationship to the Status field. That is, for each of the two possible values Status can have (Active and Inactive), Status Reason can have multiple values. To get at the essentials of the problem let’s model it like this:

Status Field Value

Status Reason Values

Active

In Progress

Pending

Inactive

Confirmed

Canceled

 

With that in mind, let’s see how the improved granularity of the Dynamics CRM 2013 real-time workflow event model can help with business requirements like these.

The Importance of Before

To appreciate the importance of the Before option, let’s see how far we get without it.

The asynchronous workflow processes we had in CRM 2011 could handle the “close” part of the business requirements discussed above, with an automatic workflow triggered by status change on the Registration entity. If status reason is Confirmed, perform the updates. If status reason is Canceled, don’t do anything.

But in CRM 2011 the problem was how to handle the “re-open” situation. There was no clean way to do it, because by the time the workflow ran, the status (and status reason) was changed, and we didn’t know what it was previously. Without a Before option, there’s no concept of previous. Basically, you know what you know in the present, but have no idea about anything that happened before. More along the lines of Fifty First Dates than Remembrance of Things Past.

Another way of thinking about this is summarized in the following table. If all we have is the After option, we know everything about the current values of status and status reason, plus we can infer the previous value of status (since it only has two possible values). But we don’t know anything about the previous values of status reason, which for the current set of requirements is exactly what we need to know!

Current Values

Previous Values

Status

Status Reason

Status

Status Reason

Active

In Progress or Pending; can test for value and take action

Must have been Inactive

Don’t know

Inactive

Either Confirmed or Canceled; can test for value and take action

Must have been Active

Don’t know

 

Solving the Problem

The key is that we need two separate automatic real-time workflows, both of which are written for the Registration entity and triggered by the Record status changes event. We’ll review these next. The first one (Registration Close) records a closed registration’s contributions to the event total fields and runs after the status changes. The second one (Registration Re-Open) backs out a previously closed registration’s contributions and runs before the status changes.

 

 

 

Here’s the Registration Close workflow process:

Workflow Properties:

 

Runs on the After option of the Record status changes trigger. So by the time it runs we know the current value of the status reason field.

 

 Registration close

Workflow Steps:

 

First we check the status. If it’s inactive we check status reason. If it’s confirmed we update the total fields on Event.

 

 Is Closed?

Step Properties:

 

In the Update Event step, increment the attendee count by 1, and the Total Revenue (Event) field by the Fee field from the Registration record.

Dynamics CRM Event

 

That was the easy part. The Registration Re-Open workflow gets more interesting:

Workflow Properties:

 

Here we need to tap into the new Before option on the Record status changes trigger. Effectively, we’re checking for what the “previous” value of status reason was.

 

 Registration re-open

Workflow Steps:

 

If the previous value of status reason was confirmed, we know we need to update the event totals.

 

 Was closed?

Step Properties:

 

Use the dynamic values Decrement operator to back out the attendee count and registration fee values that were previously recorded.

 

Dynamics CRM Values

 

With those two workflow processes activated, we can always have an update and accurate set of aggregate values available on the event form. In the following figure, the attendee count and total revenue fields are read only, so the only way they can be updated is by the workflow processes. With a little bit of form tweaking (more than I have time for now!) you could place the total revenue field in a separate section underneath the Registrations sub-grid to make it more obvious that it’s the sum of the fee fields for confirmed registrations, and even make separate sub-grids for the pending, in progress and canceled registrations. But the un-tweaked version illustrates the main point for this article:

Covet

 

Extensions

This is a good start, but of course there are plenty of additional requirements you may encounter out there in the complicated real world. Here are a couple, which may appear in future blog posts, but will definitely be in my upcoming book on Dynamics CRM 2013 business processes and no-code customizations. 

What about Deletes?

If a confirmed registration is deleted, an automatic workflow on the record is deleted trigger can easily enough decrement the aggregates, just like the Registration Re-Open process we reviewed above. But I hear the cool kids are doing it a different way these days: they’re using conditional logic to check if the record being deleted is a confirmed registration, and if so they’re canceling the process, presenting the user with a custom error message, and rolling back the whole transaction. Bam!

Opportunities and Actual Revenue

The registration entity used in this example seems kind of like an opportunity, right? In fact, one good way to model this would be to use opportunities rather than a custom entity; for example, you might have an opportunity type option set, and when you select the Event option, a custom lookup field to the Event entity is displayed on the opportunity form. That would be a good use-case for a business rule, and you could even set it to required depending on your requirements. 

But if you use the approach described in this article to calculate event aggregates it won’t work for opportunities, because of some special system-level logic built into the Actual Revenue field. I won’t go into the details on that here, but if you want to give it a shot, do it like this: rather than using the record status changes trigger as we did here, use the record fields change trigger for the Actual Revenue field. If actual revenue contains data after the event, you know the opportunity has been closed (the current status value is either Lost or Won). If actual revenue contains data before the event, you know that it’s being re-opened.

Wow. How’s that for a big finish?