6 Replies Latest reply on Aug 6, 2012 11:34 AM by philmodjunk

    Relationship diagram between customers, contracts and invoices

    sblunden

      Title

      Relationship diagram between customers, contracts and invoices

      Post

      OK I tried to search for a similar situation but did not find and answer so I will try to explain my situation as best as possible.

      I have 4 main tables: Customers, Contracts, Invoices and Products.

      I am having a problem with the relationship between customers, contacts, invoices. 

      Customer signs a contract and pays for it on an invoice. If customer renews we would just make a new invoice with an exsisting contract so there can be multiple invoices for 1 contract. Of course customers can have more than 1 invoice. It is also possible to have an invoice without a contract, IE a customer could simply purchase an product with out a contract.  

      My thought on workflow.  Enter customer contact info. Tap (this is for an iPad solution) a button to add a new contract from the customer record, enter contract data (with ability to printer contract) then tap a button to create a new invoice for that contract so the invoice shows the payment is for a specific contract. So it needs to bring the customer and agreement IDs to the invoice.  Or i can skip the contract and go straight to an invoice from the customer.  

      So I figured I would need some join tables but its just not clicking for me.

       

      here is a basic diagram (but I feel like I am missing a relationship)

      Customer ----< Customer/Contracts Join  >----- Contracts -----< Invoice ----< Line Item >---- Products

       

      My analysis:

      Customers may have multiple contracts

      Customer may have multiple invoices

      Contracts can have multiple invoices

      Invoices may have 1 contract or no contract 

      Invoices may have multiple line items.

       

       

      Options: customers will typically have a contract so if they make a purchase of a product without a contract I could simply make it and invoice for the contract they have on record.  While no contract would be required for the purchase I could tie it a contract on file for the customer.

      I could manually (or from a drop down value list) enter an agreement number on invoices instead if it being automatic.  

       

      I am relatively new to FMP so I am hoping a more seasoned user can help.  I have filemaker 12 on Mac.  Final product will be using Filemaker Go on iPad.  

       

      Thank you in advance!

        • 1. Re: Relationship diagram between customers, contracts and invoices
          beckett85_1

          Have come across a similar situation before, one of my clients invoiced for each job, then decided some invoices weren't going to be for jobs but they would be direct consulting for the client

          My way around it

          1. Have 2 table occurences -
          - Invoices_Contracts
          - Invoices_Client

          This way there will be specific invoices viewable under the contracts & Specific invoices viewable under the clients

          How to Determine between the 2
          1. Use the same foreign key in the Invoices table to link to either a client key or a contract key BUT BE VERY CAREFUL, this will only work if you use keys that are so unique that you will never have a client and contract key that are the same

          2. Give the Invoice a type (Either Contract or Client)
          - Create a global field in the client table that will ALWAYS be set to this invoice type, and a global field in the contract table that will ALWAYS be of this invoice type
          - This way if you use BOTH the key field and the type field in the relationship, they will always match

          3. Have 2 foriegn key fields in the Invoice table, a Client one and a Contract one, (I would use this one)
          Either set the client Foreign Key field on the contract foreign key field on creation, personally this is the one i would go with, because this way ALL invoices can be summed summed and viewed through one portal from the client, and contract invoices can be viewed through the contracts FURTHERMORE this will let you easily link an invoice to a contract if you add it in later :)

           

          AND You can keep the invoices to one table

          if any of the above doesn't make sense i'm happy to explain more :)

          • 2. Re: Relationship diagram between customers, contracts and invoices
            sblunden

            thanks I will try it.   I do have seperate keys customers are auto serialized start with a C then numbers.  Contracts are autoserialzed starting with AGMT and numbers.  And invoices auto serialzed with all numbers so we will not have overlap!

            I will try your suggestions this week (I am working on this at night so I am limited in my time)  I have built the database becuase I thought I had it all worked out but then was running into problems. I think I am 75% there already.

            thank you for the quick response.

            • 3. Re: Relationship diagram between customers, contracts and invoices
              sblunden

              OK so had to put this on the back burner but am back.  Option 3 seems like the best option but I am missing something.  I don't fully understand how to make customers, contracts (agreements) and invoices play nice.  Attached is an example of the tables (without all the other fields).  But when I try to link them i get weird results. I am missing something.  So the relationships do not have any linking in the picture. not sure how the relationships should be made, and if in need another match field or another table instance.  any help is appreciated.

               

              • 4. Re: Relationship diagram between customers, contracts and invoices
                philmodjunk

                What you show in your screen shot are table occurrences. Each refers to tables defined on your tables tab. But you are not limited to only one occurrence for a given table. Click one of these occurrences, then click the duplicate button (two green plus signs). This will create a second occurrence of the same table. If you hover the mouse of the upper left corner or double click one, you'll see that both refer to the same table.

                THis then permits you to use the same table in more than one relationship.

                • 5. Re: Relationship diagram between customers, contracts and invoices
                  sblunden

                  so would i use the Customer ID to have a relationship from Customers to Agreements? Then make a seperate instance of the Customers Table to link the customer ID to invoices?  

                  I understand the conspect but it's not translating to the real world...

                  • 6. Re: Relationship diagram between customers, contracts and invoices
                    philmodjunk

                    Starting with your original setup:

                    Customer ----< Customer/Contracts Join  >----- Contracts -----< Invoice ----< Line Item >---- Products

                    My analysis:

                    Customers may have multiple contracts

                    Customer may have multiple invoices

                    Contracts can have multiple invoices

                    Invoices may have 1 contract or no contract 

                    Invoices may have multiple line items.

                    Customer/Contracts Join

                    Does not appear to be needed. Join tables are only needed in many to many relationships and this appears to be one to many. You only need a join table between customer and Contracts if you need to list more than one customer on the same contract. (You've already indicated that a customer can have more than one contract.)

                    Customer ----< Contracts -----< Invoice ----< Line Item >---- Products
                                                                           v
                                                                            |
                                                                       CustomerInvoice

                    Contracts::_pk_ContractsID = Invoice::_fk_ContractsID AND
                    contracts::_fk_CustomerID = Invoice::_fk_CustomerID

                    CustomerInvoice = Invoice::_fk_CustomerID

                    Might be a workable approach. Note that CustomerInvoice is a second occurrence of Customer and fields that refer to CustomerInvoice will display customer data for both contract and non-contract customers. Fields that refer to Customer, from an Invoices layout would only display data from a contracted customer. The dual field relationship is not strictly be necessary, but is one way to insure that the correct Customer ID value from contracts is entered. An alternative is to use just the contractsID, but set up the customer ID field to auto-enter the customer ID from contracts when there is a related contracts record.