Creating a unique ID number based on a MainID number and a Sub-MainID number entered by the user.
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!