I have a procedure to generate sequential unique values for a field used as a Candidate key when new records are added to my parent table. It works fine when new records are added as long as a new record hasn't been deleted. My procedure searches the table for matches in the candidate field; if it doesn't find a match it increments by one and returns the value to the field. If a new record was just added and then deleted and you try to add another new record an error message indicates a Unique_ID violation. I understand if the record were recalled instead of packed it would indeed be a violation of uniqueness and thus why I'm getting the error message. It seems my procedure doesn't compare the return value with deleted records (deleted but not yet packed).

Lincoln,

You have undoubtedly gotten many good responses to this questions, which I do not have time at the moment to read.

The fact that you have marked a record in your table for deletion does not mean that it has disappeared. When you generate the next ID with SET DELETED ON, the procedure ignores the deleted records when determining the next ID -- thus if the last record added to the table is deleted, the procedure will attempt to reuse that number -- hence the unique id violation.

You can SET DELETED OFF while getting your next id -- that will make certain that deleted records are included in any calculation or, try this quick and dirty, but very fast, solution,

*******************************************************************************
*
*   Function for getting the next unique record id.  The id is a character
*   string from "001" to "999" for illustration purposes.  The routine is
*   styled as a function rather than a procedure since it returns a value.
*
*******************************************************************************
FUNCTION NewID

LOCAL laID, lcNextID
DIMENSION laID[1]

SELECT ;
    a.myIDField ;
    from myTable a ;
    order by a.myIDField descending ;
    into array laID
    *-- Find out if anything is in the id array
IF _TALLY = 0 .OR. EMPTY(laID[1])
    lcNextID = "001"
ELSE		
    lcNextID = ADDZER(VAL(laID[1]) + 1,3)
ENDIF

RETURN lcNextID

The trick to this routine is the instruction in the SELECT statement to order the results of the SELECT-SQL "descending". This ensures that the newest ID is at the top of the array.

Here is the ADDZER() fuction called by NEXTID():

*:******************************************************************************
*:
*: Function ADDZER()
*:
*:******************************************************************************
FUNCTION ADDZER(luVar,lnVarLen)
* OVERVIEW: Function pads luVar with "0"s if luVar is numeric or character
* variable which can be converted to a numeric variable. Input
* includes the variable and length of the padded character to be
* returned.
* SYNTAX: luVar A character or numeric value which contains a number
* expression to be padded
* lnVarLen The length of the padded variable to be returned. If
* the number passed in luVar is "123" and lnVarLen is 5
* the return will be "00123".
*/
DO case
* Returns a string variable.
CASE type("luVar")="N"
* Stringify luVar if it is numeric.
luVar=str(luVar,lnVarLen)
CASE type("luVar")="C"
* Translate luVar to string of lnVarLen length if luVar is character var.
luVar=str(val(luVar),lnVarLen)
OTHERWISE
* If luVar is some other type of variable, just return a string of "e"s.
* Since the calling function expects a character return, to return
* something other than a character may result in an error. An inspection
* of the return will indicate an argument error.
?? Chr(7)
RETURN(replicate("e",lnVarLen))
ENDCASE
RETURN(replicate("0",len(luVar)-len(ltrim(luVar)))+ltrim(luVar))

I wrote this long ago, probably in Clipper, so it has syntax I probably would not use today -- but I'm just to lazy to change something that works.

I used to use a variety of methods to keep track of the next ID, including having a separate lookuptable, and a global var which was incremented to keep track of the ID. All of these had their various advantages, but this approach has proven to be the least troublesome and the most exact since it _never_ loses track of the nextID. It can't.

Once you have gotten your next ID, I suggest you use INSERT-SQL to stick the new record in the table.

regards,