3 Replies Latest reply on Jan 17, 2012 4:01 PM by philmodjunk

    Conditional Auto-Increment on non-standard value



      Conditional Auto-Increment on non-standard value


      Hi all,

      Sorry but I can't quite find what I am looking for in manuals or in the forum - I apologize if this post is repetitive.

      I have a collection of plant specimens, each previously assigned with an Accession Number - this is a unique identifier of the specimen in the collection.  Each number is prefaced with the letter "B" (ie: B1, B2, etc.).  We are in the process of entering all these specimens into our Filemaker database into a text field "Accession Number", because to go through the existing collection it can be entered on a random basis (ie: entering all the specimens of 1 species could have specimens assigned B1, B4, B834, B21323, etc.).

      However, for new collections we'd like to assign the Accession Number as they are entered.  So if this field could have a checkbox beside it that says "Auto-increment", and have the number automatically increment from the previously selected record upon new record creation, that would be ideal.  So that when we go back to adding in older collections, we can again enter them manually on an as-need basis.

      Is there a script or calculcation I can write for this, so that when a checkbox is selected the number auto-increments (and it needs to have the 'B' preface), but when it is unchecked it can be manually entered?

      Thanks again,

        • 1. Re: Conditional Auto-Increment on non-standard value

          First, do not use this accession number in relationships in your database. Put it in one table of your database so you have it for searching etc. but use an actual serial number field for your relationships. This way, you can correct errors in the accession number without losing connections to any related records in your database.

          Second, set a unique values validation rule on this accession number to catch situations where you manually enter a value that is not unique.

          Third, you may want to change the field from text to number if "B" is the only nonnumeric character ever used with this value. A number field can store the letter, but as a number field you will be able to sort records in correct numerical order on this field if that is something you may need to do. You can also leave the letter out of this field and just add it in on the layout as layout text if you want.

          Will you be entering records for brand new specimens at the same time you are still logging in existing specimens?

          If not, I'd keep this field as a simple number field until all existing specimens have been logged, then add a serial number setting on the field specifying a next serial value at least 1 greater than the largest accession number.

          If you want to log new and existing specimens at the same time, we'll need to take a more sophisticated approach.

          • 2. Re: Conditional Auto-Increment on non-standard value

            Hi (again) Phil,

            I completely agree with many of your points:
            1) I do not use this number in relationships at all, especially because we know we have duplicate numbers in the collection we must slowly fix.
            2) We have a unique values validation rule set so a data entry member is notified when there is a possible duplicate creation.

            I did not know that a number field could store a letter - that is great!  Thank you.

            It is unlikely we will be able to log all existing specimens before we start to process new ones, as our collection has over 200,000 specimens collected over 150 years..

            We will not be entering records for new specimens at the exact same time we are still logging existing specimens (as in same data entry session), but we need to be able to switch back and forth.  So if I come in one day to enter new records, I can click this check-box (or something) and work away, but if another person comes in later in the day to work on existing specimens, they can un-check this box (or something) to enter manually. 

             Is this kind of thing possible, and/or very complicated?

            • 3. Re: Conditional Auto-Increment on non-standard value

              There are several options. One is simple, but best used for single user situations where you never have more than one person logging new specimens at the same time. The other is more complex but ensures that each new auto-numbered specimen will be unique even if multiple users are logging specimens simultaneously.

              Both use a script as part of the process.

              Simple but best for single user:

              Use this script to create a new record with no value entered in the field:

              New Record/Request
              Set Next Serial Value [Yourtable::AccessionNumber ; YourTable::AccessionNumber - 1]
              Set Field [YourTable::AccessionNumber ; "" ]

              Use this with AccessionNumber set up as an auto-entered serial number. Make sure that your next new value is large enough that it won't match an existing, but not yet logged specimen. (May not be easy to determine this value.)

              This script creeates a new record but then resets the serial value back by one and clears the auto-entered serial number from the field.

              More complex but multi-user safe:

              This method generates a new serial number in the series "on demand" when the user clicks a button to run a script.

              In your specimen table, define an auto-numbered serial number, __pk_SpecimenID, to uniquely identify each specimen record if you have not already done so. This field is NOT the accession number. Define a new table, AccessionNumbers with two fields: AccessionNumber and _fk_SpecimenID. Define AccessionNumber as an auto-entered serial number and the second field as a simple number field.

              Create this relationship:

              Specimens::__pk_SpecimenID = AccessionNumbers::_fk_SpecimenID

              Enable "allow creations of records via this relationship" for AccessionNumbers in this relationship.

              Then this script can assign a new unique accession number "on demand" when run from a button on your Specimens layout:

              Set Field [AccessionNumbers::_fk_SpecimenID ; Specimens::__pk_SpecimenID ]
              commit record
              Set Field [Specimens::AccessionNumber ; AccessionNumbers::AccessionNumber]

              The first set field triggers the creation of a related record and this auto-enters a new AccessionNumber in the series. the second set field step copies it into the accession number field in specimens. Since the numbers are generated via auto-entered serial numbers, they should be unique even if multiple users execute this script at the same time.

              In both cases, you may have to guess at to a "safe" next serial value for new specimens. Make sure to keep the unique values validation in place with validate always specified on Specimens::AccessionNumber and if the script tries to enter a duplicate value, the validation rule will throw up an error message and you can then reset the next serial value to a larger value.