Hi, I designed a quote/order system. There are going to be more quotes then orders, about 4 to 1 ratio. So I can make the next incremental Quote number easy enough. When it switches to an order I flag it with a boolean toggle. But I want to give it next order number. I cant use the record number since there may be gaps filled with quotes. The client wants them to be sequential since they print them out and it is easier for them to keep track of things, billing wise. How can I get the next order number. I was thinking of getting a count of all the orders and +1. However if a order gets deleted or changed back to a quote or something like that it will get screwed up. I can make a table that put in order numbers and then get the next one, but I wanted to avoid creating a separate table. Any ideas?
I can make a table that put in order numbers and then get the next one, but I wanted to avoid creating a separate table.
You already have that table (the Orders/Quotes table itself); what you need is a new TO of it. Create a self-join matching on type, then get Max ( SelfJoin_byType::OrderNumber ) and increment that value.