>>>>>>>>>I am working on a Customer Info form and I want to assign a unique customer number when a user chooses to add a new customer. If I do anything like a CALCULATE MAX() on existing customer numbers, the record pointer moves and a TABLEUPDATE() is automatically issued.
>>>>>>>>>
>>>>>>>>>Any ideas on the best way to do this?
>>>>>>>>
>>>>>>>>MAX(), COUNT() and a few other commands move the record pointer. The best way to assign a unique key is with a procedure stored in the dbc that pulls the unique value from table that stores the next unique value for all of your tables that require a unique value. This may seem like a pain and overkill for the time being, but as your apps and tables grow, it becomes evident that this is the only way to go. So you have field that is not the primary key but needs a unique value anyway? Define it as candidate and use the same teqhnique. Tastrade contains a stored procedure and a table that does this. Another tip that has quickly become the 'right' way to do things: all primary keys should be surrogate. That is, the ser should never see them, and they should have no real world value.
>>>>>>>
>>>>>>>Hi Erik,
>>>>>>>
>>>>>>>I found the stored procedure -- NewId but I'm having a hard time finding where in the Tastrad app it is actually called. Is it just me or does anyone else find the Tastrade example a little over complicated and convaluted?
>>>>>>
>>>>>>
>>>>>>NewID() is called called from the Default Value in the table designer.
>>>>>>You need an IDS (or something) table in the DATABASE
>>>>>>
>>>>>>Check out the solution app that comes with VFP it has a good example of this...
>>>>>>Good Luck
>>>>>>Jerimi
>>>>>
>>>>>Thanks to both you Jerimi and Erik for your replies. I've created the stored procedure in my database and created an IDS table as well. Instead of assigning the key in the fields Default Value I assign just before the TABLEUPDATE() with REPLACE Suppliernum WITH NewId(). This works great in development but when I compile to a EXE I get the error 'Program NewId does not exist'. Am I missing a particular setting to make the app look in the stored procedures? My PATH includes the data directory.
>>>>
>>>>Is there a reason that you don't want to specify the procedure in the default value? leaving this out opens you up to risk of a record being added to the table without getting the unique id. Putting it in the default value, allows you to add records in the command window, browse window or wherever and have the unique key be completely invisible. Unless you have specific reasons for not doing this, I would rethink your strategy.
>>>
>>>I allow the user at any time during an add to abort adding the new record. If I put NewId in the Default Value a unique id is used up each time the user starts an add process, whether they save it or not. The way I'm doing it the unique id is only assigned during the save of the new record.
>>
>>Why does it matter if a few key values get wasted? If your keys are type DOUBLE, you'll have a billion or so values, and if you use CHARACTER, you have as many as you want.
>
>I guess it doesn't really matter but my initial problem still stands. I am calling the stored procedure, NewId(), from one of my forms and I get the error 'Program NewId does not exist'. Why?

I suppose that the stored procedures aren't "built into the project" like our other code, and I don't know what you would say in a SET PROCEDURE in such a case. They are meant to be called from the triggers. If wasted key values isn't a problem, I use NewID() in the default of the keyfield, as it is used in tastrade. By the way, as you may have seen in my earlier thread, one effect of making keys this way is that, unless you have other default values not created by a stored procedure, you end up using APPEND BLANK instead of INSERT - SQL. You still have a unique key (not a blank) at all times.