>Probably wrong category but anyway ... I've done a couple of inventory control systems and, for every article I usually have a unique id number, given by the system, which is usually invisible to the user, and also a user assigned part number.
>
> Because of the fact that the user may change this part number, I base all my reports and query for operations involving a given part, on my system assigned unique id. I'm starting a new one and was wondering if this is the best approach.
>
>I know my new client is gonna be changing his part numbers, they asked me for a part number history tracking.
>
>I'd appreciate if you guys shared your experience with me, TIA

The system that I use has what to me are a couple of advantages. One it is very very simple. Two, it doesn't require the row or table to be locked. Three, it doesn't require an additional table to store the current or next key value. Four, it works unfailingly in multi-user environments.

In the stored procedures for the DBC, add a function, I call mine newid(). Here is the code for newid()

PROCEDURE newid
LPARAMETERS ccolumn, ctable
SELECT MAX(&ccolumn) FROM (ctable) INTO CURSOR curmaxid
cretval="curmaxid."+"max_"+(ccolumn)
RETURN (&cretval)+1

In your table, select the primary key column, and in the "Default Value" field insert;

newid("yourcolumnname","yourtablename")

Now every time you add a record, it will automatically increment the primary key value. It will do this regardless of where you add the row (SQL statement, method, whatever)

Now for the important part. In the user interface of your application, you don't want to use the table directly (especially if you are in a multi-user environment) Instead, create a view of the data, and bind the view to your UI. Now as the user adds records, they are added to the view not the table. They are written to the table when you issue a tableupdate(). **NOTE** You must open your view in a buffered mode for this to work.

You will want to take some time to read the help files on tableupdate(), and get familiar with row and table buffering.

This is only one way to do it, but it works very very well with a minimum of problems.

Regards,

Jason