Generating custom serial numbers

[elford]

I have 2 fields defined for a serial number in my database: "SerialNumber" and "JobNumber".

On creation of a record, the SerialNumber automatically is assigned the next available serial number, based on the field definition specified in the Define Database dialog -- SerialNumber is a field which contains incremental, unique numbers.

The JobNumber field is currently set up to be a calculation that appends a letter in front of a serial number -- this field is automatically filled in by the calculation. The specific letter appended comes from a Category field in the database.

Example:

New Record is made

Serial number automatically set to 114

Category is Education

JobNumber is calculated to be E-114.

However, this system doesn't work right now -- the JobNumber field's calculation is executed on record creating, but the user has not been given time to input the category data, so the JobNumber fields don't know what letter to append.

Is there a way to have the JobNumber calculation execute after the record is filled in and committed? Suggestions on other ways to handle this situation are welcomed as well.

[aldipalo]

JobNumber = autoenter calc, uncheck do not replace if empty, replaces existing value, indexed

Left (::Category ;1) &"-"& :Serialnumber

That should do it. The calc should immediately add the serialnumber and then once the category is selected the 1st letter should be added as well.

[elford]

Perfect! Thanks!

[LaRetta]

There is a real potential danger here ...

If later a User realizes they have assigned the wrong Category - and if related records have been created which use this as their foreign key, then changing that Category will BREAK that relationship. It would be safer to keep ANY data (which may change for ANY reason) out of your unique keys.

If you need Users to see the serial with the Category prefix, then DISPLAY it for them as calculation but, once an ID is created, it should NEVER be allowed to be changed. So if you must concatenate Category and serial, use your serial as your unique key throughout your file.

[aldipalo]

Hi LaRetta:

I completely agree. I thought this was only used as a JobNumber and not an ID key field. If it is then it needs to be a static field that is not changed.

Al