Have you ever wanted to calculate a value leveraging a one to many relationship in Microsoft Dynamics CRM? With the recent release of Microsoft Dynamics CRM 2015, you can easily do this using Rollup Fields. Rollup Fields allow you to create fields based on formulas commonly used in Microsoft Excel such as SUM, MIN, MAX and COUNT. Below are some examples of how Rollup Fields can be used:
- Last Appointment Date: shows the most recent appointment completed at an account
- Total Revenue: provides the total revenue for all closed won opportunities linked to a particular account
- Total Open Cases: displays the number of distinct cases currently open related to an account
Before getting started creating Rollup Fields, there are some limitations to keep in mind. First off, Rollup Fields can only be created for Whole Number, Decimal, Date & Time or Currency fields. There are also limitations to the available formulas for each data type:
- Whole Number: COUNT
- Decimal: COUNT, MAX, MIN, SUM
- Date & Time: MAX, MIN, SUM
- Currency: MAX, MIN, SUM
Once you have identified the data type and formula you want to use for your Rollup Field, you’re ready to get started. For our example, we’ll create a new field to show the Last Appointment Date for an Account. To create the Rollup Field, begin by creating a new custom field:
Be sure to select the Data Type and Field Type (Rollup) before saving the new field. Then click the Edit button to specify the rollup conditions for your new field.
The Source Entity will automatically default to the entity where you created the new field. You can select whether or not the rollup should employ a hierarchy. If Use Hierarchy is set to YES, all related records that are related to any record in the hierarchy will be included in the rollup. In this example, if Use Hierarchy were set to YES, the Last Appointment Date would evaluate completed Appointments for all child accounts, grandchild accounts, etc. You must specify the related entity for the rollup field as well as the formula to be applied for aggregation. Filters are optional. In this example, we added filter to only look at the Actual End date for Appointments with a status of “Completed”. When finished, save and publish your customizations to see the rollup field in action.
Some things to remember when working with rollup fields:
- Rollup fields are calculated using asynchronous jobs. Updates are performed automatically every hour. However, you can trigger an update manually by hovering your mouse over the rollup field on the form and clicking the recalculate icon:
- Rollup fields are stored in the database and can be included in view, reports, charts, forms, etc.
- Rollup fields can be used in combination with calculated fields. However, complex calculated fields cannot be used in rollups.
- Rollup fields are only available using a single directly related one to many entity.
- Rollup fields cannot include other rollup fields.
Ready to use Rollup Fields in MSCRM but not sure where to begin? Contact Beringer Associates today to get started.