14 Replies Latest reply on Jul 6, 2016 9:27 AM by dtcgnet

    Serial Number issue


      We have a file that we use for invoicing. Each file has a serial number that is incrementally auto entered.


      On occasion we need to issue a dummy invoice or a back order. The back order needs to have the same number as the original order but with a letter added on. (i.e. 12345a, 12345b). It is not a huge issue to add the letter manually as it doesn't come up too often.


      So here is the problem. In order to get a new record for the back order we get a new original serial number. We can change the new record to have the proper serial number, but then the subsequent invoice will have the wrong number because it will skip the number that we changed. In other words, if I need to make a back order for invoice 12345, I would get a new record, so the new record would be 12346. I would change 12346 to 12345a for the back order. The next invoice, therefore would be 12347. I need it to be 12346 as that is the next number. Right now, what I do is go back to "manage database" and reset the next number to 12346 again, not very convenient or user friendly.


      I saw a way to have the number generated on "commit" rather than on creation, but it replaces the number that is there and I am still left with the same problem.


      Any ideas?

        • 1. Re: Serial Number issue



          You can created a table for serial numbers with one record with a field next order ID,

          Then created a script for adding a new order and get the value of the next order ID,  set a variable $OrderID, set the OrderID Field (in the order table) with $OrderID and then set the field next order ID (in the serial numbers table) $OrderID +1.


          For the backorders, use another script to set a field Backorder letter in the order table.

          Also create a field unique orderID in the order table.

          • 2. Re: Serial Number issue

            Forgive me, as I don't think I understand what you are trying to say.

            • 3. Re: Serial Number issue

              I'll create a sample file for you.

              • 4. Re: Serial Number issue

                Here's the sample file.


                I use the value one field to relate to the serial number table (I always create a value one and a value zero field in the tables).





                • 5. Re: Serial Number issue

                  My understanding is that the tax office gets jittery and suspicious when they come across non-sequential invoice numbering, so be wary. I suggest you consider either: (a) a separate layout for such invoices, on which you simply append the back order tag to the existing invoice number; or (b) a separate table with a manual invoice number field for back order invoices. Of these, option b is probably best because you only create a record when needed.

                  • 6. Re: Serial Number issue

                    There are many ways of generating invoice numbers that are truly sequential but these should not be primary keys since the invoice number would be generated after record creation. My 2 cents.

                    1 of 1 people found this helpful
                    • 7. Re: Serial Number issue

                      I totally agree with that. PK should be separate.

                      • 8. Re: Serial Number issue

                        In actuality , it is not the invoice number I am trying to make but a job number so there are no issues with its, thank you for your concern.


                        So the job numbers, as I said, are 1,2,2a,2b, 3 etc. I g what I am trying to do is find the next number, when needed an insert it at the end of the entry process without chsnging the number if it is a backorder.

                        • 9. Re: Serial Number issue

                          I think the solution I added in the sample file can work for you.

                          Let me know, if it should work differently.

                          • 10. Re: Serial Number issue

                            I agree with RickWhitelaw and keywords, that the Primary Key should be separate. Your users shouldn't really ever need to see them or modify the PK.


                            As for your Invoice Numbers, create a new TO, called InvoiceNumber_Highest (for instance). Sort InvoiceNumber_Highest in descending order by Invoice Number, and relate all records to InvoiceNumber. Guaranteed, the first related record in InvoiceNumber_Highest will have the largest invoice number in your system. Consider changing your method from adding a LETTER to adding a decimal equivalent (.1, .2, or .01, .02) Those get added manually anyway, correct? The Invoice Number for any NEW invoice would be equal to Floor ( InvoiceNumber_Highest::InvoiceNumber ) + 1.


                            By using "Floor", then if the highest invoice number was 1234.1, a newly created Invoice Number would be 1234 + 1 = 1235.

                            • 11. Re: Serial Number issue

                              dtcgnet wrote:


                              and relate all records to InvoiceNumber.

                              What does that mean? Can you show me a relationship graph that represents this? How can all records be related? How is it helpful that all records are related?


                              Thanks, in advance.

                              • 12. Re: Serial Number issue

                                Three images attached will show the specifics. How is it helpful? Since all records are related, and the InvoiceHighestNumber TO is sorted in descending order based on Invoice Number, then...the FIRST record related to Invoices will always be the Invoice record which contains the Invoice with the highest Invoice Number. HTH.



                                1 of 1 people found this helpful
                                • 13. Re: Serial Number issue

                                  Thank you so much for helping me understand.


                                  I assume the "X" relationship means all records are related and therefore, the sole purpose of that TO is to determine the highest invoice number, is that correct?

                                  • 14. Re: Serial Number issue

                                    Glad you got it working. Yes, that's what the X (Cartesian) relationship is for in this case. There are all sorts of uses for it, but in a case like yours, it fits the bill nicely and should be very fast.