How to Filter Records for a Report

[James Pearson]

I have a report layout which is based on a table QuoteLines. In the header I want to print the primary contact (and other details about the contact) for the 'job', but each job can have multiple contacts (only one has the field 'Primary' set to 'Yes').

Basic structure:

Quote Lines -> Jobs -> JobContacts -> Contacts

In JobContacts (the join table) I have the 'primary contact' field.

As previously mentioned the layout is based on 'Quote Lines'. I thought I could simply change to the JobContacts layout, filter out all but the one record that is primary and then head back to the report layout but this doesn't seem to work (the first related contact is still displayed, not the primary contact). Stepping through the script I can see that the filter works, but it seems to not have an effect on the layout that needs it. I've tried performing the filter from the 'Quote Lines' layout but that doesn't work either.

I'm sure this is simple and I'm missing something obvious in my knowledge of Filemaker! Please help!

[bcooney]

You need a relationship from Jobs ->JobContacts that includes not just the JobID, but the fact that the contact is the primary contact.

I usually, for this type of thing, store the primary ContactID in the Job record, rather than a Yes/No value in a field in JobContacts.

So, then you can tunnel from Quote thru Jobs to Contacts using a new relationship Jobs>Contacts~prim where Jobs::ContactID_prim = Contacts::ID

Edited October 1, 2010 by Guest