4 Replies Latest reply on Jan 20, 2011 11:54 AM by philmodjunk

    Perform Find  returns incorrect results when using a related field based on a global match field

    cocoa777

      Summary

      Perform Find  returns incorrect results when using a related field based on a global match field

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      OS X 10.6

      Description of the issue

      FileMaker Find function is returning wrong results when performing a find on a related field that is joined by a global match field.  I have a Table called EMPLOYEE that is related to another table called AWARDS as a "one to many". 

      I am defining the join relation in the Manager Database->Relationship tab by creating a relationship between the two table occurrences.  See attached image for more on the join.  The relationship is joining Employee's to their Award records and further filtering them down by the global g_chosenFiscalYear.

      On a layout that has EMPLOYEE as the context, if you add a portal to show the related Award records, the portal will show the relationship correctly.  Namely the awards defined by the relationship.  If you change the g_chosenFiscalYear global field to a different fiscal year the portal will display the correct records.

      The trouble is when you want to do a find on a field in one of the related Award records.  FileMaker is not respecting the relationship join but is instead doing a Find on all related records in the AWARD table.

      For example, lets say I have 3 employees who have an award named "MERIT" for fiscal year 2010 and 0 employees with a Merit award in 2009.  If my global variable is 2009, portals show no records for my 3 employees - portal is working good.  But if I enter Find mode and enter "MERIT" in the portal field for award name, FileMaker returns 3 records in the found set instead of zero.

      Keep in mind, that this is not portal specific.  The same erroneous results occur from a List Report Layout where my script is doing the Find on the related award name.

      As a test, if you change the global field to a regular field and do a Replace Field contents on it to say, "2009".  The find works perfectly.  Only when it is a global does it fail.

      Expected result

      I would expect that FileMaker's find function would only return results from related records that are part of my table occurrence relation definition.

      Actual result

      FileMaker Find is returning any match in my child relation and disregarding the global field match field in the relationship

      Workaround

      none.

      Find_Error.png