10 Replies Latest reply on May 23, 2012 3:05 AM by willrollo

    Creating two sequential invoices from a single order/job

      Title

      Creating two sequential invoices from a single order/job

      Post

      Hi
      See attached ERD to see my relationship structure.  

      The user creates an Order (has its own sequential serial number), from a custome rlayout, based on customers. 

      The contents of the quoteslorders/invoices comes from the invoice details table. The actual serial numbers for each of these comes from their respecrive tables (quotes/orders/invoices)

      On this order, that has a lines portal to products which will be filled with several records containing the ordered items, the user then is supposed to raise/create an automatic invoice for a deposit. This Invoice will then be raised using the same order number, customer details etc from the order.  Once this has been done, the user can then convert the original order to an invoice - keeping the same order number but a new invoice number. The invoices must not skip, but be the next serail.

      The problem I am having is creating two sequential invoice numbers, for the same order number. Teh clsoest I have got it working is that the order numbers skip one.

      I also attach my script that needs a bit of work!

      I suppose in a way it is like creating a supplmentary invoice to the original order. The orders are like Jobs for this company..

      Thank you

      2012-05-21_02.52.35_pm.png

        • 1. Re: Creating two sequential invoices from a single order/job
          /files/b2f2dbc13b/2012-05-21_02.57.44_pm.png 614x506
          • 2. Re: Creating two sequential invoices from a single order/job
            philmodjunk

            Why two invoices for the same order? That seems odd here... If you are collecting a deposit, that would represent a specific Payment against your invoice, but I don't see the need for creating another invoice just for the deposit transaction--I'd see this as the first of two or more payments to log as payments in a payments table linked to the same invoice details record.

            In a multi-user system, getting two sequential serial numbers for your two invoices may be nearly impossible to achieve as other users might be creating invoice records at exactly the same time.

            • 3. Re: Creating two sequential invoices from a single order/job

              Hi Phil

              The reason is that with this business, a deposit is paid when the order is placed. The final invoice is issued up to 6 weeks later on completion of the order. As the deposit has to be taxed at our Uk rate of 20%, then it has to have its own invoice. The other year when there was a vat rate change, our invoices were all messed up as the deposit and final invoice should have had different vat rates.

              But also, if the client is inoviced and then they want to add somehtibng to the order, or if say something was missed off the original invoice, then a new invoice will need to be raised, with its own invoice number- BUT it must go under the same order number as the order number is just the term we use for Job number..

              Does that make any sense?

              There are only three employees who use this database...!

              Any thoughts much appreciated. Also, did you manage to look into my payments post request??

              Thank you again!

              • 4. Re: Creating two sequential invoices from a single order/job
                philmodjunk

                Even with two employees, sequential INVOICE numbers are very difficult to guarantee. Do they really need to be sequential?

                If the two invoice numbers do not need to be sequential as long as they have a common order number, but the complete series of all invoices is without any "gaps", then what you have looks like it should work. I don't see any reason why an invoice number would be skipped--more likely another user created a new invoice record and generated that "skipped" invoice number in their record.

                Also, you appear to have two fields that serve as primary key fields in the same table:

                Invoice_Details_pk

                and

                PrimaryKey

                There's no immediate harm in that. It's just an uneeded complication in your design.

                • 5. Re: Creating two sequential invoices from a single order/job

                  Thank you

                  As you say, it is more that I do not want any gaps in invoice numbers - I dont think I was clear earlier - I just dont want any gaps in numbering. However, I was habving trouble linking two different invoices to the same order number - Is there a way to do this that you would suggest?

                  • 6. Re: Creating two sequential invoices from a single order/job
                    philmodjunk

                    Your existing relationship works in the opposite direction. A primary key field in Invoice_Details links to the foreing key field in Orders. They don't link at all by order number.

                     You can always add another oocurrence of Orders to your relationship graph that links like this:

                    Invoice_Details::OrderNumber = Orders::OrderNumber

                    and the above script, which should definitely have this step removed:

                    Set Field [Orders::order Number ; $OrderID]

                    could then have:

                    Set Field [Invoice_Details::OrderNumber ; $OrderID]

                    If you never have more than one order for any given invoice, I'd use OrderNumber as the link to Invoice_Details any way. If, for any reason, you might have two or more orders linked to the same invoice, then you are looking at yet another join table as you now have a many to many relationship.

                    • 7. Re: Creating two sequential invoices from a single order/job
                       Thank you Phil. I shall try that out tomorrow morning and let you know how I get on. Once done, I shall have another go at the payments that I am still stuck on! Thx again
                      • 8. Re: Creating two sequential invoices from a single order/job

                        Hi Phil

                        That works reallywell - thank you. I thought I hads to do somehting with a new T.O so at least I am thinking along the right tracks.I am having a small issue in that on the layout, Invoice, I want to display the relevent order number as I usew the same layoyut for the deposit as for the main invoices. At the moment, if i have the field, Invoicedetails::order number, then it only shows the order number for the deposit as opposed to showing the usual orders::order number. Have tryinbg to do a new field with an If calc but doesnt seem to work - any ideas for a work around...? And thank you again....

                        • 9. Re: Creating two sequential invoices from a single order/job
                          philmodjunk

                          If you use:

                          Invoice_Details::OrderNumber = Orders::OrderNumber

                          as the relationship between the two tables, this should not happen. Both invoices should link to the same order.

                          • 10. Re: Creating two sequential invoices from a single order/job

                            Hi Phil

                            Thanks 

                            I forgot to add those new lines to my other create invoice script..

                            Works now!