1 2 Previous Next 24 Replies Latest reply on Mar 14, 2014 11:48 AM by rmittelman

    Filter Portal Records Based on Found Set

    rmittelman

      My solution is based on the Contacts starter solution. I have a Contacts table, and a Communications table. On my Contacts Detail layout, there is a portal to the Communications table. The relationship is Contacts::Contact ID = Communications::Contact ID. Communications table also has a Comm Type field, where 1 = Phone number and 2 = Email.

       

      So the portal is filtered by Comm Type = 2, because it's an emails portal. So far, everything works ok. While displaying a contact record, it shows all of his/her email addresses.

       

      Now I want to add a different portal, on the Contacts list form, so when I click the "Email" button (a popover button), it opens a popover with a portal which will show ALL emails for ALL contacts.

      I hope I did this right: I created a new table relationship between Contacts and Communications, this time with cartesian join. I based the new portal on this new table instance. I filter the portal with Comm Type = 2, just like the original portal. But since it's cartesian, it shows all emails. This is good.

       

      So here's the challenge: My Contacts listing layout can itself be filtered, showing all contacts, or filtering them by various criteria. When I filter them to get a small found set, I would like the new portal to only show records from that found set, not the entire contacts table. The question is, how do you filter on the found set? Instead of "Comm Type = 2" I want something like "Comm Type = 2 AND Contact ID in <found set>"

       

      Is there a way to do this in FMP? Thanks in advance...

        • 1. Re: Filter Portal Records Based on Found Set
          erolst

          If you've got FM13, look into the summary field type "List of". You could use

           

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

           

          If this gets too slow, you can use a filtered relationship instead, with a global calculation field = 2 and a calculation with the GetSummary() from above, and the predicates

           

          Contacts::globalTwo = Comm::commType

          Contacts::IDsummary = Comm::contactFK

           

          In FM12, build the ID list of the found set by using a script with GetNthRecord(), e.g.

           

          Set Variable [ $$IDList; Value: "" ]

          Loop

            Set Variable [ $curValue; Value: Let ( $i = $i + 1 ; GetNthRecord ( Contacts::primaryID ; $i ) ) ]

            Set Variable [ $$IDList; Value: List ( $$IDList ; $curValue ) ]

            Exit Loop If [ $i = Get ( FoundCount ) ]

          End Loop

          • 2. Re: Filter Portal Records Based on Found Set
            rmittelman

            Thanks, @erolst.  As usual, you da man...

             

            I added a summary field called "Found IDs", which is defined as a List Of summary field.  The following in portal filter seems to work:

            All Emails::Comm Type = 2 and not IsEmpty ( FilterValues ( Contacts::Found IDs ; All Emails::CONTACT ID MATCHING FIELD ) )

             

            My entire Contacts list is ~200 records, so it works pretty fast.  I don't know why you included the GetSummary function, but it seems to work ok without it.

             

            There's another slight issue.  Since people can have multiple emails, I want the portal list to be easy to read, so it should look something like:

             

            Jones  Bob@jones.com

            Jones  Sara@jones.com

            Smith   Sam@smith.com

            Smith   John@smith.com

            etc...

             

            That way, it's easy to see the "family" each email comes from.

             

            Here's the challenge:  If I drop a field on the portal referring to Contacts::Last Name, then every record says "Jones" or "Smith", depending upon which contact record is active when I open the popover containing the portal.

            I tried adding a "Last Name" field to the communications table, with an auto-enter filling it with the Last Name of the parent Contacts record on creation.  This works, but seems to violate normalization rules.  I'm thinking maybe an unstored calculated field in the communications table pointing to the Contact record's Last Name field.  Even though it technically violates normalization rules, it's unstored and calculated when needed, so no worries about data becoming out of sync, right?

             

            What would you suggest?

            Thanks again so much for quick reply...

             

            EDIT:  I tried the above idea and it doesn't seem to work.  In fact, the portal comes out sorted in a strange manner.

            • 3. Re: Filter Portal Records Based on Found Set
              erolst

              rmittelman wrote:

              Thanks, @erolst.  As usual, you da man...

              For all you know, I might be “da woman” …

               

              Anyway, you're right, you don't need GetSummary(), since you're interested in the entire found set anyway. (And since you use a popover, of course you have FM13; I need to get used to this …)

              rmittelman wrote:

              I added a summary field called "Found IDs", which is defined as a List Of summary field.  The following in portal filter seems to work:

              All Emails::Comm Type = 2 and not IsEmpty ( FilterValues ( Contacts::Found IDs ; All Emails::CONTACT ID MATCHING FIELD ) )

               

              Here's the challenge:  If I drop a field on the portal referring to Contacts::Last Name, then every record says "Jones" or "Smith", depending upon which contact record is active when I open the popover containing the portal.

              The problem is that the related records in Comm cannot “look backwards” to their matching Contacts via this relationship – all they see is the first matching record.

               

              This “challenge” is easy to solve without an additional field (though a bit of overhead is unavoidable): let your Comm records “look ahead” by relating a new TO of Contacts to the TO of Comm your portal is based on; then “drop” the name field from that TO into the portal.

               

              Having said that: if you find yourself adding TOs all over the place just to “pipe through” the same data over and over, take a page from the FM6 era (when relationships were one-directional, one-hop only) and add a calc field to do so; I'd rather have one more field than 20 additional TOs, considering the tools we have to navigate the Relationship Graph (or lack of same …)

              • 4. Re: Filter Portal Records Based on Found Set
                rmittelman

                "you da man" is a unisex thing, like "mankind" or "man, this is confusing".  Speaking of which, here's what I have now:

                 

                Contacts ----- = -----> Communications

                Shows just the emails or phones for the current contact, works fine.

                 

                Contacts ----- X -----> All Emails (a TO of Communications)

                Shows all emails for all contacts.  Can't filter, as it's cartesian.

                 

                All Emails ----- = -----> All Emails Contacts (a TO of contacts)

                This gives me the last name for any particular item in All Emails.

                 

                All of these joined by the same Contact ID field.

                 

                 

                This seems to work, but I can no longer sort by the last name, as I could when I included a LastName field in the Communications table.  The Sort function in either the relationship or the portal won't let me refer to any fields for any other table when I'm adding to the sort order.  Don't I need to have a calc field in the Communications table (hence also in the All Emails TO) in order to sort my portal records by it?

                 

                Not sure I follow the last paragraph.  Well, I follow, but I tried that before and got strange results.

                 

                Thanks...

                • 5. Re: Filter Portal Records Based on Found Set
                  erolst

                  rmittelman wrote:

                  "you da man" is a unisex thing, like "mankind" or "man, this is confusing".

                  I see … isn't the pc word nowadays "humankind”? But I digress …

                  The Sort function in either the relationship or the portal won't let me refer to any fields for any other table when I'm adding to the sort order.

                  Time for the lil' bag o' tricks: Open the portal setup, switch the “Records from” popup to the TO from where you want to sort (All Emails Contacts), open the sort dialog, select the name field, click OK, select the previous portal TO (All Emails), click OK, done.

                  Shows all emails for all contacts.  Can't filter, as it's cartesian.

                  And you know a portal showing a Cartesian relationship can't be filtered because …?

                  1 of 1 people found this helpful
                  • 6. Re: Filter Portal Records Based on Found Set
                    rmittelman

                    OK, I think I misspoke.  For some reason, I thought it didn't offer filtering.  Of course it does, I see that now.

                     

                    So if I make a calculated field called LastName in the communications table, and base it on the LastName in the Contacts table, it will not work, at least in the TO with the cartesian join, right?  On the "main" relationship, it will, as each Communication record has a 1-to-1 with a Contacts record.  So I should remove the calculated field I added from the Communication table.  Instead, in the portal based on the cartesian TO, I can add a <<All Emails Contacts::LastName>> merge field and that will work, then use the "bag 'o tricks" trick to accomplish the sort.

                     

                    So far, so good.  I will try this.  I now have a bigger issue, as the "check-uncheck" functionality no longer works properly in the portal.  Even though it updates my $$list variable properly, it's no longer properly updating the tick mark.  It immediately updates the $$list, but the item I tapped on in the portal doesn't update until I tap on another portal item.  Except on the original portal it is working just fine.  I use the same script from each portal, sending the appropriate table/TO name via a parameter.  I will need to play with this a bit more.

                     

                    Thanks...

                    • 7. Re: Filter Portal Records Based on Found Set
                      erolst

                      rmittelman wrote:

                      So if I make a calculated field called LastName in the communications table, and base it on the LastName in the Contacts table, it will not work, at least in the TO with the cartesian join, right?

                       

                      Of course it will work, once it's properly defined. To ensure this, you must use a relationship between Comm and Contacts that's based solely on the ContactID, not any fancy/dynamic stuff that may be going on.

                       

                      Portal filtering / and or relationship predicates on one hand, and sorting of the relationship and / or any portal on the other are independent of each other.

                       

                      The long and short of it: to make your sorting work, you need an additional piece: either a new TO (and the portal sorting trick) to use the original field, or a new calculated field.

                      • 8. Re: Filter Portal Records Based on Found Set
                        rmittelman

                        Except that you told me before that the reference to the Contacts table with the cartesian join would only show the name from the first contact in every record of the TO table in the portal.  That's why you suggested a new 1-1 relationship to a new Contacts table TO in order to get that name when needed.

                         

                        It is unclear to me at this time whether once having the 1-1 relationship back to the Contacts table, it is better to use the sorting trick or use a calculated field. 

                        Presently, I have removed the calculated field from Communications, and am using the 1-1 relationship between All Emails and All Emails Contacts, in conjunction with the sorting trick.  This seems to work.

                         

                        Still have that problem where clicking the items in the popover portal does not update the tick mark until I click on another portal row, at which time the tick appears for the previously clicked-on row.  This does NOT occur for the popover which appears based on a button click on each individual row of the contacts list. 

                         

                        To clarify, it was working fine with only one popover, whether the popover button was located in the header of the layout or on the record of the layout.  Either way, the portal referenced the Communication table and only showed emails for the currently selected Contact record.  I copied this entire popover button and popover and pasted it into the header, then changed all relationships to the newly created Communication TO.  That worked ok until I started playing with the LastName field, and complicated things.  I will go through all scripts, button setups, triggers and conditional formatting to see what is causing the issue.

                         

                        Thanks...

                         

                        EDIT:  Never mind, now it's working.  I'm not sure what changed, but now the ticks are appearing/disappearing in a timely manner.  I really like that bag-o-tricks of yours.  Send me more!

                        • 9. Re: Filter Portal Records Based on Found Set
                          erolst

                          rmittelman wrote:

                          I will go through all scripts, button setups, triggers and conditional formatting …

                          … object names!

                           

                          Your problems most likely come from the fact that copied objects don't retain the original's name (but have “copy” added to it), since each object on a layout must have a unique name. Then the script (or its copy) will reference the original object and fail to refresh the “copy”.

                           

                          See here for a working version, plus the correct arrangement of the TOs necessary, and including the sort trick.

                          • 10. Re: Filter Portal Records Based on Found Set
                            rmittelman

                            Wow, thanks.  Got it figured out in the meantime.  You couldn't have been more wrong about the reason.  The original popover was called "selectionPop"  The copied one was not called "selectionPop copy".  It was called "selectionPop 2" (see disclaimer below).  Once I changed the popup name (by adding it as a parameter), all worked ok.

                             

                            Disclaimer:  My droll sense of humor has gotten me in trouble many times.  I hope that isn't happening here...

                             

                            Thanks a bunch for all of your help, including the sample apps.  I will check your newest file just to make sure I'm doing this properly. 

                             

                            The real challenge is to actually create the email.  Presently it's just popping up a dialog with the email addresses.  Pretty impressive in its own right, but not useful.  My new task is to figure out how to actually configure the email.  Specifically, the TO address and salutation.  I have a mix of situations.  Contact has last name, first name, last name 2, first name 2 fields.  The communication record has a Description field ("Joe", "Sara", "Bill Work", etc.)  If only one email record I usually leave this blank.  I added a Salutation field to this table so I can enter: "  "Dear " & Salutation " in the email body.  I need to calculate the TO address ("John Doe <john@doe.com>") depending upon each item chosen.  I will have to think on this...  It's a big problem, but a fun one for us programmer-types.

                            • 11. Re: Filter Portal Records Based on Found Set
                              rmittelman

                              Very nice.  Like the way you can toggle showing found set only or not.  Curious about that script section.  I see you are toggling the global field. Why do you set Customers::ID to Customers::ID, and what causes the popup/portal to re-evaluate?

                               

                              Thanks...

                              • 12. Re: Filter Portal Records Based on Found Set
                                erolst

                                rmittelman wrote:

                                Why do you set Customers::ID to Customers::ID, and what causes the popup/portal to re-evaluate?

                                Exactly that; change a key field in the relationship, and you force it to refresh. Refresh Object didn't work here reliably, neither for the popup panel nor the portal object (or both combined), and I didn't want to use the blunt instrument that Refresh Window is.

                                rmittelman wrote:

                                I will have to think on this...  It's a big problem, but a fun one for us programmer-types.

                                Well, not that big. If you have 15 mins. time … all you need is a global field and a new TO.

                                 

                                Create a relationship into Comm based on a global field and matching all records in the found set*, and in the Send section of the script, set that global with the selected commIDs. Then call a dedicated Send script …

                                * This will give you a wrong result if you choose an address from a Customer not in the found set, so you may want to remove the toggle and only allow selection from the found set (or still use the toggle and preemptively Show All Records before performing the Send script).

                                 

                                If you want to send maximally one mail per person (i.e. to all their selected addresses), go to the related Comm records, then back to the related Customers and gather the selected addresses via the global relationship.

                                If you want to send a separate mail to each address (i.e. possibly several mails to the same person), just go to the related Comm records and send them from there.

                                 

                                The upshot: choose a context for sending where you can make the record count the same as the number of mails you want to send.

                                 

                                OTOH, there's always at least one other way to get to the same result, so consider this merely an approach, not the gospel (even though it comes from me …).

                                 

                                Have a look at the attached file.

                                rmittelman wrote:

                                Disclaimer:  My droll sense of humor has gotten me in trouble many times

                                And here I was thinking you had that surgically removed …

                                 

                                PS: Don't you think “The Question has been answered”?

                                1 of 1 people found this helpful
                                • 13. Re: Filter Portal Records Based on Found Set
                                  rmittelman

                                  Thanks for the tip of resetting a key field in the relationship.  Who knew that the actual refresh commands were not reliable...

                                   

                                  The reason I said this was a complex problem was not for the reasons you think.  Easy enough to make an email and include all the addresses in $$list.  I was more thinking of the send-to name and salutation.

                                   

                                  This is a club, with either a single person or a husband-wife / partner-partner membership record.  I have LastName, FirstName, LastName2 and FirstName2 fields. LastName2 is in case the wife wants to keep her maiden name, or for unmarrieds.  I have calculated fields to make full names for labels for example, so I can get "Bob & Carol Jones" or "Ted Smith & Alice InWonderland"

                                   

                                  The problem occurs when the email record, which has a description, needs to be turned into a real "TO" address.  So if my email records for the first account are:

                                   

                                  Description: Bob

                                  Email: Bob@me.com

                                   

                                  Description: Carol

                                  Email: Carol@me.com

                                   

                                  I could use the description field for the salutation (unless it was "Bob Work" or something like that), but would have nothing for the actual TO address, which should be: "Bob Jones <Bob@me.com>".

                                   

                                  This is a complex calculation, as if I'm in my "easy" portal, I just go back to the Contacts table to get the first & last name, but that has both husband & wife's name, so which do I use?  Further, if I'm in my "hard" portal (the cartesian one), I need to go to All Emails Contacts TO instead, and still have the same problem as above.

                                   

                                  So I solved it by just going old-school and adding a "Send To" field and a "Salutation" field to the Communications table, and filling them all in with the appropriate values.  Therefore, no looking around and/or calculating send-to or salutation. 

                                   

                                  I guess in the processing section of the script, need to loop thru portal, and if the row is checked (ie: in $$list), add the appropriate fields to another list of Send-to's and of Salutations before actually generating the emails.

                                   

                                  Since there's no room in the portal for Send-To and Salutation, maybe I'll figure out how to do a dynamic tool tip for each portal row.  That shouldn't be too hard, right?

                                   

                                  Thanks again for all the help.

                                  • 14. Re: Filter Portal Records Based on Found Set
                                    rmittelman

                                    So I guess this isn't so difficult if you want 1 email sent to all chosen recipients.  I end up with a list $$toList which looks like:

                                     

                                    Bob <Bob@me.com>

                                    Carol <Carol@me.com

                                    Ted <Ted@you.com>

                                    Alice <Alice@you.com>

                                     

                                    As long as I send only one email, I can substitute the NewLine character with "; " and use that for the Send-To field.

                                    It seems a bit harder to do individual emails, as it looks like it wants to send to each record in the found set.  That would mean somehow remembering the current filter criteria, then filtering the current found set so only those included in $$list are included, then generating the emails, then returning to the immediately prior found set.  Not at all sure how to do all of that.

                                     

                                    Either way, never having done emailing in FMP, I don't know if it leaves you with 1 or more open emails on which you need to click the Send button (preferable, for editing purposes), or it just sends.

                                     

                                    EDIT:  How in the world do you constrain a found set based on a value list of email addresses?  My found set contains 50 records, and I selected 5 records in the popover, so my selected list is:

                                     

                                    Email1@one.com

                                    Email2@two.com

                                    Email3@three.com

                                    Email4@four.com

                                    Email5@five.com

                                     

                                    I want to say something like "constrain the current found set so only records whose Email Address is in the above list".  I can either accept if they are in, or omit if they are not in, the list.  I don't know how to do this in the filter window.

                                    1 2 Previous Next