Limitations of Calculated Fields and the Data Export Service

You probably already know that I'm a big fan of the Data Export Service. The single fact of having a 'near real time' replica of your data in a SQL Azure Database to query in any way you want is simply amazing.

Today I came across an interesting limitation with Calculated Fields. Although Calculated Fields are created in the Dynamics database as SQL Server Computed Columns, they are output in the Replica Database fields as standard fields.

This has a rather inconvenient side-effect when you have calculated fields that are linked to either date/time or a related record. Since the Azure Replica sync is event based, when a related record is updated there is no corresponding event on the referencing record that contains the calculated field therefore it does not get updated. Likewise, if a calculated field changes depending on the date/time then there is no event that triggers the azure replica to be updated. This means that although calculated fields maybe correct at the time the record was created, subsequent updates can make the field become stale and inaccurate.

Lesson learned - you cannot guarantee the accuracy of calculated fields in the Azure Replica if they contain:

1. The Now() function
2. A related record field (e.g. accountid.name)

Interestingly, calculated fields that use data on the same record do get updated, so the event integration must do a compare of any calculated fields to see if they have changed.

@ScottDurow