6 Replies Latest reply on Jun 20, 2014 10:39 AM by flybynight

    Filter Portal Records Based on Found Set (redux)


      I originally posted this question in https://fmdev.filemaker.com/message/141848#141848.


      It *seemed* to be answered by erolst, but it is not working dependably for some reason.


      My solution is based on the Contacts starter solution. I have a Contacts table which contains a SUMMARY field called "Found IDs", which is a list of CONTACT ID MATCHING FIELD, the auto-indexing primary key.


      The Comm table contains a "Comm ID" auto-indexing primary key, and also the "CONTACT ID MATCHING FIELD" foreign key. The "Comm Type" field is either 1 or 2, indicating the record is either a phone number record or an email address record, respectively.


      My portal wants to have a list of email addresses, but only those which are relevant to the current found set in the Contacts table. So if I have a list of ALL contacts, I should see those email addresses in the portal, but if I've found a list of only the INACTIVE contacts, I should see only their email addresses in the portal.


      To accomplish this, I based my portal on the TO called "All Emails", which is an occurrance of the Comm table with a cartesian join to the Contacts table, on the "CONTACT ID MATCHING FIELD" column. The portal filter (suggested by erolst) is as follows:


      CommType = 2 AND not IsEmpty ( FilterValues ( GetSummary ( Contacts::FoundIDs ; Contacts::FoundIDs ) ; Comm::ContactID ) )


      The only difference is that I replaced the GetSummary formula with just Contacts::FoundIDs. This seems to work itentically, and the documentation says the GetSummary function only works when the found set is sorted by that field (which it is not in my case).


      My solution "sort of" works either way, as I tried both versions of the above filter formula. I can filter my Contacts records by: All contacts, Inactive, Active, Board Members, Owe Money, and others. In all cases, when I filter the Contacts data, the FoundIDs field then properly contains the correct list of "CONTACT ID MATCHING FIELD" values.


      All of the different Contacts found sets I get cause the portal to fill with the proper email addresses, except for when I filter the Contacts for Board Members. In that case, the portal contains all email addresses, not just the board member email addresses. This is obvious when I scroll the portal, as there are 200+ members, and only 16 who are board members. The portal should show 16 email addresses, but it shows 200+ instead. But when I filter for the 5 Inactive contacts, the portal properly shows 5 email addresses.


      So if the summary FoundIDs list field contains the proper 16 IDs, why isn't it filtering the portal properly? Any ideas?


      Thanks for the patience it must have taken to read this post.

        • 1. Re: Filter Portal Records Based on Found Set (redux)

          First thing I would do is check the 16 board member records to make sure they have the correct flag to allow the filter to work—or maybe the other way around: make sure the other 184+ don't have the board member flag.

          • 2. Re: Filter Portal Records Based on Found Set (redux)

            Thanks for the quick response.


            I'm not sure I follow you. Let's take the portal problem out of the mix for a minute. I have several different ways to filter the data. In all cases, I choose the proper type of data from a drop-down powered by a value list. I run a script, with a bunch of IF blocks, each one sensitive to a different filter choice. Each block properly filters the data and returns a found set. Visually I can see that they all return the proper found set. Also, the list summary field then contains the proper IDs, the list of which has the new proper number of values.


            Once I am that far, the portal filter should work, as it simply filters the comm records by being the right type (2), and the FilterValues comparing the list summary field with the contact ID field from the related table. That should work, no?



            • 3. Re: Filter Portal Records Based on Found Set (redux)

              I am assuming board members are flagged in some way by way of data in a field, and that your filter matches only records that have the correct data that marks them as board member. I also assume that if other filtering IS working but this particular filter isn't, the problem may very well lie in the filter field.

              • 4. Re: Filter Portal Records Based on Found Set (redux)

                I'm thinking by your reply that I did not state the problem clearly.


                All filters are working. If I want inactive members, I apply the proper filter and get just the inactive members. If I want board members, I apply the proper filters and get just the board members. The filters themselves are working fine.


                There are 2 ways to tell the basic filtering is working fine.

                First, when I filter for board members, the found set contains 16 records. When I filter for inactive members, the found set contains 5 records.

                Second, after those filters are applied, I look at the summary list FoundIDs field, and in all cases it contains a list of the appropriate ID's.


                What is NOT working is the portal to the Comm table. For ALL members, the portal shows all email addresses. For inactive members, the portal shows only email addresses for those members. Ditto for all other filters, except for the board members filter, where the portal still shows email addresses for all members.


                So although the basic filtering is working OK, the portal filtering to the Comm table is not working when found set is only board members.


                I hope that clarifies the issue.

                • 5. Re: Filter Portal Records Based on Found Set (redux)

                  Ok, I guess I solved the issue myself. Well, not exactly myself.


                  I was researching Dwindling Value Lists, and found an excellent article on this: http://filemakerhacks.com/2012/06/10/dwindling-value-lists-2/

                  This technique also uses a portal, and they mentioned at the bottom that you need to refresh the portal, as it will not necessarily do so itself

                  So they recommend using Refresh Window [ Flush cached join results ].

                  I tried this and it now works perfectly.


                  Thanks for the answers.

                  • 6. Re: Filter Portal Records Based on Found Set (redux)

                    Or if you are using FM 13, you can use the new Refresh Object and point it at your (named) portal. That won't have the adverse performance effect that a full Refresh Window can have.

                    1 of 1 people found this helpful