4 Replies Latest reply on Jun 20, 2012 2:50 PM by philmodjunk

    Insert missing serial number record?



      Insert missing serial number record?


      I have a table for invoices. Instead of marking an invoice as void, I would rather delete the invoice, but this would leave a gap in the invoice ID's. Is there any way of inserting a record with the missing serial number field? So lets say I have invoice 1-100, and number 57 is missing, can I have a script to create a record without interfering with future serial numbers that are created?

        • 1. Re: Insert missing serial number record?

          I work with two (actually three) types in my invoices. First you create a new record and that's an offer. That has an Id.

          Only when an offer actually becomes an invoice I change the type to "Invoice"

          Only then an invoice number gets generated. And it's the max invoice number plus 1.

          That way you don't need to try and fill in gaps later on.

          An offer only becomes an invoice on the moment the work has been done and the client needs to be billed.

          • 2. Re: Insert missing serial number record?

            I recommend that you mark the invoice void. This is much less troublesome to maintain and reviewing your voided invoices after the fact--while discussing what happened with the person that voided it can be a useful way to gather feedback on how to improve your user interface. Also, the invoice number is often referred to as an informal way to determine when it was created or when it was processed (depends on when you assign the invoice number)

            I suppose the following script could determine that there's a "gap" in your series, but I don't recommend it for reasons I will list at the bottom>

            Show All records
            Sort Records [no dialog] //sort in ascending order by Invoice serial number
            Go to record/request/page [first]
            Set Variable [$K ; Invoices::InvoiceID]
               Exit loop if [$K ≠ Invoices::InvoiceID]
               Set Variable [$K ; value: $K + 1]
               Go to record/request/page [next ; exit after last]
            End Loop
            New Record/Request
            Set field [Invoices::InvoiceID ; $K ]

            Reasons this is not the best idea:

            1) If you have two or more users creating new invoice records at the same time, it's pretty easy to get two records with the same invoice number. Adding validations and code to handle that when it occurs to correct the issue can get a bit messy.

            2) As the number of invoices in your table increase, it will take longer and longer to add new invoice records.

            One option for minimizing the creation of Voided invoices, is to use an internal, hidden serial number field as the primary key for your invoices and assign the visible invoice number that you want to be gap free just before you print a copy and finalize the sale transaction that it represents.

            I've come up with a 'serial number on demand' trick that can be used in that way and it avoids the problem of multiple users producing duplicate serial numbers. If that sounds useful, let me know and I'll describe that method.

            • 3. Re: Insert missing serial number record?

              Thanks for your help. I think I will go ahead with the idea of having an internal serial number and then the printed invoice would have a separate number that we input. I see how after the table has more records it would take longer for a script to search for the new one.

              • 4. Re: Insert missing serial number record?

                I wouldn't have the user input the serial number--too much chance for user error if you do.

                Do it this way

                current table: Invoices

                __pkInvoiceID (auto-entered serial number, used in relationships to link to other tables)

                New table: InvoiceNumbers

                _fkInvoiceId (number field, use it in link to Invoices)
                InvoiceNumber ( auto-entered Serial Number )

                Define this relationship:

                Invoices::__pkInvoiceID = InvoiceNumbers::_fkInvoiceID

                Enable "Allow creation of records via this relationship" for InvoiceNumbers in this relationship.

                Now this script step can assign a unique invoice number to the Inovoices record once and only once. (Run the script again on the same record and nothing changes.)

                Set Field [InvoiceNumbers::_fkInvoiceID ; Invoices::__pkInvoiceID ]

                The first time it is performed on a given record, a new record in InvoiceNumbers is created and InvoiceNumber auto-enters the next serial number in its series. If it should be accidentally run a second time, no new record is created and _fkInvoiceID is set to the value it already has--so there is no change to any data.

                To display this invoice number on your Invoices layout, just use the field tool to add InvoiceNumbers::InvoiceNumber to the layout. It wil be empty until you choose to run the script with the above set field step in it.