Are You Taking Advantage of Calculated Fields?

CRM Blog Are You Taking Advantage of Calculated Fields?

I’ve been using calculated fields more and more while working with clients lately. They seem to be an answer to many scenarios that previously required jscript or plugins. While plugins were reliable solutions to calculate forms, they required hours of development versus a couple of minutes to create a calculated field.

Here are a few notes that I have put together when working with calculated fields. these notes have been very useful as I plan out customizations for our clients. Here is another article we published when calculated fields first became available.

Here is the Good

  • You can create a calculated field on a child entity that relates to a parent entity
  • You can use calculated fields to concatenate on a text field
  • You can use date functions to calculate Difference between date fields as well as calculate days into the future
  • Whole Number, Decimal and Currency fields use common excel like functions
  • OptionSets and Two Option fields are supported
  • Calculated fields can use branching logic so fields can have different calculations based on different fields on the record

Here is the Bad

Fields

  • You cannot use fields on a related entity in the calculation.
  • You cannot change existing fields to calculated fields – for example, if you want the Estimated Revenue field on an opportunity to have a different calculation, you cannot update the built in calculation.
  • Calculated fields are not available for floating number or Lookup type fields.

Views

  • Calculated fields can be placed on a view – but you cannot export that view to Excel if you have more than ten calculated fields.
  • Calculated fields may not always be sortable on a view if the calculation references another calculated field or a field on a parent record.

And Here are the Available Functions

Function Syntax Description Return type
ADDDAYS (whole number, date and time) Returns a new date and time that is equal to the given date and time, plus the specified number of days. Date and Time
ADDHOURS (whole number, date and time) Returns a new date and time that is equal to the given date and time, plus the specified number of hours. Date and Time
ADDMONTHS (whole number, date and time) Returns a new date and time that is equal to the given date and time, plus the specified number of months. Date and Time
ADDWEEKS (whole number, date and time) Returns a new date and time that is equal to the given date and time, plus the specified number of weeks. Date and Time
ADDYEARS (whole number, date and time) Returns a new date and time that is equal to the given date and time, plus the specified number of years. Date and Time
SUBTRACTDAYS (whole number, date and time) Returns a new date and time that is equal to the given date and time, minus the specified number of days. Date and Time
SUBTRACTHOURS (whole number, date and time) Returns a new date and time that is equal to the given date and time, minus the specified number of hours. Date and Time
SUBTRACTMONTHS (whole number, date and time) Returns a new date and time that is equal to the given date and time, minus the specified number of months. Date and Time
SUBTRACTWEEKS (whole number, date and time) Returns a new date and time that is equal to the given date and time, minus the specified number of weeks. Date and Time
SUBTRACTYEARS (whole number, date and time) Returns a new date and time that is equal to the given date and time, minus the specified number of years. Date and Time
DIFFINDAYS (date and time, date and time) Returns the difference in days between two Date and Time fields. If both dates and times fall on the same day, the difference is zero. Whole Number
DIFFINHOURS (date and time, date and time) Returns the difference in hours between two Date and Time fields. Whole Number
DIFFINMINUTES (date and time, date and time) Returns the difference in minutes between two Date and Time fields. Whole Number
DIFFINMONTHS (date and time, date and time) Returns the difference in months between two Date and Time fields. If both dates and times fall on the same month, the difference is zero. Whole Number
DIFFINWEEKS (date and time, date and time) Returns the difference in weeks between two Date and Time fields. If both dates and times fall on the same week, the difference is zero. Whole Number
DIFFINYEARS (date and time, date and time) Returns the difference in years between two Date and Time fields. If both dates and times fall on the same year, the difference is zero. Whole Number
CONCAT (single line of text, single line of text, … single line of text) Returns a string that is the result of concatenating two or more strings. String
TRIMLEFT (single line of text, whole number) Returns a string that contains a copy of a specified string without the first N-characters. String
TRIMRIGHT (single line of text, whole number) Returns a string that contains a copy of a specified string without the last N-characters. String

Now it’s Your Turn

enCloud9 has one of the most experienced Microsoft Dynamics CRM teams in the US. From pre-sales to project management, and user support, we respond quickly with our expertise to answer your questions. We are a Silver Microsoft Cloud CRM partner based in Omaha Nebraska and from our heartland roots we deliver best of class service to customers throughout the United States and Canada.

Our history dates back to 2009 – During the Dynamics CRM 4.0 days! – but our experience dates back even longer. Our consultants have been advising companies for almost thirty years to give them the tools to achieve their goals. Our experience leads to your success. We use our unique approach in helping small and medium-sized businesses lower their costs and boost productivity through Microsoft’s powerful range of cloud-based software.

We can be contacted at our webform or call us today at 1-844-264-0729

To read more about the benefits of a CRM solution, read our recent blog entry “Can Your Business Benefit From a CRM?”

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365