Find the records that are modified recently

ZUHADZUHAD Posts: 3Member
Hi all,

I am performing an Asynchronous integration of third party system with business central.I have written a code unit that is exposed as web service which will be used by the 3rd party system
i am syncing the following entities:

1. Product with Product variants and product identifiers
2. Sales order header with sales line.
3. sales shipment header with sales shipment line
4. sales invoice header with sales invoice line.

In my scenario i have multiple jobs which executes multiple time in a day that find the set of record which are changed after the given date-time. Then sync only the records which were changed.

Now the problem i am facing is that in business central most of the tables doesn't have modified date time field, which can be used to filter the records that are modified recently.

Can anyone tell me how i can filter the records from any table that are modified recently?

Note :

One of the possible solution is to extend each table add a new field modified date-time. Extend the onmodify() trigger and set the current date-time on it.
But my client doesn't prefer to extend all of these tables just for a very basic field.

The other solution i have identified is to use the change log feature.But i believe this will slow down the system due to large numbers of records.



Thanks.

Zuhad Mahmood

Answers

  • Wisa123Wisa123 Posts: 265Member
    Hi Zuhad,

    The preferred way to do something like this is using the SQL Timestamps that have been introduced with NAV 2016. However this still requires you to add fields to your Tables.
    https://docs.microsoft.com/en-us/dynamics-nav/how-to--use-a-timestamp-field
    One of the possible solution is to extend each table add a new field modified date-time. Extend the onmodify() trigger and set the current date-time on it.
    Also a fine solution IMO.
    The other solution i have identified is to use the change log feature.But i believe this will slow down the system due to large numbers of records.
    No, just don't. For the exact reasons you stated.

    /Wisa

    Austrian NAV/BC Dev
  • ZUHADZUHAD Posts: 3Member
    edited 2019-08-21
    Hi @Wisa123,
    I have just reviewed the time stamp property.The AL is not allowing to enable it on table extension. Is there any other way to enable it? I am using the business central cloud sandbox .
    07nmn5wb2pp1.png

    Thanks,
    Zuhad Mahmood
  • Wisa123Wisa123 Posts: 265Member
    Hi Zuhad,

    Seems like you're right. The issue was handled here: https://github.com/Microsoft/AL/issues/1409

    Well, then extending your tables with a "modified at" field is your best bet in my opinion. Unless someone else here has a more elegant solution to offer.

    /Wisa
    Austrian NAV/BC Dev
Sign In or Register to comment.