5 Replies Latest reply on Mar 15, 2015 9:48 AM by GuyStevens

    Reset of Serial Numbers By Each month



      Reset of Serial Numbers By Each month


      Hello, smiley

      I am stuck while creating the invoice numbers. 
      I am currently creating a new database with the invoice numbers. 
      I would like to create a serial number the following way.
      The serial number should go back to 01 with the start of each month. 

      Right Now field set to Auto-Enter field so its always increment the value by 1

      but i want that when i create the new invoice its start from 01 to unlimited values (because i am not sure how many invoices i am going to create. ) 

      F.ex. 01-01-2015 ID=01

              01-01-2015 ID=02

              02-01-2015 ID=03 till 31-01-2015

      in next month its has to start from the beginning

              01-02-2015 ID=01

              06-02-2015 ID=02 till 28-02-2015

      I have the following fields at the moment: 

      Date: Creation Date
      Serial Number: Auto-enter serial 

      whenever i delete any data and create new one its increment my number not replacing my old data number too.. 

      How should I update that one. 

      Thanks for clarifying. 

        • 1. Re: Reset of Serial Numbers By Each month

          This is not a good database design for uniquely identifying invoices. A simple serial number is a better design option.

          But there is a script step that can reset the next serial value back to zero or any value you set it up to specify. There is also a special function, getnextserialvalue, that can return the value that a field with this feature is set to assign to the next new record.

          Other thoughts on invoice numbers:

          For audit control purposes, it's best to never delete an invoice record but to mark an invoice you were going to delete as "void". This avoids "gaps" in your serial numbers. You can also set up a system that only assigns such a value to the invoice (not for use in relationships) just at the moment the transaction is finalized not when the invoice is first created.

          • 2. Re: Reset of Serial Numbers By Each month

            can you write example


            • 3. Re: Reset of Serial Numbers By Each month

              Hi day mond. 

              This is actually pretty easy to create.

              You can create a new table occurrence of your Invoice table and relate it to your invoices by the year and month. 
              That way you can see the highest invoice number for that month in the related table and create a number that is one higher.

              When there is a new month and therefore no existing invoice numbers you start back from 1.

              This explanation might not make a lot of sense so I quickly made a youtube video to display how you could create something like this. 

              I recently taught this technique in my Udemy Filemaker course for Invoicing
              However there it only restarts each year. 

              • 4. Re: Reset of Serial Numbers By Each month

                Hi Guy, good to see you back, but there's a cautionary comment that is needed for your method: When you have multiple users using this method to create new invoices all at the same time, there's a small but significant chance that you'll get two invoices with the same number. Essentially, two different people run the script (or the auto enter calculation evaluates) at the same time and thus both get the same Max value returned for the invoice number, add one to it and get the same invoice. For invoice numbers, this is not a good thing.

                It's better to rest an auto-entered serial number via a script that runs on a regular schedule during "off hours", than to risk such an outcome with this other method.

                • 5. Re: Reset of Serial Numbers By Each month

                  Hi PhilModJunk, good to see you are still here providing your wisdom :)

                  It's true that this might be tricky with multiple users making invoices at the exact same moment. And there is a possibility of this going wrong all be it a very small one.
                  If your system doesn't have to many users then this might be sufficient. If not then PhilModJunk's system might be a better idea.

                  The only problem with PhilModJunk's system is that when you create an invoice and send it, it might not have an Invoice number yet. And so you can't send it yet to your client. You would either have to wait for it to get an invoice number in the off hours or force the invoice numbers to be made at that moment. 

                  The user might forget to create the invoice number, but that could be checked with a script so you are not able to email an invoice out if it doesn't have a number.

                  But this is all a bit of work that might not be needed if you are just working a small company with few users.

                  I guess it depends on the case at hand.