4 Replies Latest reply on Jan 29, 2015 8:26 PM by realgrouchy

    Searching a field in a filtered portal returns parent records with filtered-out items

    realgrouchy

      Title

      Searching a field in a filtered portal returns parent records with filtered-out items

      Post

      In my parent layout/table, I have a global field with values "Current" and "Archived", with a portal showing entries from a table. Each entry in the child table has a field that can be set to either "Current" or "Archived".

       

      The relationship between the two tables has two entries, matching parentID, and Parent::globalCurrentorArchived = Child::CurrentorArchived.

       

      This successfully filters out the results shown in the portal when I toggle the Current/Archived switch in the parent layout. However, if I perform a search in the parent layout for a field in the portal, it returns all results, not just those filtered by Parent::globalCurrentorArchived = Child::CurrentorArchived.

       

      That is, it returns all records in the Parent table that have an entry in the child table. Those child table entries don't show up in the portal, but I only want parent results that have Current entries in the child table.

       

      I don't have a field in the portal for the child entry's Current/Archived value, though I suppose if I did one could manually search it, but that becomes time consuming (especially since I have a few portals/child tables with this functionality).

       

      If I put the current/archived field in the portal, is there a way to get that field to automatically populate with the global field value when I perform a search (or otherwise achieve the desired functionality)?

      Thanks,

       

      - RG>

        • 1. Re: Searching a field in a filtered portal returns parent records with filtered-out items
          philmodjunk

          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.

          • 2. Re: Searching a field in a filtered portal returns parent records with filtered-out items
            realgrouchy

            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?

            - RG>

            • 3. Re: Searching a field in a filtered portal returns parent records with filtered-out items
              philmodjunk

              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.

              • 4. Re: Searching a field in a filtered portal returns parent records with filtered-out items
                realgrouchy

                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...