Q: I have a database that details the service history for our customers. They can have a variety of services performed, but they all reside in the service history table. Different fields are used depending on the service, so I have different fields for the screening date, evaluation date, referral date, and so on. Each of these date fields are filled out in multiple related records, one for evaluation, one for referral, etc. I have a search layout that uses all these fields as related fields, and it works fine if I only search one date. But if I search for a screening date and an evaluation date, I get no records found when I should find some. Do you know what's going on here, and how I can fix it?
A: Your problem stems from FileMaker Pro thinking you're searching for a record where both dates are filled in on the same portal row, when in fact those dates are filled in on different portal rows. Having the fields on your layout as related fields is the same as having them in a one-row portal. In your question you mention three related record types: screening, evaluation, and referral. As long as you have a relatively small number of related record types, a simple solution is to create duplicate relationships. In your relationships graph you have a relationship from Client to Service History. Because your search layout is based on the Client table occurrence, you'll have to duplicate the Service History table occurrence twice. Add relationships from Client to these new table occurrences so you have as many relationships as you have Service History record types.
On your search layout, re-specify your date fields (and any other fields unique to a specific service history type) so the screening date, evaluation date, and referral date all use different relationships. Now when you perform a search, you'll be able to successfully find clients that have search criteria in multiple related service history records.