How to get current AutoIncrement number?

itspeter

How do I get the autoincrement number right after I insert a record?

kriki

Use this:

"Next No." := NoSeriesMgt.TryGetNextNo("No. Series","Posting Date");

NoSeriesMgt is codeunit 396.

I hope this is what you wanted.

itspeter

I need to get change log number (Entry No.).

kriki

Which table are you inserting? And from which table you want the no.? :?:

But in general it is enough to do this:

recMytable.RESET;
IF recMyTable.FIND('+') THEN
  intLastNo := recMyTable."Entry No.";

Remember: if you are still in the transaction, you will have the last no. you inserted. IF you AREN'T in the transaction, it is possible you get the last no. someone else inserted.

itspeter

I insert into table 405 - Change Log Entry.

Should I lock table before I insert them, so I can make sure the number that I get is from the record I insert. :-s

kriki

itspeter wrote:

Should I lock table before I insert them, so I can make sure the number that I get is from the record I insert. :-s

Not necessay, when inserting, the table will be locked automatically. Even with SQL this is the case, because Navision must get the last no. and then add 1 to it to have the next free no.
You just must be sure you're still in the transaction.
You can test this in this way: Where you get the no. of the last record, put this:

FORM.RUNMODAL(FORM::"Company Information");

If you get an error stating you cannot RUNMODAL a form when in a transaction, you are in the transaction. If you DON'T get an error, but you get the form, this means the transaction is over and you can get the no. of another transaction.

PS don't log too much fields and tables. Each table and field logged is an extra record to be inserted when modifying/deleting/inserting a record.

itspeter

Good, the runmodal raise error. So, it is in transaction then.

Thanks.

bbrown

In SQL the last identify seed value used is stored as a parameter of the table. There are transact-sql commands to retrieve it. You can check it prior to committing the transaction. The native db does something similar but I am not sure where it is stored or how to retrieve it.

On either DB an autoincrement field does not issue a find last.

A simple test.

1. create a table with the following primary key

Entry No. (integer)(autoincrement)

2. Insert 10 records (do not populate "Entry No.", let the system do it)

3. View the table. The records will be "Entry No." 1 thru 10.

4. Delete the records.

5. Repeat step 2.

6. The records will be numbered 11 to 20.

kriki

bbrown wrote:

In SQL the last identify seed value used is stored as a parameter of the table. There are transact-sql commands to retrieve it. You can check it prior to committing the transaction. The native db does something similar but I am not sure where it is stored or how to retrieve it.

On either DB an autoincrement field does not issue a find last.

You got me curious about this. So I did some debugging to see how it works.
I found that field 1:"Entry No." in Table 405:"Change Log Entry" is a biginteger and that it has the property "AutoIncrement"=Yes.
So in fact, it does not a find last, but somewhere it is kept in the DB. Probably in some hidden table, so we can't access the value and we have to do a find last to get the value.

kriki

I just thought of another (better) solution to get the last "Entry No.".

Codeunit 423:"Change Log Management" is a SingleInstance-codeunit. This is necessary for this trick

In the codeunit you must define a global : biiLastChangeLogEntryNo as big integer
In Function InsertLogEntry, you have to put

...
ChangeLogEntry.INSERT;
// NEW CODE START
biiLastChangeLogEntryNo := ChangeLogEntry."Entry No.";
// NEW CODE STOP

You have to create a new function :

GetLastChangeLogEntryNo() : BigInteger
EXIT(biiLastChangeLogEntryNo);

Now in you object where you want to know the last used Entry No., you have to put codeunit 423:"Change Log Management" in the globals.
When you have done your actions, you can put this to know the last entry No.:

MESSAGE('Last Entry No.:%1',cduChangeLogManagement.GetLastChangeLogEntryNo());

A singleinstance codeunit remains in memory and keeps it's globals (I don't think the codeunit is being CLEARed somewhere).
So if you change something, the last entry no. is retained and it will not be changed by changes in another session.
I didn't try this out, but I think it should work.

itspeter

I don't have the license to change "Change Log Management"(CU423). It require Solution Developer license.

I'm calling own codeunit, and transfer related functions from Change Log Management.