Perform Find on a Related JOIN TABLE?
I was wondering if you can do a Find against a field from a related join table occurrence. I have the following relationship:
EMPLOYEE: pk_empID, g_fiscalYear
EMPLOYEE_OFFICE_SYMBOL_ASSIGNMENT: fk_empID , fk_OfficeSymbolID, startDate, endDate, fiscalYear
OFFICE_SYMBOL: pk_officeSymbolID, officeSymbolName
My table occurrences are relating an Employee to one EOSAssignment by empID and fiscalYear. So the to many is being filtered down to one EOS and Office Symbol. In my layout, which is in the context of EMPLOYEE, show a portal of EOSA and shows the one record perfectly. So I know my filtered relation and join is working correctly.
However, if I try to do a Find based on that related field, officeSymbolName, the Found Set is containing any record in the join table (EOSA) that fits the criteria not just my filtered record based on the join match fields. I have had no problems with FileMaker searching related fields from other relationship types. is this a known thing with Join Tables?
I can get what I need by putting a calculation field in Employee that gets the office symbol code from the relationship but I am curious if this is a bug or I am doing something wrong.
thanks for any info you have