How to access timestamp field in C/AL?

gumboots
gumboots Member Posts: 23
edited 2008-03-07 in NAV/Navision Classic Client
Hi,

I am currently working on an integration project and it requires synchronization of data between a NAV table and a schematically similar external table. I would like to perform a full sychronization between both tables periodically, which requires some sort of timestamp comparison. NAV tables have a timestamp field. Does anyone know a clean and elegant way to access the value in the timestamp field using only C/AL?

Thanks

Scott

Comments

  • kriki
    kriki Member, Moderator Posts: 9,127
    [Topic moved from Navision Tips & Tricks forum to Navision forum]

    If you are working on a Navision-DB => Navision doesn't have such a field.
    If you are working on SQL, you best work directly on SQL. Navision C/AL has no access to it. And on SQL it will be a lot faster.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • gumboots
    gumboots Member Posts: 23
    kriki wrote:
    [Topic moved from Navision Tips & Tricks forum to Navision forum]

    If you are working on a Navision-DB => Navision doesn't have such a field.
    If you are working on SQL, you best work directly on SQL. Navision C/AL has no access to it. And on SQL it will be a lot faster.

    Hi Kriki,

    If there is no direct access to this field from C/AL. Are you suggesting ADO would be the only strategy to access the timestamp field?

    Scott
  • bbrown
    bbrown Member Posts: 3,268
    Are the 2 tables in the same database? The current timestamp is kept for each database. A timestamp value is only unique within its database.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    If you need to periodically sync the tables have you considered snapshot replication? Or even transactional replication?
    There are no bugs - only undocumented features.
  • kriki
    kriki Member, Moderator Posts: 9,127
    gumboots wrote:
    If there is no direct access to this field from C/AL. Are you suggesting ADO would be the only strategy to access the timestamp field?
    With ADO or writing something directly in SQL to be launched by the SQL Agent.
    bbrown wrote:
    If you need to periodically sync the tables have you considered snapshot replication? Or even transactional replication?
    Be careful with replication and Navision-DB's. The problem is that that field is used by Replication to know what is new and by Navision for simulating the versioning-principle.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • bbrown
    bbrown Member Posts: 3,268
    Transactional replication uses the transaction log to determine updates. The timestamp would not be an issue if Navision is the publisher. It would be an issue if Navision is the subscriber.
    There are no bugs - only undocumented features.
  • gumboots
    gumboots Member Posts: 23
    bbrown wrote:
    Are the 2 tables in the same database? The current timestamp is kept for each database. A timestamp value is only unique within its database.
    Hi bbrown,

    Actually the two tables are not in the same database, though they are schematically so similar and have many common fields. What I'm trying to do here is to sync selected tables between NAV and RMS (e.g., Item and Customer). [Perhaps later we can move this discussion to the RMS-NAV integration topic]. I want to hear more from technical experts in this forum first so I get my head around the techniques use to sync tables. Next I will I have to determine the direction of dataflow between tables.
  • bbrown
    bbrown Member Posts: 3,268
    Comparing timestamps will not work between databases. The timestamps are created within each database.

    Next questions:

    Will users update the table in only 1 database? Which one?

    What frequency of updating (to the other database) is required?
    There are no bugs - only undocumented features.
  • gumboots
    gumboots Member Posts: 23
    bbrown wrote:
    Comparing timestamps will not work between databases. The timestamps are created within each database.

    Hi bbrown, may I ask why timestamp should not be used when synchronizing records? I am thinking more along the line of synchronizing individual tables rather than database files.

    My understanding of synchronization is this. Your goal is to ensure currency of records across different databases.

    Let SourceTableRecCount be S and TargetTableRecCount be T, you can use the following technique to detect table-level or record-level change.

    S > T: Row count to detect addition of record
    S < T: Row count to detect deletion of record
    S = T: May suggest no change or field change. Only way to tell is from timestamp

    Timestamp becomes important when you try to ensure currency of a record. For example, if a customer changed his phone number or mailing address and you have the same customer (same VAT Registration No. or Tax File No.) in the Customer table in both databases. How would you determine which one is current without looking at the timestamp?

    In NAV, we are lucky that Customer and Item tables both have a Last Date Modified field. But what about other NAV tables that don't like Sales Price?

    If you're trying to sync two tables that have the exact same table structure, there are other specific questions we also need to answer:

    1. What strategy do you use to detect change?
    2. If change is detected, how do we sync the source and target tables?
    3. Which one is the master table? Which one is replicate? Can either one be the master?
    bbrown wrote:
    Will users update the table in only 1 database? Which one?
    No, user can make changes to the records in either database. In my case a user can update a Customer record in a NAV Customer table at head office and or in RMS Customer table at the store.
    bbrown wrote:
    What frequency of updating (to the other database) is required?
    Can't tell you for sure right now. We want this to be a user option in a Setup table: Every X Minutes, Every Y Hours, etc. However I guess the real design decision here is whether we want to sync it real-time (triggered by OnInsert(), OnModify() or OnDelete()) or not (i.e., asynchronous batch update).
  • bbrown
    bbrown Member Posts: 3,268
    A timestamp is not an actual time. It is a relative time within a single database. The time stamp value is assigned by the system and not the user. If you copy a record from DB1 to DB2 they will have different timestamps.
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    How do you plan to handle conflicting updates?

    A user updates table in DB-1. Between that time and the time the sync process runs, a user updates the same record in DB-2.
    There are no bugs - only undocumented features.
  • gumboots
    gumboots Member Posts: 23
    bbrown wrote:
    How do you plan to handle conflicting updates?

    A user updates table in DB-1. Between that time and the time the sync process runs, a user updates the same record in DB-2.
    Can we lock the source table in DB1 during the process for NAV->RMS sync.

    Scott
  • bbrown
    bbrown Member Posts: 3,268
    gumboots wrote:
    bbrown wrote:
    How do you plan to handle conflicting updates?

    A user updates table in DB-1. Between that time and the time the sync process runs, a user updates the same record in DB-2.
    Can we lock the source table in DB1 during the process for NAV->RMS sync.

    Scott

    The issue is DB-2
    There are no bugs - only undocumented features.
  • gumboots
    gumboots Member Posts: 23
    bbrown wrote:
    The issue is DB-2
    Hi bbrown, I haven't thoroughly thought about that one yet. What's your take on this anyway? Let's say if we had a solution for this. How would you detect record-level change or a field change?
  • jlandeen
    jlandeen Member Posts: 524
    I've seen this problem many times in the past on integration probjects and the simplest solution that I always recommend is to have a master and slave system. This means that all adds, deletes and modifications are made in only 1 of the systems and this data pushed into the other. This means that either DB1 or DB2 is essentially "read only" and simply receives updates from the master database.

    If you make Navision the master database and have changelog turned on for any tables in question you could look to pull data from that to determin what changed and when.
    Jeff Landeen - Sr. Consultant
    Epimatic Corp.
    http://www.epimatic.com
  • pberb
    pberb Member Posts: 31
    jlandeen wrote:
    I've seen this problem many times in the past on integration probjects and the simplest solution that I always recommend is to have a master and slave system. This means that all adds, deletes and modifications are made in only 1 of the systems and this data pushed into the other. This means that either DB1 or DB2 is essentially "read only" and simply receives updates from the master database.

    Agreed - this is what we did in MASI Retail Portal(TM).

    http://www.masi.com/markets/MASIRetailPortal.html
    _________________
    Paul Berberich
    MicroAccounting Systems, Inc.
    Bellevue, WA, USA

Categories