4 Replies Latest reply on Oct 29, 2010 8:18 PM by JeremyWenrich

    Understanding how to resolve many-to-many conflict.



      Understanding how to resolve many-to-many conflict.


      I've watched the 9+ hours of Lynda.com Essential Training for FileMaker Pro 11 and I feel that I grasped the tutorials well. Unfortunately I'm failing to fully understand how to resolve many-to-many relationships. The standard client, invoice and product data will need to be saved in my database. I've also created a line item table and connected the primary and foreign key fields between all the tables. I find this information repeated over and over on the internet and in the forums. I get how client data can be related to many invoices. What I do not get is how the relationships work between invoices, products and line items. All instructions I've found stop right before making sense out of this. Am I making too much of it? Does it work without me doing anything else?

      I'd really like to have a solid understanding of this before proceeding lest I build this database incorrectly.

      To give a little more details about the information I need to track, it breaks down into Clients, House Calls, Service Orders, Invoices, Products and Tasks. A client will potentially be associated with multiple invoices, service orders and house calls. I want to be able to add multiple products to House Call and Service Order records so that I can build quote reports for these records (i.e. I can quote a client a labor and product price total without building an invoice. Maybe this is inefficient though.). I'm a little unsure what to do with Tasks yet. I need to be able to reference any other major table, but copy and pasting record IDs may be fine. Once I understand the core issue I believe I can make a more informed decision regarding Tasks.




        • 1. Re: Understanding how to resolve many-to-many conflict.

          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.

          • 2. Re: Understanding how to resolve many-to-many conflict.

            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.

            • 3. Re: Understanding how to resolve many-to-many conflict.

              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.


              • 4. Re: Understanding how to resolve many-to-many conflict.


                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.