Good day,

Using Firebird 3.0.3.

RAD Studio 10.2.2 (Tokyo)

I have a need to generate unique sequential invoice/credit note numbers. I fully understand the usage of generators to produce unique identifiers in tables. However, according to Firebird documentation the use of generators does NOT guarantee sequential numbering. I don’t think that this is too difficult to implement but I need to know how to get the last generated ID value so that after inserting a new invoice I can assign the next sequential invoice number to this record.

In my table I have a number of fields but the ones of interest here are:

The new way of implementing auto incremental numbering.

ID – INTEGER AUTOINCREMENT IDENTITY PRIMARY KEY

INVOICE_NO – INTEGER NOT NULL

The traditional way of implementing auto incremental numbering.

ID – INTEGER AUTOINCREMENT PRIMARY KEY (Using a visible editable generator)

INVOICE_NO – INTEGER NOT NULL

My question: I wish to obtain the last generated value for the ID field.

1.       The new IDENTITY implementation ‘hides’ the generator so how do I get this value using this method.

2.       With the traditional way I could get this value by querying the tables generator value + 1.

I would imagine that I would then update the current invoice by using the AFTYER INSERT trigger to update the INVOICE_NO field by identifying  this record from its last generated ID.

So my code would look something like this:

DECLARE VARIABLE NEXT_INVOICE_NO INTEGER;

DECLARE VARIABLE LAST_ID INTEGER;

<Get the last generated ID and assign it to the LAST_ID variable>

<Get the next invoice number by using MAX(INVOICE_NO) form the invoice table and assign it to the NEXT_INVOICE_NO variable>

<Update the INVOICE table and set the INVOICE_NO field value to the NEXT_INVOICE_NO variable value>

A snippet of sample code as to how to implement this would be greatly appreciated.

Thank you for your assistance.

Kind regards,

Christian Giesen

Managing Member

Express Talent cc

Mobile: +27 82 574 5566

E-Mail:   chris@...

Alt Email: cvgiesen@... or cvgiesen@...

This message contains private and confidential Information. If you are not the intended addressee indicated in this message or you are not responsible for the delivery of such messages to the intended recipient, you may not copy or deliver this message to anyone. You may not use any information gleaned from this message for the benefit or promotion of yourself or your organization. If such is the case, please destroy this message immediately and kindly notify the sender by return of E-Mail. Express Talent cc takes no responsibility whatsoever resulting in you misusing information contained in this message and doing so may render you liable for the consequences of misusing said content of this E-Mail message.