>>On many apps, the order number is not typically shown until the order is submitted (or data has been completed keyed) because it is not a valid order until then. Once that is done, then the order number is displayed.
>>
>If this satisfies the user requirements, then the problem is easy to solve. But if we do need to show this field in advance, then I don't see a simple solution.
If you have to be able to show the order number even before the order is submitted, then I would have a auxillary table that contains the order numbers. Basically, this is how it would work:
New order is requested:
1. Get next order number from orderlist table (ordered by ordernum)
FUNCTION GetNextOrderNumber
LOCAL lnNextOrderNum AS Integer
SELECT orderlist
LOCATE FOR DELETED()
IF FOUND()
RECALL
lnNextOrderNum = orderlist.ordernum
ELSE
GOTO BOTTOM
lnNextOrderNum = orderlist.ordernum + 1
INSERT INTO orderlist (ordernum) VALUES lnNextOrderNum
ENDIF
RETURN lnNextOrderNum
ENDFUNC
This now gives you a order number that will be unique. If the user cancels the order request, then delete the record from the orderlist table:
FUNCTION DeleteOrderNumber
LPARAMETERS pnOrderNum
DELETE FROM orderlist WHERE ordernum = pnOrderNum
ENDFUNC
Also note that any "holes" in the sequence of the order numbers would be temporary and at the end of the list. As new orders are requested, they are always filling the "holes" first.
Also, I replyed initially to the wrong person -- sorry for this.
If the user commits the order, then the order number is what is used in your order header table and order items table as the key