2 Replies Latest reply on Nov 14, 2011 9:17 AM by philmodjunk

    Creating a unique ID number based on a MainID number and a Sub-MainID number entered by the user.

    RobertRaub

      Title

      Creating a unique ID number based on a MainID number and a Sub-MainID number entered by the user.

      Post

      Hello All,
      I have created a part numbering system that goes as follows ( X.XX.XXXX). X is the MainID number (which has 9 posible values (no 0 value allowed)) and is a drop-down list value. XX is the Sub-Main ID (which has 99 possible values (no 00 value allowed)) and is a drop-down list value based on the MainID value choice. XXXX is the Unique ID number with 9999 possible values. The composite number value field, X.XX.XXXX is set to validate as "not empty" and "unique".
      Obviously I can simply generate the unique, XXXX, number by having FMpro create a serial number and increment it by 1 for each new record created. This would only create 9999 part numbers overall though. One would enter new part numbers and it would go as follows for example. 1.05.0001, 6.11.0002, 8.23.0003, 7.45.0004, 1.05.0005, 7.45.0006... and so on.
      I would, instead, like to have each new record entry be able to determine the currently stored max value for each X.XX part number combo and then increment by 1 the XXXX value associated with that combo. In my example above the first 7.45 part number would have been given 0001 and the second entry 0002. which then should allow 9 x 99 x 9999 possible part numbers.
      Thus, the database could have many 7.45 designated parts, for example, but each of those related part numbers would be made unique by the XXXX value starting with 0001 for each X.XX combo. Hope this makes sense to someone out there. Appreciate your thoughts and comments. Thanks!

        • 1. Re: Creating a unique ID number based on a MainID number and a Sub-MainID number entered by the user.
          RobertRaub

          Hello All,

          Been trying to sort thru this and it seems a script is in order (I am new to all of this so be gentle). I'm thinking that when a user enters the MainID and the Sub-MainID via drop-down you would then trigger a script (perhaps OnRecordCommit) that would 1. find all records using the MainID and SubMainID values from the current record as find criteria. 2. Find the max value of the UniqueID of the found set. 3. Increment the max value by 1. 4. enter that incremented value in the UniqueID field of the current record. 5. End.

          Any thoughts on this approach? Thanks

          • 2. Re: Creating a unique ID number based on a MainID number and a Sub-MainID number entered by the user.
            philmodjunk

            One thing that you do not explain is why you need such a complex number. It can certainly be set up and you've figured out one possible approach, but a simple, single part serial number that starts with one and increments with each new record is MUCH simpler to set up and far safer to use when your database is hosted over a network where two or more users might be generating such ID's at the same time. Such a serial number would not be limited to 4 digits but would simply keep incrementing, generating as many digits are needed up to the maximum number that can be stored in a number field in FileMaker.

            Often, such numbering systems as this are to keep your database compatible with an existing identification system that you cannot change. If so, you can implement such a numbering system, but do not, use the field to link it to related records. Instead, keep it as a "label" field in your main table so it can be used in searches and sorts, just don't make it a key field in your database.

            To better manage such an numbering system, I'd use 3 separate fields, one for each portion of the number and use either merge text or a calculation field to combine the 3 parts into single value for display, searching and/or sorting records. That also makes it possible to set up relationships that make it easy to identify the current maxium value for a given series of values without having to perform a find each time.