I'm not sure that I understand the problem.
If this is your relationship in Manage | Database | Relationships:
Parent::ParentID = Child::ParentID AND
Parent::CurrentOrArchived = Child::CurrentOrArchived
Then performing a find on the Parent layout where you specify criteria in a child field should find all Parent records that have at least one related child record matching the criteria specified for the child record.
If you put "Current" into Parent::CurrentOrArchived and this is truly a field with global storage, then your find should only find Parent records with at least one Child record that is both "Current" AND meets any other criteria specified in Child fields. That doesn't seem to match what you are describing, but the reason may lie in several crucial details:
It only takes one related child record that matches the specified criteria for the parent record to be found and included in the found set. The fact that other child records exist for the current record that do not match the current value of CurrentOrArchived or that do not match any other specified criteria for child records will not affect that result. It only takes one related matching record. Once the find has been performed and the found set pulled up, however, all related records both those that match the find criteria and those that do not will appear in the portal.
If you are using a portal filter expression instead of a relationship, I think you'll find that the portal filter does not affect the found set produced, only the match field values specified for the relationship will affect which records in the parent table are found.
That's the weird thing; I'm not using a filtered portal. Maybe it's a bug in FMP.
- The global field in the parent table is configured as a global field in the Storage tab
- The ParentID numbers are unique in the Parent table
- The relationship is set as you describe.
Yet still when I am in the parent layout, with the Global field set to "Current", and I perform a search where I type a value in a Child:Fieldname field in the Child portal, it returns all Parent entries that have at least one Child entry with that value, even if there are no Child entries that are "Current". (The "Archived" entries don't appear, unless I toggle the global field.)
I notice that I can't select the global field in the Parent table during a Find. Is that supposed to be the case, and is that perhaps related to the issue I'm having?
You cannot enter find criteria into global fields while in find mode. That would make no sense to do so as the value in a global field is not specific to any one record so specifying criteria in it would not affect your search results.
But I ran a quick test and am surprised by my results. As long as the "filter" field in the parent is global, I get the same results that you report. If I change the field's storage to nonglobal and use replace field contents to give this field the same value in all records, I get the results you and I both expected.
Seems like a bug, but if it is, it's one that's been around at least since version 11 as I also tested this in that version.
What you can do, is perform this search via a script that specifies this value in the related table or perform the search on a layout based on the child table and then use Go to Related Records with the "match found set" option to pull up the parent records on the parent layout.
Okay, good, so I'm not crazy then. Thanks for testing and confirming.
As I continue to wrap my head around this database I'm building (combination of complicated datasets and complicated things I want to do with them), I think this issue might be moot based on how I want to handle the issues, at least for now...