How to effectively detect if data has changed using RecVersion

Sometimes there is a requirement to effectivle detect if data has changed in a table. The first thing that might come to mind is, why not just use the "ModifiedDateTime" field of the data? Well, that's perfectly fine if don't have a lot of data in your table.

But, what if there is a lot of data in our table? Like millions of records? Filtering on an unindexed field like "ModifiedDateTime" and with a lot of data, will force the database server to scan all the records in the table. The database server has to do this to determine which records are modified after the specified date and time. Table scans on large tables is not effective and painfully slow because it's not using any of the table indexes.

RecVersion

So, what's the solution? How do we effectively detect which rows have changed in our data? By using the system field RecVersion. Every table in Dynamics 365 Finance and Operations (F&O) has a field called RecVersion.

Every time a record is changed in a table, the RecVersion of the record is updated by the system to a new value, or version. The RecVersion field contains the record version of the record.

But, there is a small problem, how do know what the previous version of RecVersion was before the data was changed? Well, we don't. But luckily there is a simple way so solve this. By creating a custom table with the RecId and RecVersion of our data.

Steps

• Create a custom table with a reference RecId and RecVersion field to our data. Field names: RefRecId and RefRecVersion
• In code, create a method to create a "snapshot" of the RecId and RecVersion values of the data. Use insert_recordset to copy the data.
• Query your table with a join to the custom table. Join on YourTable.RecId and YourTable.RecVersion and SnapShotTable.RefRecId and SnapShotTable.RefRecVersion

To get all the data from the target table that has changed, join the two tables on RecId and RecVersion. The records where RecVersion is different, has changed.