Let's look at your three tables in question this way:
Invoices----<LineItems>----Products (----< means one to many )
Any given Invoice can list any number of products and any given Product can be listed on many different Invoices, so this is a classic many to many relationship. LineItems stands in the gap between Invoices and Products to build links that support the many to many relationship between the Products and Invoices.
The details of these relationships should look something like this:
Invoices::InvoiceID = LineItems::InvoiceID
Products::ProductID = LineItems::ProductID
When you start to enter data in your lineitems portal, FileMaker automatically enters the current Invoice's InvoiceID number to link the LineItem record to the current invoice. When you select or enter a ProductID, you then link that LineItem Record to a specific Product so that pricing and description data can be looked up and/or displayed in the portal. By finding all Line Item records with a given Invoice ID, you get a list of all Products purchased on a given invoice. By finding all Line Item records with a given Product ID, you get a list of all Invoices where that Item was purchased. You could, in fact, place a portal to LineItems on your Products Table to see a list of all invoices where the product was purchased just like the portal on your Invoices layout lists all Products purchased on a given invoice.
With regards to your House Calls and Service Orders tables. You can indeed define separate tables for this and define additional fields in Line Items so that you can relate a LineItem record to a HouseCall record or a ServiceOrder record. Given that such records may be very similar to your Invoice records, however, you might also choose to treat house calls and service orders simply as special kinds of Invoices and just add a single new field to Invoices where you specify whether the record is an Invoice, House Call or Service Order. I've seen successful systems use either approach so the choice is up to you.
For my LineItems table, do I need to define any other fields other than the foreign keys for the product and invoice IDs? Once the primary and foreign keys are linked to LineItems then an Invoice can access the fields in a Product? For example, if no price field is created in LineItems, will a Product price be visible in an Invoice?
I will tinker and try to answer these questions for myself as best I can. My problem is with visualizing how these relationships work. Seeing them in action my help.
I appreciate your response.
The two key fields are the minimum needed to make a many to many relationship work. You often need to identify additional fields in the join table to record information specific to that particular linkage between the two "many" tables.
Using Price as an example, you could indeed add the price field from Products to your LineItems portal and when you select a productID, the matching price would appear. However, this is a very bad idea for price information. Let's say you sell a red widget at a unit price of $0.50 to a customer on Monday. On Tuesday, you are informed by your supplier that the cost of red widgets is going up, so you change your price in Products to be $0.75. For a new invoice, all seems fine as any new sales of red widgets are priced at $0.75, but if you check that invoice for Monday, it now says that you sold that widget for $0.75! You need to record the price of the item at the time it was sold. The fix for this, is to add a unit price field to your line items table with a looked up value option that copies the current price from Products into the unit price field in LineItems. Now, if you change the price, new invoices will show the new price and old invoices will not show any change.
Here's a simple invoicing demo file created by Comment, another forum participant, you can download and examine to see how all of this works.
The invoicing demo file is extremely helpful! I'm able to experiment with it and view how modifications I make in one place do or do not impact another with how tables are related and fields are setup. Perfect!
Your insight into pricing is invaluable. I feel confident in setting up LineItems now. I'm sure I've overlooked some other things, but I'll heavily test my database before putting it to use. Perhaps I'll post here again for critique when I'm in the final stages.