2 Replies Latest reply on Oct 4, 2013 1:33 PM by MichelleH

    Increment serial number separately for each model?



      Increment serial number separately for each model?


           Hi, I'm brand new to FileMaker and trying to figure out how to automate a customer's existing serial numbering scheme.

           They sell several different models of knife, each model having a different alpha-numeric code, such as A1, A2, B5, B12, etc.  Each individual knife should be assigned a serial number that increments based on the model.  So the A1 knives are numbered A1-1, A1-2, A1-3, etc, and the B5 knives are numbered B5-1, B5-2, B5-3, etc.  Each time a new knife is entered into inventory, the user should be able to enter which model type it is, and the system should generate a serial number that increments from the highest serial number used so far for that knife model.

           What is the best way to set up such an autonumbering scheme?  Note that I am NOT trying to make this serial number my primary key for anything...I know what a bad idea that would be.

           I apologize if this question has been answered already, and I'd appreciate a link to such a post if that's the case. I did several searches and didn't find anything matching my problem.


        • 1. Re: Increment serial number separately for each model?

               It is a fairly often asked question.

               You've addressed the most important concern that I have when this question is asked: while you may need such an identifier to support existing systems used by your Client, you do not want to use such as a primary key as the "encoded info" can create problems down the road as it opens the door to your clients deciding to change the system of encoded info and then the needed change in your database can result in major issues with keeping related data correctly linked.

               There is a second concern to keep in mind, however, the systems typically used to meet this need also run the risk that two users trying to generate a new serial number for the same product line might get duplicate serial number values. So you need to put safe guards in place to prevent that.

               A fairly simple way to get the next serial number in your model specific series is to use a self join relationship that matches records by model. Then you can use the Max Serial value + 1 to assign the next value in the series to a new record in that Model line:


               Products::Model = Products|SameModel::Model

               Where Products and Products|SameModel are two Table Occurrences with the same data source table.

               Then: Max ( Products|SameModel::ProductSerial ) + 1 will return the next serial number in that Model line.

               Your full serial ID would actually be an auto-entered calc on a text field:

               ModelLetter & ProductSerial

               And you would place a "unique value" validation on this text field to guard against two users getting exactly the same value in this field.

          • 2. Re: Increment serial number separately for each model?

                 Thank you, that sounds like an excellent solution!  I appreciate the very thorough explanation.