Anyone who has tried to make a data integration with Microsoft Dynamics 365 for Finance and Operations has met the obstacle, that the system does not support timestamp. That makes it troublesome to identify new and modified data. There are several ways to solve this.
The problem is that if you don't want to transfer an entire table every time you want data synchronized, then you need a way to detect, which data is modified or added.
So how do you deal with it in the best way?
Method 1: Develop triggers in the Code that updates a timestamp field on the record
The first method requires that a developer creates a timestamp field on each table, or creates a similar functionality that gets updated every time a record i modified. But there are several issues with this method.
- A record can be modified via man different actions. It can be a manual update performed by a user, it can be an import that goes directly without executing code, and it can be functionality from many different places in the system code which you need to detect in order to maintain the consistency of the time stamp. It can be very difficult to predict if the code captures all possible changes and it make the system fragile to changes.
- The programming needs to take into consideration all the tables - also related tables and it means that both the programming, testing and maintenance can be massive and the integration be become fragile and expensive to maintain because it adds another layer of complexity to a system that is already complex by nature.
Method 2: Creating comparison tables in AX
Another method is to create comparison tables in AX where the developer can compare new and old values and by this detect which records that are modified. This is in many way a more solid way to detect changes, however it also comes with some challenges:
- It can require significant work to custom build this logic and like in method one it requires a significant work to make and to maintain. If it's just one table it may no be a big deal, but imagine if you have 30 tables or more.
- Most Microsoft Dynamics 365 Finance and Operations implementations perform a lot of transactions thus there are many record in the systems. Therefore, it can create a big overhead in reading/writing data that can negatively impact the performance of the system which is not easily overcome - and definitely no cheap.
Method 3: Using the Rapidi Mirror technology
The mirror is a highly optimized variant of the above Method 2. The mirror is a separate binary SQL database (MS SQL, MySQL or similar) designed to solve the timestamp issue without having the constraints in the above methods:
- It's completely programming free which means that you don't have to write a single line of code.
- It requires almost no effort to setup because it's all automated regardless of any customizations or add ons in Microsoft Dynamics 365 for Finance and Operations.
- Regardless how a record is modified it is detected as a change and can be used by the integration platform RapidiOnline to identify the specific records that are changed.
- In other integration scenarios you need to create fields on the records to contain the link to the other system/other systems. However the RapidiOnline solution as a LinkStorage feature where this link is built and maintained in the integration platform. This way you can have an integration without having to manually create any code or fields and your upgrade path is unaffected.
- The mirror works not only with Microsoft Dynamics 365 Finance and Operations, the timestamp issue is also a problem with Microsoft Dynamics AX and it can appear in custom developed systems e.g. on SQL. The RapidiMirror is generic and can work with other systems as well (Microsoft Dynamics GP has the same challenge) and it works with both on premise and cloud applications.
If you want to discuss the integration with Microsoft Dynamics 365 for Finance and Operations or any timestamp challenges you may have we are always happy to help.
Read also: OData - what happens with your Dynamics integration when you need to upgrade to 365
About the author
With over 25 years’ experience in strategically propelling businesses forward, Henning is considered a business development entrepreneur with a passion for transforming businesses, sales and marketing operations through out-of-the-box thinking, concepts building and process automation to improve overall performance and scalability.