How to access timestamp field in C/AL?

gumboots

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

kriki

[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.

gumboots

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

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.

bbrown

If you need to periodically sync the tables have you considered snapshot replication? Or even transactional replication?

kriki

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.

bbrown

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.

gumboots

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

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?

gumboots

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

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.

bbrown

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.

gumboots

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

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

gumboots

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

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.

pberb

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