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.

Quicktip: Install .net 4.5.2 before developing for CRM2015

Before you start developing console application, workflow or plugin for CRM2015 the first step is to install .net 4.5.2 as this is required by CRM2015. I was working on a simple console application using CRM2015 assemblies and I encountered a weird error. I ran "Install-Package Microsoft.CrmSdk.XrmTooling.CoreAssembly" from the PM console without any issue. But when you try to build the application I got these compilation errors.

The root cause of these errors is because the project is not targetting .net 4.5.2. Download .net 4.5.2 from http://www.microsoft.com/en-us/download/details.aspx?id=42637 and update the project target framework to .net 4.5.2. This time the build process succeeds. I was quite surprised that nuget did not prevent me from using the package in the project, even though I didn't have a the prerequisite framework version.

Calculated Fields in CRM 2015

Calculated fields are new to CRM 2015. Business Rules now also have a new Entity Level scope option. When it comes to simple decimal operations you can use either Business Rules at Entity Level scope or calculated fields. Obviously calculated fields, are much more powerful than business rules. There is one issue I experienced with business rules at entity level. The division operator currently does not seem to work properly.

This the business rule to perform the division.

When you save the record an error is displayed.

This is the actual error

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Expression operator not supported for specified type.Detail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ErrorCode>-2147220891</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
    <KeyValuePairOfstringanyType>
      <d2p1:key>OperationStatus</d2p1:key>
      <d2p1:value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">0</d2p1:value>
    </KeyValuePairOfstringanyType>
    <KeyValuePairOfstringanyType>
      <d2p1:key>SubErrorCode</d2p1:key>
      <d2p1:value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">-2146233088</d2p1:value>
    </KeyValuePairOfstringanyType>
  </ErrorDetails>
  <Message>Expression operator not supported for specified type.</Message>
  <Timestamp>2014-12-06T22:54:07.9005933Z</Timestamp>
  <InnerFault i:nil="true" />
  <TraceText>

[Microsoft.Crm.ObjectModel: Microsoft.Crm.ObjectModel.SyncWorkflowExecutionPlugin]
[c3877360-9a7d-e411-80cf-e83935c2f340: ]
Starting sync workflow 'Decimal Formula', Id: bc877360-9a7d-e411-80cf-e83935c2f340
Entering ConditionStep1_step: 
Sync workflow 'Decimal Formula' terminated with error 'Expression operator not supported for specified type.'

</TraceText>
</OrganizationServiceFault>

The calculated field that performs the division works without any issue.

Interesting behaviours that I encountered below.

Scenario 1: Calculated field to divide two integers and the result of the operation is float e.g. : 5 / 2

Result: The result is rounded up to the closest int.


  
Scenario 2: Calculated field - Divide by zero

Result: No divide by zero exception. The result is blank.



Scenario 3: Calculated field of type text, with calculation using decimal fields.

Result: No error. Result of the operation is can be assigned to the string field.



Conclusion: If it can be done using calculated fields, do it that way, instead of Entity scope business rules, as calculated fields offer much more flexibility and functionality.