>Hi hilmar,
>
>The number that will be returned from a stored procedure will be used as a transaction number in a different table.

No problem. The table SerialNumber stores sequences, each with a name, and the next number to be returned in the sequence. A corresponding stored-procedures-function, the UDF SerialNumber, goes to this table and fetches the next number (and increments it in the process).

Suppose you want to emulate the auto-incremental field. Craig Boyd doesn't recommend auto-incremental fields, and I haven't tested them yet - but I think a UDF and a sequences table, gives you more control. In this case, just call the sequence function from the field's default value - for example, the default value might be SerialNumber("client"), for the client PK. If the user creates a record and presses a "Cancel" button (TableRevert()), the number will be skipped. This doesn't matter for primary keys (if you use primary keys without business value - codes which the user never sees).

If you want to make sure that numbers in a table are not skipped - for codes which the user does see, like a document number - you have to use a transaction (and include the table SerialNumber in the database, so that it can participate in transactions).

In this case, the serial number should be assigned only when the record is saved. Example:

begin transaction
replace PurchaseNumber with SerialNumber("PurchaseNumber")
if ThisForm.SaveRecord()
  end transaction
else
  rollback
endif

Replace ThisForm.SaveRecord() by whatever method your framework uses to save a record.

Of course, you can invoke the sequence from any other place where you need a serial number. Also, you can use as many different sequences as you wish - my function creates a sequence automatically if it doesn't exist yet.

Here is my function SerialNumber(), which you can place in the DB stored procedures:

FUNCTION SerialNumber(tcSequence)
	* Get serial number. Used mainly to generate primary keys.
	* The easiest way to achieve this is to call this function from a fields default value.
	* Example: default value for primary key "Client" of table "Client" = SerialNumber("Client")
	* This function accesses table SerialNumber. Fields: Sequence C(30), NextNum I.
	tcSequence = lower(tcSequence)
	local lnSelect
	lnSelect = select()
	if used("serialnumber")
		select serialnumber
	else
		select 0
		use serialnumber
	endif
	set order to "sequence"
	seek padr(tcSequence, len(sequence))
	if not found()
		append blank
		replace sequence with tcSequence, nextnum with 1
	endif
	local lnReturnValue
	if lock()
		lnReturnValue = nextnum
		replace nextnum with nextnum + 1
	else
		lnReturnValue = -1
	endif
	unlock
	select (lnSelect)
	return lnReturnValue
ENDFUNC