If you haven’t worked with the calculated or 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.
If your users or sales reps are logging activities in MSCRM, chances are that management would like to know how often they’re working on a particular customer or opportunity. With a rollup field, you can get this information with ease. This example is going to determine the date of the most recent closed activity for an account. My example will look at all activities but you can limit it to specific types. I have a client that only wants to know when a rep logged a phone call and didn’t leave a message because that should mean that the rep actually spoke to the customer.
- Create a new field for the account. The Data Type will be Date & Time, Field Type will be rollup. Format is your preference – Date Only or Date and Time.
- Make sure that Behavior is NOT set to Date Only. Since the Actual End field on the activity is not date only, the rollup field can’t be either.
- When you click on the Edit button next to Field Type, a new window will open where you can define your rollup criteria.
- Under Related Entity, choose Activities (Regarding).
- For the Activity filter, set it to Activity Status equals Completed.
- Under Aggregation, use MAX of Actual End. You can choose a different date field here but I use Actual End to be consistent across all activity types.
Here is what the completed rollup settings will look like.
Rollup fields are automatically updated every evening with a system job that runs. Additionally, you can manually update the field by clicking on the small refresh icon.
