6 Replies Latest reply on Jan 8, 2016 1:22 AM by buddles1

    Quotation to Invoices relationship - Joined table needed?




      I am new to FileMaker and I could do with some help please :-)


      I am trying to write a database which involves quotations being sent and most (Hopefully) being turned into jobs and then invoices.


      I can't quite get my head around the relationship side of things!


      I am thinking:


      Table:     Quotation

                     QUOTEID pk

                     CUSTOMERID fk

                     SUPPLIERID fk    

                     PRODUCTSID fk


      Table:     Quotation Details

                     QUOTEDETAILID pk

                     QUOTEID fk

                     CUSTOMERID fk           


      Table:     Invoice

                     INVOICEID pk

                     CUSTOMERID fk

                     SUPPLIERID fk    

                     PRODUCTSID fk

      Table       Invoice Details

                     INVOICEDETAILID pk

                     INVOICEID fk

                     CUSTOMERID fk


      Obviously adding more fields to both the quotation and invoice tables.


      All tables with a one to many relationship.


      Layout governed by a status combo box: quotation/active job/invoice and buttons to become visible (run by script) dependent on which status is selected to turn the quotation into an invoice (different layout) etc. So the user would create the quote, customer accepts, quote now becomes an active job (which would print a scope of works without prices for the employees) job completed, invoice.


      I am thinking down the right lines?


      Many thanks for your help and time x