If you haven’t worked with the calculated 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 fields that I have found useful for many clients.
People often want to know how old a record is. For example, you may want to know how long an opportunity has been open or how long a lead has been waiting to be contacted. An easy way to achieve this is with a calculated field. You can basically “set it and forget it.” Let’s review how to create a field to calculate the age of an opportunity. It will indicate how long an opportunity has been open.
- Create a new field on the opportunity. The Data Type will be Whole Number for the number of days. The Field Type will be Calculated.
- When you click the Edit button next to the field type, it will create the field and open a new window where you can set up your calculation.
- First set up a condition that checks if the status of the opportunity is Open.
- Add an action for the condition. It will set Age to DIFFINDAYS(createdon, NOW()). This is essentially today’s date minus the day that it was created on.
- Add an Else action that will calculate the age when the opportunity is not open (closed as won or lost). This will look at the actual close date minus the date that it was created. The formula is DIFFINDAYS(createdon, actualclosedate).
Here is a screenshot of what the calculation looks like when finished.
It’s very simple and doesn’t require any maintenance. The field updates on its own.
