3 Replies Latest reply on Jul 23, 2013 7:27 PM by LaRetta_1

    Making Custom Serial Numbers



      Making Custom Serial Numbers


           Hello Everyone,

           I have two tables "Customers" and "Files". The customers table contains customer info and each customer is given a regular 4 digit serial number. The files table contains files that belong to a specific customer. What I would like to achieve is to give each "file" a serial number containing of the related customers reference number a - then a 3 digit number. The problem I am having though is that I would like each customers files to be numbered 001 and so on. For example:


           Customer 0056 would have files 0056-001, 0056-002, 0056-003 and so on.

           Customer 1234 would have files 1234-001, 1234-002, 1234-003 and so on.

           Does this make sense?


        • 1. Re: Making Custom Serial Numbers

               Hi tshirts,

               If Files are deleted, you will have holes in those incremented child serials.  And if a file is accidentally assigned to the wrong Customer, you will have to change your File's PRIMARY KEY which is never good.

               I suggest against such an approach and would suggest instead that you keep the CustomerID in it's own field in Files and simply let the record number be the 'iteration' of children when viewing from Customers.  If you want to know how many files a customer has, you can count them using summary (in Files) or Sum() calculation in Customers.  Normally however, simply placing the record number (which is @ in versions prior to 12 and {{RecordNumber}} if 12, accessed from Insert > Other Symbols new in 12), is quite sufficient to show the incrementing of the lines.

               The purpose of unique primary keys is NO PURPOSE.  They should be meaningless.  By attaching meaning, you risk it breaking in multi-user mode since it is difficult (but not impossible) to prevent record-lock. :-)

          • 2. Re: Making Custom Serial Numbers

                 Hi LaRetta,

                 Thank you for your response! I understand your reasoning for staying away from the incremented child serials, but unfortunately due to reasons outside of this database (A system already in place) I need to try and make it work that way. Is it possible?

            • 3. Re: Making Custom Serial Numbers

                   Then you need to use a Serial table and ability to create new records must be removed completely and handled only via script.  You create a new record in the Serial table, commit it, take the serial and use it in your concatenation.  In this way other Users cannot use the same serial.

                   I suggest you search for 'serial table' over on http://fmforums.com/forum/ ... I know it's been discussed there many times and there are even sample files about it - one of the many great things about that forum.