Hi,
I'm an exact problem which was posted here: Perform Find returns incorrect results when using a related field based on a global match field . I believe the bug hasn't been fixed yet in the latest release. To provide more information about the bug, I will follow the template on that thread using my description.
Summary
Perform Find returns incorrect results when matching related tables using a global field, or when filtering portals using a global field.
Product
FileMaker Pro Advanced
Version
17.0.1.143
Operating system version
Windows 10 Enterprise | 10.0.15063 Build 15063
Description of the issue
The bug is exactly the same as described in the reference thread. I can reproduce it by doing the followings:
1. Create 2 tables: Students and Participation, and their respective occurrences. The Students table has StudentID (PK), g_TermID (global field used for matching); The Participation table has TermID, StudentID (FK), Attended (regular text field). I join them like in this picture:
2. I have these records in each table:
| Students::StudentID | Students::g_TermID |
|---|---|
| 1 | 100 |
| 2 | 100 |
| Participation::StudentID | Participation::TermID | Participation::Attended |
|---|---|---|
| 1 | 100 | N |
| 1 | 200 | N |
| 2 | 100 | N |
| 2 | 200 | Y |
3. Create a layout with Form view based on Students. On the layout create a portal showing records from the related table Participation. Now on the portal, it should show only 1 record with the term 100 for each student as expected. The portal displays the record correctly:
4. But as soon as I do a Perform Find.... If I put in Attended a "Y" and then find, I should expect the result to be no records found, but FileMaker still finds 1 record instead (the "Y" from StudentID "2", TermID "200" is found even though it was filtered out by the join setup using g_TermID). It ignores the join using the global field g_TermID, the found set is now incorrect:
5. Same thing happens if I don't do the joining Students::g_TermID = Participation::TermID and use Filter portal records Participation::TermID = Students::g_TermID instead. The portal displays correctly but Perform Find returns incorrect found set.
6. Same bug whether the database is local or hosted on FileMaker Server. Tested on different computers.
Workaround
The only workarounds I can find are:
1. Changing g_TermID to a non-global field, then replace field in all records to a term I want, Perform Find then behaves as expected.
2. When doing the find, also put in "100" in Participation::TermID along with in "Y" in Participation::Attended, then no record is found as expected.
What I need to achieve
Having the Student layout with the children table portals that only display the term I selected when there are multiple terms. What's the best way to do this other than the workarounds I found? Or is it possible to fix the bug somehow?
I also attached the FileMaker test file I used for quick reference if needed.
Thank you in advance for your help!
Maybe this isn't an actual bug, more of an 'expected behaviour with undesired result'???
I can see why you might think that but the whole point of a filtered portal is that all the records are downloaded to the client some of them are then just filtered out. SO, when you search the portal you are searching through ALL the related records NOT just ones that you want to not display because you want to filter them out.