3 Replies Latest reply on May 11, 2012 6:15 AM by philmodjunk

    Auto enter serial invoice little problem



      Auto enter serial invoice little problem




      I'm just new to FMPA 12. For my invoices I use the following calculation:

      Right ( "00" & Invoicenumber ; 3 )  & Right  ( Year ( Datum ) ; 4 )

      With this result: 0012012   (for first invoice of 2012).

      Now my problem is with the field type of 'Invoicenumber'. It's an auto enter serial type. I would prefer when a new record has been created and it has been a mistake, that the invoicenumber would remain by the last invoicenumber when deleted.


      Example: Invoicenumber is at #11, I create a new invoice, so # 12 has been asigned to this new Invoice.

      I delete this one. When I create a new one now, I would prefer it to be again # 12 and not # 13.


      Since i'm very new to this... i have no clue how to this... (hope my question is clear).


      Any suggestions?

        • 1. Re: Auto enter serial invoice little problem

          A script could reset the serial number value to one value less to eliminate the gap in the invoice number sequence, but this must be done with care as it could result in invoices with duplicate numbers if not implemented correctly. If your understand how to write a script, look up Set Next Serial Value to see how this could be done.

          A simpler solution may be to keep the invoice record created in error, but change a status field in it as "void". Then you have a sequence of Invoice numbers that are free of gaps--which makes auditors happy.

          Another method is to keep the Primary Key for linking invoices to other tables such as a line items table separate from the invoice number printed on the invoice. The primary key is generated when you create the invoice, the invoice number is generated via script from a related table when you print or mark the invoice as "complete".

          • 2. Re: Auto enter serial invoice little problem

            Hi Phil,


            Thank you for this information! This gives me a clear view on my problem.

            As you suggest in the last option, I won't like to use the primary key for this. Could you help me with finding a script to generate such an invoice number and that resets every year?


            Thanks for your support! Very useful for a newbie.

            • 3. Re: Auto enter serial invoice little problem

              The trick I came up with for "on demand" serial numbers is to use a related table to generate the serial number. You create a record in this table at the time you need the serial number.

              Add a new table to your system and link it to invoices like this:

              Invoices::__pk_InvoiceID = InvoiceNumbers::_fk_InvoiceID

              Enable "allow creation of records via this relationship".

              Define: InvoiceNumber as an auto-entered serial number in InvoiceNumbers.

              Use this script step, performed from your invoices layout, to generate your invoiceNumber to use in your calculation:

              Set Field [InvoiceNumbers::_fk_InvoiceID ; Invoices::__pk_InvoiceID ]

              This generates invoice numbers reliably for both single user and multi-user (where two or more users might be doing this at the same time) systems. If you accidentally run this script twice on the same invoice, no harm is done and the number does not change as this step creates a new related record only if one does not already exist.

              You will still need to reset this serial number's next serial value back to 1 at the start of each new year if you want that type of series here for this number.