3 Replies Latest reply on Aug 15, 2013 9:04 AM by philmodjunk

    How to generate consecutive serial number after deleting last serial number

    JeongwungJeong

      Title

      How to generate consecutive serial number after deleting last serial number

      Post

           Hello 

           I have serial numbered "order number" field in my program. Consecutive serial numbers are automatically generated as I add new order. But if I add new order after deleting last order, the next generated order number is the next number of what I deleted.   

           For example, I have got 15 orders and last order number is 15. If I deleted last order which number is 15 and added new order, next order number is become 16 not 15.

           After all, the order numbers are become nonconsecutive like this; 13, 14, 16, 17, 18.....

           Please help me to generate consecutive order number which is starting from last actual order number

           I use FMP12 Pro ADV

           Thank you

           Wung

        • 1. Re: How to generate consecutive serial number after deleting last serial number
          philmodjunk

               Instead of deleting the order, you may want to keep in in your table, but with a status field changed to "void".

               A script can be used to reset the next serial value, but a number of complications can ensue if you have multiple users all creating orders records at the same time. By the time you delete the record, another user may have already created another new order record.

               Thus it is often much safer just to Void the order.

          • 2. Re: How to generate consecutive serial number after deleting last serial number
            JeongwungJeong

                 Thank you for the explanation. But I don't get the exact meaning of "Void". Does it mean that just leave the order as a blank??

                 Now I can understand why serial number works like that. But for my case, the order number should be aligned with the running number given by the company's billing system.  So the numbers should be consecutive without any break.

                 I want to make a "add new order" and "Cancel order" buttons but I don't know how to make a script to generate consecutive number. And the generated order number should be next number of last order number or empty number in the middle of serial numbers.

                 Please help on this matter.

                 Thank you

            • 3. Re: How to generate consecutive serial number after deleting last serial number
              philmodjunk

                   "Void" means that you set a value in a field to mark that order as null and void. You could clear the other fields or leave them depending on what works best for how you do business. If you keep data in the fields, any calculations that are setup then have to ignore values from Voided orders.

                   Since you are synchronizing to a value from outside of FileMaker, you may want to set up a related table with a counter field that is incremented each time you create a new record. Keep an internally generated serial number different from this one for linking to other records on your table, but use the value from the related table each time you need to match this order against data from the billing system. A script can increment this counter and assign the value to your order when it is finalized not when it is created and then you can delete a newly created order without having to reset a serial number field.