And is it that this number starts at one for the group of people linked to a specific project?
If so, how have you set up your relationships to link a people record to a project record?
Max( SecondaryID ) + 1 will always return 1 if SecondaryID is zero or empty as this syntax only references the value of SecondaryID in the current record.
That makes sense. The SecondaryID is not project specific, rather it is assigned if someone is working on one specific type of project. You can think of it as a security clearance ID, saying that if someone is assigned to a confidential project, they must be assigned a clearance ID.
And can the same person be assigned to more than one "type"? If so, how would that affect these serial number values?
No. There is only one type. Globally, each person gets a serial number automatically when they are added to the database, and then if at any point they are assigned to a confidential project, they need to be assigned a second SecurityID.
So you have one and only one additional serial number series? Out of curiosity, why does it need to be sequential?
Here's one method you could set up.
MainTable::__pkMainTableID = ConfidentialSerial::_fkMainTableID
__pkMainTableID is your original auto-entered serial number. Define a field, __pkConfidentialSerialID as the secondary auto-entered serial number.
IN the above relationship, enable "allow creation of records via this relationship" for ConfidentialSerial.
Then, whenever you need to assign someone a Confidential Serial number, use this one line script:
Set Field [ConfidentialSerial::_fkMainTableID ; MainTable::__pkMainTableID ]
This creates a new related record in ConfidentialSerial and that new record auto-enters the next serial number in that sequence. Please note that if a related record already exists, and thus the person already has this serial number, no change in your data takes place.