Microsoft Dynamics® CRM Training
Calculating Aggregates 02
An error occurred while loading the video. Please try again.
This is the second video of a two-part lesson. In part one, we created a boiled-down version of a Goal that had no real time-period, and no target. This allowed us to calculate the aggregate sales totals generated by an Account record called “Adventure Works”.
In this video, I want to place this calculated number right on the Account record. That way, I won’t have to open the Goal record to view the total sales. To do that, I have to make the Goal a child record of the Account, and then create a Workflow that populates a custom field on the Account record. These tasks require a Security Role of System Administrator, System Customizer, or equivalent privileges.
The first thing I want to do is relate the Goal to the Account. To do that, I create a custom lookup field for Account records in the Goal entity. I’ll open the Goal we created in the first video called “Adventure Works All-Time Sales”. In the Customize tab of the ribbon, I’ll click Form.
When the form editor opens, I’ll click the New Field button in the bottom right corner. The Field form opens, where I’ll name this custom field “Account”. In the Type field, I’ll select “Lookup”, and then select “Account” in the Target Record Type field. I’ve configured the field, so I’ll click Save and Close.
Back at the form editor, I’ll drag the new Account field onto the form. Now, I’ll save and publish my changes. Once the customizations are published, I’ll close and reopen the Goal record. I now see the Account field, and in it, I’ll select “Adventure Works”. I have now made Adventure Works the parent record to this Goal.
Now, I need to create a field on the Account record that will display the actual money total of the Goal. I’ll save the Goal, and then open the Adventure Works record from here by clicking its name in the Account field.
When the Adventure Works record opens, I’ll create the new field by opening the form editor. I’ll click the New Field button in the form editor. In the Field form, I’ll give it the name, “All-Time Sales”. In the Type field, I’ll select “Currency”, and then select “2” in the Precision field. Click Save and Close, and then drag the new field onto the form. I’ll save and publish the changes, thus making this new field available in the Account record.
So far, I’ve make the Account the parent record of the Goal by creating a lookup field. I’ve then created a custom field for the Account entity which will display the all-time sales for the Account. I still have to create a Workflow that will automatically populate the All-Time Sales field.
I’ll close the records and then navigate to Settings > Process Center > Processes. In the toolbar, I’ll click New. When the Process dialog opens, I’ll name it and select “Workflow” in the Category field. Now, for the Entity field, I want to select “Goal”. Even though were going to read the information on the Account record, it’s the Actual (Money) field in the Goal record that will trigger this process. Now, I’ll click the OK button, thus closing the dialog and opening the Process record.
In the Options for Automatic Processes area, I’ll set the Scope to “Organization” so all users can have access to this Workflow. In the Start when field, I’ll check the Record fields change box only, and then select Actual (Money) in the Select Fields dialog. This means the process will run only when the aggregate total is updated.
Now, I can begin to add steps to the process. The first one will a Check Condition step. I’ll click the link to configure the step. What I want this step to do is make sure the Goal is associated with an Account in the first place. In the Specify Condition dialog, I’ll set the query to “Goal, Account, Contains Data”, and then click Save and Close.
I’ll then click Add Step and select “Update Record”. I want this step to update the All-Time Sales field of the Account record with the data from the Actual (Money) field calculated in the Goal. In the dropdown, I’ll select “Account”, and then I’ll click the Set Properties button.
The Account dialog for the Information form opens. I don’t see the All-Time Sales field here because I added it to the flow form, not the old one. However, if I scroll down to the Additional Fields area, I’ll find it.
Once I locate the field, I’ll click it, and then select “Goal” and “Actual (Money)” in the Look for dropdowns on the right. Next, I’ll click Add, and then the OK button so that it should read, “[Actual (Money) (Goal)}”in the All-Time Sales field. I’ll click Save and Close to return to the process.
My Workflow is configured, so I’ll save it and then Activate it. Once it is activated, I’ll test it out.
I’ll go back to the Sales module and open the Goal record. Remember, the process is triggered when the aggregate is updated. I have already closed a sample Opportunity record as “Won”, and it has yet to be factored into the actual money for this Goal. Goal records recalculate automatically every 24 hours, but I can also do it manually in the ribbon.
Now that the Goal has been updated, I’ll give the Workflow a moment to run, and then I’ll open the Adventure Works record.
Sure enough, the All-Time Sales field has been automatically populated with the most recent aggregate.
We have successfully used the Goal as a calculation mechanism. Thanks to the custom fields and Workflow, I don’t have to open a separate record to check the Account’s all-time sales.
For more tips, tricks, and tutorials, please refer to the Success Portal and blog.xrm.com.