AnsweredAssumed Answered

Relationship diagram between customers, contracts and invoices

Question asked by sblunden on Jun 12, 2012
Latest reply on Aug 6, 2012 by philmodjunk


Relationship diagram between customers, contracts and invoices


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!