5 Replies Latest reply on Oct 7, 2013 12:04 PM by philmodjunk

    Generate Secondary Serial Number



      Generate Secondary Serial Number


           Hi all!

           I am working on a database managing people and projects. Each person gets a unique ID no. when they are first entered into the DB, but for certain projects, we need to generate a second unique and sequential ID for these people if they are assigned there. Ideally, I would love to have a button to press in the contacts DB that would generate this number. So far i have tried using Max( SecondaryID ) + 1, but it starts at one on every record. At this point there will not be more than one person at a time accessing the DB, but I would like to have that possibility available in the future.



        • 1. Re: Generate Secondary Serial Number

               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.

          • 2. Re: Generate Secondary Serial Number

                 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.

            • 3. Re: Generate Secondary Serial Number

                   And can the same person be assigned to more than one "type"? If so, how would that affect these serial number values?

              • 4. Re: Generate Secondary Serial Number

                     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. 

                • 5. Re: Generate Secondary Serial Number

                       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.