I am somewhat new to FileMaker and have done some tutorials on Lynda.com so have a general understanding of how to navigate and create databases in FileMaker. However, I'm trying to wrap my head around a database I'm creating to manage my clients and the various invoices.
My database involves:
1. Client List
My database is currently set up with a Client table, a Quotes table, and an Invoice table. I originally set up my database with Client and Invoice tables only but when I created my layouts, I realized the quotes and the final invoice amounts were often dissimilar (due to changes in scope of projects). Also, upon setting up my layouts, I realized every quote I created could then be viewed as an invoice (and vice versa), which doesn't make any sense. I mean, a quote is a quote and an invoice is an invoice.
Now, I'm encountering the same issues as I now have to create different invoicing options (deposit invoice, final invoice, etc.). If I keep all the values in one Invoice table but use multiple layouts, I can view my deposit invoice as a final invoice, etc., which I don't want to be able to do. So the solution is then to break out my Invoice table into two, one for deposits and another for final.
So the problem is this. It seems I would need to have unique tables for all of the different types of invoices (deposit, final, ones where I bill per day, ones where I bill per item, etc.). Well that doesn't make a lot of sense! Additionally, each deposit invoice has an associated final invoice. Most of the values should be linked between the two, with the only exception being an occasional difference in the deposit amount billed versus the deposit amount paid. This means there is typically a one-to-one relationship between the Deposit invoice and the Final Invoice, suggesting that all the values should exist in a single table. But by doing so, I end up with the problem of being able to view a Final invoice as a deposit invoice (and vice versa).
I think the correct approach would be to keep the values in a deposit invoice in a table separate from the values of the final invoice. If so, how do I link the values so deposit values can easily be "migrated" to the final invoice?\
What am I missing here?
Please keep in mind what I know about FileMaker has been through Lynda.com tutorials and I've been teaching myself. There's so much I don't know so please take it easy on me!
Thanks for your help!