1 2 Previous Next 16 Replies Latest reply on Aug 11, 2017 8:09 AM by philmodjunk

    Search as you Type multiple Fields at once?

    ezeitgeist

      Situation:

      I have a portal in a layout that is filled via a VL with 6 fields (2 not displayed the "n" and the Serial_ID of the record pulled into the VL).

       

      The VL Table is linked to the main layout by:

      Contacts::zg_searchField <--X--> VL_List_ReVamp::VirtualField::n

       

      I have the search field self-update on OnObjectModify to get live-as-you-type results using the below formula in the portal Filter calculation:

       

      IsEmpty ( Contacts::zg_searchField ) or PatternCount ( VL_List_ReVamp::VirtualField ; Contacts::zg_searchField ) or PatternCount ( VL_List_ReVamp::VirtualField2 ; Contacts::zg_searchField )

       

      This allows me to enter a search in the search field that gives results from either VirtualField OR VirtualField2 fields. BUT, one or the other only.

       

      What I want:

      I want to be able to type in the search field "New York" (VirtualField info) AND "Agent" (VirtualField2 info) and get results for ONLY New York Agents in the portal. Currently it gives NO results if "New York Agent" is typed in.

       

      Is there a workaround or is it a full teardown?

        • 1. Re: Search as you Type multiple Fields at once?
          philmodjunk

          Use two search fields, one for each criterion. Then modify your portal filter to reference the two different search fields as needed to patterncount on the appropriate fields with the appropriate text.

          1 of 1 people found this helpful
          • 2. Re: Search as you Type multiple Fields at once?
            ezeitgeist

            What would that look like in terms of the portal filtered calculation? Would it be the below where searchField1 and searchField2 are 2 separate search fields in the Contacts Table?

             

            IsEmpty ( Contacts::zg_searchField ) or PatternCount ( VL_List_ReVamp::VirtualField ; Contacts::searchField1 ) or PatternCount ( VL_List_ReVamp::VirtualField2 ; Contacts::searchField2 )

            • 3. Re: Search as you Type multiple Fields at once?
              philmodjunk

              Basically, but notice that you are not checking to see if the second search field is empty. Once you are searching by more than one criteria, the portal filters rapidly grow in complexity.

               

              So your filter logic needs to handle:

               

              //If both search fields are empty

              ( Isempty ( field1 ) and Isempty (field 2 ) ) OR

               

              //If search field 1 is empty, but search field 2 is not

              ( IsEmpty ( field 1 ) and Patterncount ( portalField2 ; field 2 ) ) OR

               

              //If search 1 is not empty but search field 2 is empty

              ( PatternCount ( portalField1 ; Field 1 ) AND IsEmpty ( Field 2 ) ) OR

               

              //Both search fields have text

              ( PatternCount ( portalField1 ; field 1 ) and Patterncount ( portalfield2 ; field 2 )

              1 of 1 people found this helpful
              • 4. Re: Search as you Type multiple Fields at once?
                ezeitgeist

                I follow.

                 

                Okay, so when I have 3 search fields it gets even more crazy. And given I have 4 fields in my portal, I'm looking at 4! different scenarios.

                 

                Is there a more efficient way to organize it so that it does a Perform Find and Constrain Found Set with each Search field, and then have that Search update the VL Portal somehow?

                 

                It may be wishful thinking. I was using Master Detail and could search all aspects of the records in the Main table, and then the portal would be updated, but it had some glitches with my setup so I tried to simplify it since everything in the VL portal is from the same Main table. Mainly, if I could have a Search field that did a general search of any occurrence in any field in the table and then updated the portal filter, that would be amazing. Not on FMP16, which has window cards or whatever it is called, so cannot use that.

                • 5. Re: Search as you Type multiple Fields at once?
                  philmodjunk

                  It's a limitation inherent to this method of "update the portal as you type" approach with partial text matching.

                   

                  Other approaches are possible, but I think that you'd tend to lose the ability to update the list with each key stroke, such as filling out a "search form" of several fields, then clicking a button to get a list of matches. That approach could work from either a scripted find or possibly a creative use of an ExecuteSQL query to pull up a list of primary keys to plug into a multi-key type relationship.

                  1 of 1 people found this helpful
                  • 6. Re: Search as you Type multiple Fields at once?
                    ezeitgeist

                    Okay, that all makes sense. Thank you. So one possible work-around (if it is even possible) is it possible to perform a Find on records and then update a VL portal of that table to just show the current constrained set?

                     

                    As in, I do a Perform Find for one of the fields in the table that is NOT in the VL and then update the VL based on the constrained results? Just curious. If not, I have another idea I can try, but just thought if that was a functionality possible?

                    • 7. Re: Search as you Type multiple Fields at once?
                      philmodjunk

                      If by "VL", you mean virtual list, then no, that wasn't what I had in mind.

                       

                      In v16, you might use the new Card window to perform the Find and list the results.

                       

                      Or you might add a "list of" summary field that lists the table's primary key field to the table being searched. Your script performs the Find, then sets a global field in your layout's table to the value of this summary field. This global field would be the match field to the portal's table.

                      1 of 1 people found this helpful
                      • 8. Re: Search as you Type multiple Fields at once?
                        ezeitgeist

                        On that last note regarding "list of"summary field ...

                         

                        I have VL::VirtualField_0 which contains the Main Table::Serial_ID.

                         

                        So, am I following correctly that I could create a "list of" summary field (in the Main Table), hide that outside the window, and then have that match up to the Virtual List Table (that populates the portal)?

                         

                        If YES, how exactly would I do that?

                         

                        [I just tried adding an additional VirtualField to my VL Table and then added that to the Search As I Type via an "or PatternCount" in the portal filter, but given the specific field used it slowed down both Virtualizing the Found Set to unacceptable levels and was not giving me the correct desired results.]

                        • 9. Re: Search as you Type multiple Fields at once?
                          ezeitgeist

                          Also, could I just create a NEW VL with the results and then fill that VL portal with those new results, temporarily?

                          • 10. Re: Search as you Type multiple Fields at once?
                            philmodjunk

                            I don't see anything that makes that impossible, but bu it's not clear to me what you gain by using a virtual list here in the first place. What problem does that solve for you here? I've avoided it in my examples because it adds a layer of complexity to the process and the extra steps needed to produce the list will increase how long it takes to get a response.

                            1 of 1 people found this helpful
                            • 11. Re: Search as you Type multiple Fields at once?
                              ezeitgeist

                              Oh crap, I think I've been missing what you have been saying the whole time. You are advocating for just a 2nd TO instead of a VL?

                              • 12. Re: Search as you Type multiple Fields at once?
                                philmodjunk

                                Yes and no. You may have very good reasons to use a Virtual List. I just don't see that reason in the info of this discussion. So I haven't included a VL as part of my examples here.

                                1 of 1 people found this helpful
                                • 13. Re: Search as you Type multiple Fields at once?
                                  ezeitgeist

                                  No, you are 100% right. I had tunnel vision as I was modifying what once was a MasterDetail setup so I was in the VL mindset. It works faster and is easier to deal with as a 2nd TO.

                                   

                                  My remaining question relates to a solution you mentioned linking "list of" to the portal results:

                                   

                                  So, am I following correctly that I could create a "list of" summary field (in the Main Table), hide that outside the window, and then have that match up to the TO Table (that populates the portal)? If so, how do I do that?

                                  • 14. Re: Search as you Type multiple Fields at once?
                                    philmodjunk

                                    For those reading along at home, a Virtual List is a handy trick for pulling data from more than one record into a single row in a report. It's particularly useful for "cross tab" or "pivot table" type formats.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next