--- In firebird-support@yahoogroups.com, "Mercea Paul" <paul.mercea@...> wrote:

>

Hi,
Paul below gives two solutions, a status on the invoice table or a seperate table. Personally we use the second approach and save all cancelled invoices to an archive file. If you want to see all invoices (as accountants will) then you can use an SQL statement to join the two tables togther.

Another alternative would be to assign an invoice number only after the user had confirmed their order. I think this would be prefereable in your circumstances. Use the first screen to gather the customers information then regirgitate it on a confomation screen togther with a newly assigned invoice number. That will save cluttering up the daatabase.

Bear in mind that many people on the internet will create an invoice just to find out how much the purchase will cost, with no intention of completing the purchase.

Regards,
Geoff Marshall

> Hi Myles
>
> I think you need to have the big picture in this.
> If a user can delete an Invoice, than can delete any invoice?
>
> We have same system, but each invoice have a status field.
> If an invoice is deleted, this mean the invoice status modify from normal
> to delete. If the invoice is canceled then the status will be canceled. But
> NEVER is deleted from database.
>
> Another aproach is to use additional table with type of document field
> (invoice) and current_value(invoice nr).In this case you need again the
> status of invoice (is created, is saved, is canceled) to increment or nor
> the invoice nr.
>
> Best regards,
> Paul
>
> ----------------------------------------
>
> From: "Myles Wakeham" <myles@...>
> Sent: Thursday, May 14, 2009 6:18 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Generator question
>
> I am using a Generator to create sequential ID numbers for most of my
> tables. It works great.
>
> However one table I have to create data in contains records that I want
> to be able to keep sequentially contiguous. Its an INVOICE table.
>
> The way I'm doing this is that when a user wants to create an INVOICE, I
> create a record in that table and get an ID back from the generator
> assigned to it. I then display an invoice form (this is via PHP on the
> web) to the user to fill out.
>
> Since I don't want two or more users creating an invoice with the same
> ID, I immediately commit the record creation stored procedure that gives
> me back the invoice number that I'm using. The record is then updated
> with the actual fields entered by the user when they submit their update.
>
> The problem occurs when a user elects to cancel or not complete the full
> invoice creation process. In this case I will delete the invoice record
> created, however I need to be able to 'recycle' the invoice number that
> was given, so that it becomes the next candidate invoice number given to
> the next user.
>
> I'm thinking that the best way to do this would be to reset the
> Generator to the number that was just recycled. That way when the user
> who next wants to create an invoice gets a number, it will come from the
> generator with that recycled number.
>
> Is this a good way to do this, or am I going to find myself in problems?
> And if it is a reasonable way, how can I change the generator's next
> value in a stored procedure?
>
> I tried this:
>
> SET GENERATOR GEN_INVOICE_ID TO :A_RECYCLE_NO;
>
> but it won't accept it, stating that its a parsing error.
>
> What am I doing wrong?
>
> Myles
> --
> =======================
> Myles Wakeham
> Director of Engineering
> Tech Solutions USA, Inc.
> Scottsdale, Arizona USA
> http://www.techsolusa.com
> Phone +1-480-451-7440
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>