7 Replies Latest reply on Dec 2, 2016 5:10 PM by philmodjunk

    How to transfer records between tables

    rudydimaggio

      Hi,

       

      I have a table: ORDERS related to ORDER_LINES which contains the details of the order.

       

      And a table INVOICE related to INVOICE_LINES which contains the details of the invoice.

       

      Now, when an order becomes an Invoice, I need to transfer all or part of the lines from ORDER_LINES to INVOICE_LINES

       

      But I am not able to find an easy way to do that, any suggestion?

       

      TIA

       

       

       

       

      Filemaker 15 advanced on MacBookPro

        • 1. Re: How to transfer records between tables
          Johan Hedman

          Best thing is to relate data into your tables.

           

          Order to Invoice. Have a OrderID in Invoice and then have all fields that you want to get data from Order to Invoice to relate calculated data into Invoice. You have a relationship based on Order ID between your tables and set data in Invoice table with Calculated value link in image below

           

          Same thing for Order_Lines to Invoice_lines

           

          Skärmavbild 2016-12-02 kl. 08.52.38.png

          1 of 1 people found this helpful
          • 2. Re: How to transfer records between tables
            GuyStevens

            I think a better system would be to never transfer data between tables.

            The way I always set this up is to have an Invoices and an InvoiceDetails table.

            Then in the Invoices table you have a field called "Type" And that is automatically set to "Order"

            You could also set this to "Invoice" when that order needs to become an actual invoice.

            The structure of these two things is Identical. Maybe the only difference is a slightly different looking Print Layout. But that can easily be done depending on the type.

            If it's an Order use one layout to print. If it's an invoice use another layout to print.

            I think this is the far superior system.

            Greetings Guy

            • 3. Re: How to transfer records between tables
              TekGlobe

              Hi Guy,

               

              Based on your statement, would you do the same for estimates to invoices.

               

              Set Field Type from Estimate to Invoice.

               

              Or, is it better to keep estimates in a separate table for different reasons? Just curious as to what you think!

               

              Thanks Andrew

              • 4. Re: How to transfer records between tables
                Johan Hedman

                For me Invoice is a binding material that noone is supposed to do any changes in. Therefore I like to create my data inside the Invoice and keep it there. Not in Order where I create/change things before I invoice.

                I

                • 5. Re: How to transfer records between tables
                  GuyStevens

                  Hi Andrew,

                  I will always create all these kinds of documents in the same table. That way if you want to make a change, like add a field, you only need to do that once, in one table.

                  You can have all kinds of types.

                  You could have:

                  - a Quote / Price estimate

                  - an Order

                  - An Invoice

                  - A Credit Note

                  - ...

                  The only thing you change is the value in the Type field.

                  Now there are a few things to keep in mind.

                  Like a Quote doesn't have an Invoice number. That only gets generated or assigned when the type becomes either Invoice or Credit Note.

                   

                  But all of these things are very easily managed with tiny little scripts or script triggers.

                   

                  Then if you want to get an overview of your Quotes, Estimates, Invoices, etc.

                  You can simply make a list view that has a subsummary part by Type so that all the types are grouped together.

                  If you only want to see all documents of one type you simply perform a find on the type field. This can be done by creating little buttons on top of your list view.

                   

                  Greetings Guy

                  1 of 1 people found this helpful
                  • 6. Re: How to transfer records between tables
                    GuyStevens

                    You could use the Filemaker Security settings to make it so that no changes can be made to the fields in Invoices and InvoiceDetails when the type is set to "Invoice".

                     

                    Greetings Guy

                    2 of 2 people found this helpful
                    • 7. Re: How to transfer records between tables
                      philmodjunk

                      I agree with GuyStevens, but

                       

                      Sometimes business practices require that an estimate be kept separate from an invoice because the details of what is actually ordered may differ from what was created on the estimate and the business operators may want to be able to compare estimates with the actual invoices produced from them.

                       

                      But this does not require two tables. You can create an order from an estimate by duplicating the Estimate record and it's set of line item records followed by changing the type of the new record from "Estimate" to "invoice". The users can then edit the new Invoice data while finalizing the sale.

                      2 of 2 people found this helpful