If you haven’t worked with the calculated and rollup field types in MSCRM, you’re missing out! They really help to automate calculations and provide an easy way of doing tasks that you previously had to do with JavaScript or workflows. I’m going to review a few examples of calculated and rollup fields that I have found useful for many clients.
Sometimes you may want to know how many child records a customer has. For example, how many opportunities have you had with a particular client and moreover how many were won vs. lost. Management may want to know how many open or active quotes are currently out to customers. You can quickly get this number by using a rollup field. My example will demonstrate how to get the number of won opportunities for an account.
- Create a new field on the account. The Data Type will be Whole Number and the Field Type will be Rollup.
- When you click on Edit next to the field type, the field will be created a new window will open where you can set the criteria.
- Under Related Entity, choose Opportunities (Account).
- For the filter, set it to look at only opportunities with a status of Won.
- Finally, for the aggregation set it to Count of Opportunity.
Here is what the rollup settings look like when finished. You could also do something similar to sum the total revenue across all won opportunities.
The field will update automatically every evening when a scheduled system job runs. You can also manually update the field by clicking on the small refresh icon.
