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
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!