Using Workflows to Calculate Field Values

Dynamics CRM does not have a built-in “calculated” field type, but you can use workflows and the Multiply by and Set to operators to get something close to it. For example, the Opportunity entity has built in attributes “Est. Revenue” (attribute name “estimatedvalue”) and “Probability” (attribute name “closeprobability”). Suppose you wanted a “probability-weighted” revenue forecast. You can create a custom attribute, and then use a workflow to multiply the built-in fields by each other to update the custom attribute. Here are the three attributes I will use to illustrate, all on the Opportunity entity:

• Est. Revenue (entity name estimatedvalue, type money)
• Probability (entity name closeprobability, type int)
• Weighted Revenue. This is the custom attribute. Name it what you like and give it a type of money. 

After adding the custom attribute, we need to build a workflow to update its value with the product of the “Est. Revenue” and “Probability” attributes. The Multiply by operator works by multiplying the current value of a field by a single numeric value. What this means is that you cannot enter a formula as you might think. My inclination was to have a workflow update the weighted revenue value with something like this:
   =(est. revenue)*(probability)

Unfortunately it’s not quite that easy! Since the Multiply by operator only allows you to update the current value by multiplying it by something else, the workflow needs to perform three consecutive Update Record actions:

1. Update weighted revenue with the current value of Est. Revenue.
2. Update its new value by multiplying it by Probability.
3. Update its new value by multiplying it by .01 (since Probability is an integer value!)

The next figure shows a simple example of this, with three Update actions generously commented to make it somewhat self-documenting.
 

The next figure illustrates how to configure the Update Record action for step 2. In step 1, the Weighted Revenue field was updated with the value of Est. Revenue, so in step 2 we need to multiply it by Probability:

 
After that, the Update Record action in step 3 will account for the fact that Probability is an integer (e.g., 50% is represented by the integer value 50). To do that, multiply by .01, as we illustrate in the next figure.
 
Here are a few comments and suggestions in case you need to implement functionality like this:

• The reason it takes three Update Record actions to perform the calculation is that you’re always updating the current value of the field, and you can only perform one operation at a time. It would be better if you could use formulas, but you can’t. 
• You can imagine a more complicated calculation might take many more Update Record actions. This might become clunky or impractical in the workflow approach I discussed here. If it does, it might turn out that using client-side (Jscript behind form events) is a better approach. While some people might argue that it’s ALWAYS better to use the client-side script approach, I don’t agree with that. For a problem like the one we’re trying to solve here, you can use either, and which one is better depends on your requirements…as well as your skill-set!
• Another disadvantage of the workflow approach to calculating fields is the latency problem: Dynamics CRM workflows run asynchronously, one implication of which is that calculated fields will take some time to update on the form, generally from 15 to 30 seconds. If you use the workflow approach, you will probably want to take the calculated field off the form, or maybe “hide it” on another tab.
• How should a workflow like this one be triggered? You might need to run it manually for records that were created before you implemented it. On an ongoing basis you might want to run it when a new record is created, or when the Est. Revenue or Probability attributes change. Again, the specifics will be determined by your business requirements. This figure shows what would be a reasonable configuration of an automatic workflow to do this: