I am redesigning a company database in Filemaker 7 to utilise the new relational features, and I have the following question:
I have a 'Clients' table which is linked to an 'AddressBook' table and a 'ClientCorrespondence' table by ClientID->ClientFK. The client table stores individual and company details (first name, last name, company name etc). The address book table stores address details and the type of address (street, city, state etc and home address, work address etc...). The correspondence table simply stores the contents of letters and the dates they were created and modified, along with the ClientFK.
In one of the layouts for the ClientCorrespondence table I use merge fields to extract the related name and address information from the client and address book tables. However, I only want to display "company name" from the clients table IF the related address is a "work address". Is there a way to insert conditional logic into merge fields? Alternatively, might there be a way to achieve this via a script? I thought about moving the "company name" to the address table, but I am reluctant to do this because it properly belongs to the client table.
I believe the answer to this is to set up the relationship to your AddressBook table to match on type of address as well as ClientID, based on a gflobal or constant field in the clients table which you've stored the work address type indicator
That way, the match will only be valid if the related address is a work address and your display of related address data will be conditional.