Doing finds with join tables will produce the same results as putting search criteria in any other field from a related table. (Under the hood, they're exactly the same as far as the mechanics of your search goes.)
You mentioned that this is a "filtered" portal. Does that mean you are using FileMaker 11 with a filtered portal? (Filtering can be done at the relationship level with earlier versions.) I'd have to run a test or two to be sure, but I think that the portal filtering may be what's appearing to produce different results.
In you find criteria, are you just specifying the symbol name or are you also specifying the fiscal year?
What I think is happening is that you are entering find mode and just specifying the symbol name. FileMaker then finds all employee records with a related join table record that has that symbol name. The filter then kicks in and hides the related record for those employees where the related record is from a different fiscal year.
The solution that comes to mind is to also specify the fiscal year when creating your find request.
thanks for your reply. see the original post for a screen shot.
I guess this is why I am a bit confused with this scenario. First I should mention that the portal is NOT filtered, it is just displaying the related records from EOSA which are filtered by the relationship. I really only mentioned the portal because it "proves" the relationship defined in the table occurrence's relationship is correct. That filtered relationship is filtered by the join from Employee's g_fiscalYear and pk_empID to EOSA.
The exact set up for this situation is that my layout is in the context of EMPLOYEE and it is a report list layout. So in my body I want to show EMPLOYEE records and I am basing my Find on the related OFFICE SYMBOL for a fiscal year that they are choosing, which I am setting as a global in the EMPLOYEE table. So my assumption was that in Find mode when I specify the officeSymbolCode that FileMaker only returns results based on my filtered Table occurrence. I know this works for other table relations like "to one" and "to many".
For instance, I have an ACCOUNT and layout that displays portals of different TRANSACTION records that are filtered by transaction types- in the data model table occurrences, not filtered portals. I can enter Find Mode and enter any portal field as criteria and it will find any ACCOUNT with that related data. I am not sure why a JOIN table would not behave similarly. I know it is difficult for you to see exactly what I am trying to do with the scenario that is failing but at least I have a workaround. I was just VERY curious why it didn't behave the same as those other scenarios. Thanks again
I can replicate that behavior on a test file. I think it's the global field that's the issue not the fact that there's a join table involved. When I change the global field in my test file to local storage and use Replace Field Contents to set the "year" value for all records in my employee table, the find then works as expected.
You might want to write this one up in "Report an Issue" as I don't think FileMaker should work this way.
Thanks Phil, i will write it up.