4 Replies Latest reply on Dec 9, 2010 10:23 AM by cocoa777

    Perform Find on a Related JOIN TABLE?



      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



        • 1. Re: Perform Find on a Related JOIN TABLE?

          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.

          • 2. Re: Perform Find on a Related JOIN TABLE?

            Hi Phil,

              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

            • 3. Re: Perform Find on a Related JOIN TABLE?

              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.

              • 4. Re: Perform Find on a Related JOIN TABLE?

                Thanks Phil, i will write it up.