Find by timestamp field

(0) Share

Report

Posted on by tvinko1

Hello BC experts,

i'm having problem finding records by timestamp field inside my codeunit. Other fields are working ok. This is my code

TableID := 17;
recRef.Open(TableID);
MyFieldRef := recRef.Field(0);
MyFieldRef.VALUE := '201347';
IF recRef.FIND('>') THEN BEGIN
    repeat
        timestampFld := recRef.Field(0).Value;
    until recRef.Next() = 0;
recRef.Close();

When I select rows without filtering I get timestamp field at 0 position.

Thanks

I have the same question (0)

All responses (5)

Answers (1)

tvinko1 40 on at

Like (0)

Report

RE: Find by timestamp field

Ok, thanks for input Daniel

Was this reply helpful? Yes No
Verified answer

Daniel Rimmelzwaan 3,485 on at

Like (0)

Report

RE: Find by timestamp field

Nope, you can't add fields to tables dynamically in AL (or C/AL for that matter) unfortunately.

Was this reply helpful? Yes No
tvinko1 40 on at

Like (0)

Report

RE: Find by timestamp field

Hi, thank you both for answers.

But before I can test proposed solution I must add new field dynamically, and now I'm stuck here.

Let me just explain what is the reason for TimeStamp field.

We have BI solution for NAV and using directly access to SQL Nav database. It's very complex process, because we are reading all information about tables and columns dynamically from metadata.

We are upgrading solution to D365 BC on cloud where there is no access to SQL db directly. We created additional service that calls AL code unit and pumps data from entites and store it to the Azure blob storage. From here, we can use our existing logic.

Problem is that pumping must be incremental - we need to know which are new records from previous batch and which records had been deleted / modified. So we are relaying on TimeStamp field.

Long story short : all must be dynamically, tables and columns are known in runtime, so we cannot use any "pre-prepared" extensions in AL.

Is there any chance to add timestamp field to the table dynamically, not through extension?

Thanks!

Was this reply helpful? Yes No
Lars Lohndorf-Larsen on at

Like (1)

Report

RE: Find by timestamp field

Hi,

Like Daniel says be careful with this field / leave it alone. There is one way you can possibly use it, but you must add the field to the table first as described here docs.microsoft.com/.../how-to--use-a-timestamp-field.

Add a new field to table 17:

Enabled Field No. Field Name Data Type

Yes 55000 Rec Timestamp BigInteger

And set property "SQL Timestamp" = Yes.

Now this field will be automatically populated with the TimeStamp field. As far as I know you can use it for example to syncronize data, so you can filter out any new entries since last time you did something. Example:

OnRun()

MESSAGE(FORMAT(GLEntry.COUNT));

LastTimeStamp := 263286;

GLEntry.SETFILTER("Rec Timestamp",'>%1',LastTimeStamp);

MESSAGE(FORMAT(GLEntry.COUNT));

IF GLEntry.FINDSET THEN REPEAT

// Process my new entries

UNTIL GLEntry.NEXT = 0;

LastTimeStamp := GLEntry."Rec Timestamp";

// save LastTimeStamp somwhere for next time I run my code.

(LastTimeStamp = BigInteger)

I have not tried this in .al so I don't know if the time stamp field can be added as a field extension, but I hope this gives some ideas how to use it,

Was this reply helpful? Yes No
Daniel Rimmelzwaan 3,485 on at

Like (1)

Report

RE: Find by timestamp field

The timestamp field is a system field, just leave that field alone. What are you trying to do? I've never had to use that field.

Was this reply helpful? Yes No

Community site session details

Find by timestamp field

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

Find by timestamp field

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates