Microsoft Dynamics CRM

{Useful to know} Calculated fields functions and considerations for dynamics crm 2015

Following is list of functions available with Calculated fields in CRM 2015:

Calculated field functions syntax

The following contains information about the syntax for the functions provided in the ACTION section of the calculated field.

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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.

DateTime

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

In the CONCAT function, you can use literal strings as single lines of text, entity fields that contain a single line of text, or a combination of both. For example: CONCAT (FirstName, LastName, “is a manager.”). If a literal string contains quotation marks, precede each mark with the backslash (\) escape character, like this: “This string contains the \”quotation marks.\”” This ensures that the quotation marks inside the string aren’t treated as special characters that separate the strings.

The following examples show how to use the TRIMLEFT and TRIMRIGHT functions. They contain the initial strings and the resulting strings, returned by the TRIMLEFT and TRIMRIGHT functions:   

TRIMLEFT (“RXX10-3456789”, 3), returns the string “10-3456789”
TRIMRIGHT (“20-3456789RXX”, 3), returns the string “20-3456789”

Calculated fields considerations

You should be aware of certain conditions and limitations when working with calculated fields:

  • Saved queries, charts, and visualizations can have a maximum of 10 unique calculated fields.
  • The calculated field values are not displayed in the CRM Outlook Offline mode in the tile views or on entity main forms.
  • A maximum number of chained calculated fields is 5.
  • A calculated field can’t refer to itself or have cyclic chains.
  • If you change one of the condition operators in a multiple condition clause, all of the condition operators will update to that condition. For example, in the clause IF (x > 50) OR (y ==10) OR (z < 5), if you change the OR operator to the AND operator, then all ORoperators in the clause will become AND operators.
  • You can access parental fields via the Lookup field to the parent entity, such as <LookupFieldName>.<FieldName>. This is not possible with multi-entity Lookup fields like Customer which can be Account or Contact. However, some entities have individual Lookup fields for a specific entity, such as ParentAccountid.<FieldName> or ParentContactid.<FieldName>.
  • Sorting is disabled on:
    • A calculated field that contains a field from a parent record.
    • A calculated field that contains a logical field (for example, address field).
    • A calculated field that contains another calculated field.
  • Calculated fields can span two entities only.
    • A calculated field can contain a field from another entity (spanning two entities – current entity and parent record).
    • A calculated field can’t contain a calculated field from another entity that also contains another field from a different entity (spanning three entities):
      (Current Entity)Calculated Field <- (Parent Record) Calculated Field 1 <- (Parent Record) Calculated Field 2.
  • You can’t trigger workflows or plugins on calculated fields.
  • You can’t change an existing simple field to a calculated field. If your current application is using JavaScript or plug-ins to calculate a field, you would not be able to use the calculated fields feature without creating a new field.

Hope it helps!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s