>We have a header table that has a field validation default value for it which uses RECCOUNT() to autogenerate a unique number (lets call the field RFQ). As long as the table is not packed, we are fine. However, the code to create a new record and then populate it and child records is not working well in a multiuser environment. If I have nothing in my code to SET REPROCESS or do anything with LOCK or MULTILOCK and I do an APPEND BLANK on this header table and then immediately copy the "RFQ" field value. Can I be sure that no matter what, the copy of that RFQ value will never conflict with someone else running the code? In other words, when you do an APPEND BLANK how can you make a lock on that record...or is there a better way to do what I'm asking?
It is not quite clear how you obtain the unique values. If you get it with the equivalent of GO BOTTOM / fetch last value used, that is indeed not convenient for a multiuser environment.
The combination I use, and which has worked for years in a multiuser environment, is the following.
From the field's default value - say, for table Client - call the function for the sequence number, like
SerialNumber("Client").
The corresponding function is in the database stored procedure. It goes to a table of sequences, locates the sequence, locks it temporarily (as far as I can remember, this is the only place in the entire application where I explicitly lock a record - otherwise I implicitly let VFP do it, through the use of buffering), increments it, and returns the correct sequence value.
The primary keys (and the corresponding keys) are defined as integers.
Here is my function, perhaps it may be useful for you.
FUNCTION SerialNumber(tcSequence)
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
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)