I'm having an impossible time getting my head around the theory of relationships. Is there a thread on this forum, or an article, that speaks to the question of how to understand which fields to relate in order to get the results you want?

Abbe

There are some good discussions in a couple of books, notably Special Edition Using FileMaker Pro 5 by Rich Columbre and Advanced FileMaker Pro 5.5 Techniques by Chris Moyer and Bob Bowers.

Here's a quick introduction. Individual database files (tables in the language of other database management systems) track entities of one type, such as contacts, invoices, companies and products. Each file needs a field that uniquely identifies each record. Fields that contain data in them won't work because, for instance, more than one contact could have the same first name. So, a special field, called the key field, is manually used, called perhaps ContactID, InvoiceID, etc. This field is setup so that it's value is always unique for each record. The easiest way to do this is to have it auto-enter a serial number.

With my example files (contacts, invoices, companies and products) each entity has a relationship with others. For instance, a contact belongs to a company, a product belongs to an invoice, an invoice belongs to a company.

Some of these relationships are one-to-many, meaning that one local entity is related to many foreign entities. One company can have many contacts, but one contact belongs to only one company. These relationships are the most straightforward. The contacts file would have a field called CompanyID and there would be a relationship in both contacts and companies, relating to the other file, based on the identically named field CompanyID.

Other relationships are many-to-many. One invoice can have many products and one product can be on many invoices. Many-to-many relationships can't be built directly in FileMaker (or any other database management system that I know of). A join file is used between them to create two one-to-many relationships. I'll call this file the line items file. One invoice can have many line items but one line item belongs to only one invoice. One product can belong to many line items, but a line item belongs to only one product. Once you've broken up a many-to-many relationship in this way, you create the two one-to-many relationships in the same manner as above.

That's pretty much the basics. There are advanced techniques that you can use with relationships, but these are what I call utility relationships and aren't actually part of the relational structure of the system.

Hope this helps. Check out either of the two books I mentioned for more info.

Chuck