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
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.