Using two independent auto-generated serials in one table
My situation is this:
Records in my solution will not be deleted after being committed, instead rather than deleted, they will be archived. This is for auditing purposes.
I want to use an auto-generated serial for my primary key that is generated on record creation or request eg kp_customerId. This will be used for defining relationships only and not viewed by the user.
I would also like another serial that has meaning in the 'real world' such as CustomerID eg CUST000001 to be created on record commit. This will be sequential with no gaps i.e if I have ten customers, they will have ID's CUST000001 through to CUST000001. I hope this makes sense.
Is it possible to have two auto-generated serials to running parallel in one table?
If not I am thinking of using a global variable to hold the last used CustomerID that is incremented when a newly created record is comitted. This value will be auto-entered in the CustomerID field again when a newly created record is committed.
Anyone got any ideas on how I go about this using the simplest solution?
I am using Filemaker Pro Advanced 10.
Thanks in advance