New calculated field functions in CRMOnline Update 1

I logged a suggestion in Connect long time back, about lack of DATEDIFF function option in calculated field (https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/1086828).

Quite to my surprise, CRMOnline Update 1 (Carina) now has a bunch of new DATEDIFF functions. These are

  • DIFFINDAYS
  • DIFFINHOURS
  • DIFFINMINUTES
  • DIFFINMONTHS
  • DIFFINWEEKS
  • DIFFINYEARS

There is also an additional function “NOW”, that gives you the current datetime. According to
https://youtu.be/NJgRctOncuA?t=855 “NOW” returns SQL Server UTC Time and not the user’s local time. This is true, only if the datetime field is Time Zone Independent. If the datetime field is created as User Local, “NOW” returns user’s local timezone.

If you try to use NOW function in a datetime field with behaviour Time Zone Independent, you’ll get “You can only use a Time-Zone Independent Date Time type field” error message.

The trick to getting UTC time in this case, is to first create the datetime field as User Local, fill in the calculation field action, and only then change the datetime behaviour to Time Zone Independent. Also note that, it is possible to change the datetime behavior from User Local to Time Zone Independent, but not the other way around (from the UI).

I created two calculated fields using NOW function, one is User Local and one is Time Zone Independent. After doing an Advanced Find with the attributes, here is the result.



DIFFINYEARS and NOW in combination, can be used in scenarios like calculating Age from Date of Birth, days since record creation, days to hit a certain deadline. These calculations, which were once accomplished by a running a periodic workflow or service, are trivial to implement with calculated fields. For eg. age calculation

This is the Advanced Find result

These great additions in the Spring Update, make calculated fields more powerful than ever.

Advertisements

2 thoughts on “New calculated field functions in CRMOnline Update 1

  1. Great news! However I’ve done a quick test, and to me the DiffInYears function will not 100% accurately define AGE just by calculating the difference in Years as it also needs to take the Month/Day into account.

    It doesn’t take into account the fact that a record’s “birthday” has/has not occured yet.

    For example if the Contact’s date of birth is in December, for the at least 11 months of the year the AGE will show a value of 1 year higher than it should be. Technically the AGE should only increment on the day of the birthday onwards.

    I’m hoping they include some very basic functions to accurately calculate important “CRM” based values like this eventually! May be worth another CONNECT suggestion!

    Great blog post mate.

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