Dynamics 365 Using NOW() in Calculated Duration Fields

Sean Roque, 22 June 2021

In our Dynamics 365 environment, there is a custom 1 to Many relationship between Contact and Case. In the Case entity, there is an existing Calculated Field called “Case Duration” which calculated the time in hours, between the Case “Created On” field and the current date and time. Notice that the out of the box NOW() function is being used to represent the current date and time.

We then encountered a scenario where we needed a field on the Contact entity to represent the sum of the “Case Duration” fields for each of the Contact’s related Cases. At first glance, this seems easily achievable by creating a rollup field, using the calculated field as part of the aggregation.

However, when creating this rollup field, we found that the “Case Duration” field is not selectable when selecting the Aggregate Related Entity Field

It turns out that if a Calculated Field is using the NOW() function, it becomes unusable in a rollup field.

After removing the NOW() function from the Calculated Field expression and temporarily replacing it with the “Last On Hold Time” field, we can see the Case Duration field and set it as part of the Rollup aggregation

And when you try to update the calculated field to use NOW() again, you will get the following error

So how did we get around this limitation?

With consideration that Rollup fields by default calculate every 12 hours, a potential workaround is to create a new field to represent the NOW() function. Then we can build a process that updates this field to current Date Time. As long as this update occurs more frequently than every 12 hours, in most cases this can provide sufficient accuracy for reporting needs.

Depending on quantity of data, the ideal method of implementing the process can differ. One method is to use a scheduled Power Automate Flow to update the field in all relevant records. In this example, I created a new Date Time field called “mag_datetimenow” and below is an example implementation of setting the value of this new field using a Power Automate Flow:

The expression for Row ID is as follows: items('Apply_to_each')?['incidentid'] , where “incidentid” is the primary key for the entity.

We have also added our current Date Time placeholder field “mag_datetimenow” as the only column to retrieve to reduce the Flow’s resource consumption. We are also filtering only for active records “statecode eq 0”. Ideally, we want to add as many filter criteria as possible to minimize the records that will be retrieved to only those required e.g., we do not need to update records created X years ago as they are irrelevant.

Next, we can easily set the calculation for the calculated field to use mag_datetimenow instead of the out of the box Now(). This calculated field can then be used as part of rollup fields as normal.