Arrgh. following multiple relationships.

[spankalee]

Another problem that I constantly run into (as I try to get used to FileMaker vs SQL) is how to display data that's seperated from a record by two or more relationships. Here's a typical scenario.

Addresses DB

Customer DB

Invoice DB

Customers can have a main, shipping and billing address (all possibly the same), so there's three address fields with address id's. On a new invoice I want to be able to type in a customer code and have the main and billing addresses fill in.

So how's this normally done? Do I need a script? Do I save the address IDs into fields in the Invoice DB and have Invoice->Address relationships based on those? Do I need a scipt for that or can I just base the Invoice's Address ID off a related value from the Customer DB?

[Kurt Knippel]

There are never any 3 step relationships, even in SQL (at least I have never encountered or used any, nor do I even know how to accomplish one). You just relate from DB1 to DB3, via the ID of the record in DB2.

So in this case you have some way of identifying the Main and Billing address in the AddressDB. How I do it is with a simple calc: cAddressID_type = CASE ( Type = "Main", CustomerID & "-M", Type = "Billing", CustomerID & "-B", "" )

Then from the InvoiceDB, you need 2 calcs and 2 relationships to derive the addresses. The calcs would be: cAddressID_billing = CustomerID & "-B" and cAddressID_main = CustomerID & "-M".

Then setup a relationship from each of these calcs to the cAddressID_type field in the AddressDB and you have the addresses linked to the invoice.