Hi Christian, this is a snippet from a long-time working version with
several users creating invoices and no problems experienced. This
invoice number is then available in Delphi.
...
InvHeadInsert.ExecProc;
locInv_Nmbr:=InvHeadInsert.Params.ParamValues['new_inv_nmbr'];
...

There is a trigger and SP as follows:

CREATE OR ALTER trigger tr_inv_nmbr for invhead
active before insert position 0
AS
begin
new.inv_nmbr=gen_id(inv_nmbr_gen,1);
new.inv_date=current_date;
new.del_time=current_time;
end

create or alter procedure INVHEAD_INS (
ACNO type of ACCOUNT_NO,
NO_BOXES type of DECIMALS_0)
returns (
NEW_INV_NMBR type of DECIMALS_0)
as
begin
insert into invhead (
acno,no_boxes)
values (
:acno,:no_boxes)
returning inv_nmbr
into :new_inv_nmbr;
end

I have not tried with FB3, this is 2.5, but I hope it helps.
Alan J Davies
Aldis


On 02/09/2018 16:03, 'Christian Giesen' chris@...
[firebird-support] wrote:

> 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@... <mailto:chris@...>
>
> Alt Email: cvgiesen@... <mailto:cvgiesen@...> or
> cvgiesen@... <mailto: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.
>
>