1 Reply Latest reply on May 6, 2014 11:26 AM by philmodjunk

    How to auto-increment only if a previous field has been completed?

    terrypink

      Title

      How to auto-increment only if a previous field has been completed?

      Post

           Hi everyone,


           I was wondering, if I have two ID fields, and they are both serial numbers, one of which is entered by hand (let's call it FirstID) and the second is auto-generated (SecondID), how would I create a calculation that would only auto-generate the SecondID if the FirstID has been filled out?

            

           Thank you so much,
           ~Terry

        • 1. Re: How to auto-increment only if a previous field has been completed?
          philmodjunk

               Sounds like you need an "on demand" serial number. Instead of generating a serial number just because a new record is created, you would seem to need a number that is generated only when certain user actions are taken.

               Use two tables and two auto-entered serial numbers.

               Say your current table is called "Data". Define __pkDataID as an auto-entered serial number in Data and use it where needed as your primary key. For your "on demand" ID field, define a second table, SerialNumbers with two number fields:

               _fkDataID (number)
               SerialNumber (auto-entered serial number)

               link SerialNumbers to Data in a relationship like this:

               Data-----SerialNumbers

               Data::__pkDataID = SerialNumbers::_fkDataID

               Enable "allow creation of records via this relationship" for SerialNumbers in the above relationship.

               Add the SerialNumbers::SerialnNumber field to your Data layout to show the assigned serial number if one has been assigned. This is your "secondID" field.

               Set up the OnObjectSave script trigger on FirstID to perform this simple script:

               If [Not IsEmpty ( Data::FirstID ) ]
                  Set Field [SerialNumbers::_fkDataID ; Data::__pkDataID ]
               End IF

               This script will use the "allow creation..." option to create a new record in SerialNumbers, but only if such a related record does not already exist for the current record in Data. The creation of the related record, in turn, generates the serial number value you need as your "secondID".

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained