5 Replies Latest reply on Nov 16, 2011 3:09 PM by philmodjunk

    How do I create ID#'s with a certain condition?

    iMakeFilez

      Title

      How do I create ID#'s with a certain condition?

      Post

      Hello.

      I created a registration database with an ID# field and wanted to set up that field in such a way that, when the persons Date of Birth is entered or if the Age field is less than or equal to 17, then that person would receive the child ID CH001 and that if there age is greater than or equal to 18 they would receive the adult ID AD001. I already set up the ID# field so that it increments when creating a new record but dont know much else.

       

      Another thing is that, although I set the ID# field to increment, I also want to set it so that it remains blank as long as a Date of Birth/Age has not been speciified, and then populate with either CH or AD ID# when the Date of Birth/Age has been speciifed.

        • 1. Re: How do I create ID#'s with a certain condition?
          philmodjunk

          There's a way to generate serial numbers "on demand" that works well even when you have multiple users doing this on a shared database all at the same time.

          But I need to know more about what you have in mind here.

          How does the sequence of these numbers work? Can the serial numbers have gaps in the series? Will they increment continually to the next larger number or will the series need to reset to zero? If so, under what circumstances will it need to restart at zero?

          Simplest to implement would be if you saw this series produced by the first 4 people to register:

          AD001
          CH002
          CH003
          AD004

          If this won't work for you, describe what will work and why you need it set up that way. (Why is very crucial here as we may be able to recommend alternative approaches that make this easier to implement.)

          • 2. Re: How do I create ID#'s with a certain condition?
            iMakeFilez

            How does the sequence of these numbers work?

            The program would try to find the current highest child/adult ID# thats already being used by a record in the database, and then, upon creating a new record, it would increment by 1 and create a new ID#. For example, if the highest child ID is CH0259, then it would increment and be added to the new record, CH0260, and the same would be applied for adults (if highest = AD0184, then increment = AD0185).

             

            Can the serial numbers have gaps in the series?

            No, there wont be any gaps in the series.


            Will they increment continually to the next larger number or will the series need to reset to zero?

            Yes, the increment will be continuous.


            If this won't work for you, describe what will work and why you need it set up that way

            I wanted it set up this way (CH0001, CH0002, AD0001, AD0002, etc.) because this is the way the setup has always been.

            • 3. Re: How do I create ID#'s with a certain condition?
              philmodjunk

              Please avoid using such fields as primary keys. "Meaning added" keys can be a major source of trouble for you that can easily be avoided. Use a Primary key defined as a serial number field in your parent record to link to other related tables. Use the field we are discussing here as an additional label field you can place on reports and layouts for humans to see and to use in sorting and searching.

              No, there wont be any gaps in the series.

              "won't be" and "can there be" are slightly different things. If a record is subsequently deleted, perhaps one that is not the most recently created record, You will get a gap in the series. Whether or not that is possible or a serious issues is something you will need to decide and then tell me why it is an issue so we can look at potential ways to handle such issues.

              Here's how to set up a "bullet proof" on demand serial number system:

              Define two new tables: ChildSerialNumbers, AdultSerialNumbers. I'll describe the set up for the ChildSerialNumbers table and leave you to apply the same set up to the second table for adult numbers.

              Give ChildSerialNumbers two fields:

              SerialNumber, Define it as an auto-entered serial number
              _fk_MainRecordID, Define it as a number field

              If you do not alreay have one, define a serial ID field, __pk_MainRecordID in your parent table. Link these two tables in a relationship like this:

              MainTable::__pk_MainRecordID = ChildSerialNumbers::_fk_MainRecordID

              Enable "Allow creation of records via this relationship".

              Do the same for the AdultSerialNumbers table.

              Now this script can generate a new serial number for the current record in MainTable "on demand". If such a serial number has already been assigned, The existing number is not changed:

              If [ MainTable::Age < 18 ]
                 Set Field [ ChildSerialNumbers::_fk_MainRecordID ; MainTable::_pk_MainRecordID ]
              Else
                 Set Field [AdultSerialNumbers::_fk_MainRecordID ; MainTable::_pk_MainRecordID ]
              End If

              An "OnObjectsave" trigger on the Birthdate field or an OnRecordCommit trigger on the data entry layout can perform this script to assign a serial number.

              Last step here, is to set up a calculation field in MainTable that adds the labeling text and leading zeroes:

              If ( Age < 18 ; "CH" & RIght ( "000" & ChildSerialNumbers::SerialNumber ; 4 ) ; "AD" & Right ( "000" & AdultSreialNumbers::SerialNumber ; 4 ) )

              The main reason for setting things up this way is so that multiple users can simultaneously enter data and yet see each record get a unique serial number. Many other approaches have problems avoiding duplicate values if you have multiple users entering data at the same time. If you are sure that there will only be one person entering such data at a time, a simpler process can be set up.

              Please note, that this approach can create a problem if you enter a birtdate for an age < 18 in error, then change the date to the correct adult birthdate. A new adult labeled serial number will be created, but now a "gap" has been created in the child series of numbers--which is why I am asking you to take a look at how serious an issue this will be should it happen, the srcripting needed to "close the gap" could be a bit tricky as other users may have already gone ahead and assigned serial numbers to their new records before you have a chance to spot and correct the error.

              • 4. Re: How do I create ID#'s with a certain condition?
                iMakeFilez

                This setup will only be used by one person to input data, so the simpler process would be better for me. 

                 

                As for the last paragraph, the thought of the person's ID changing when there age changes would be troubling. I'd be better if there was a way to stop the persons ID from changing when there age changes. 

                • 5. Re: How do I create ID#'s with a certain condition?
                  philmodjunk

                  As for the last paragraph, the thought of the person's ID changing when there age changes would be troubling. I'd be better if there was a way to stop the persons ID from changing when there age changes.

                  That would seem to defeat the purpose of having a different number series for each age range.

                  Why do you need a different sequence for each age range? (If this is to count the number of records in each age range, there are other ways to get such counts without needing to get this complicated with the ID numbers.)

                  You can define a calculation field, cAgeGroup, as: if ( Age < 18 ; "CH" ; "AD" )

                  Then you can define a self join relationship:

                  YourTable::cAgeGroup = YourTable 2::cAgeGroup

                  then Max ( YourTable 2::IDfield ) + 1

                  can be used to assign the next number in the specified age group's sequence.