1 2 3 Previous Next 31 Replies Latest reply on Mar 26, 2010 7:48 AM by elizas

    Portal filter using multiple search terms

    Polarpro

      Title

      Portal filter using multiple search terms

      Post

      Hi there.

       

      I find a lot of examples that show how you can easily create a portal filter: Typing a few letters in a search field and hitting the Enter key shows the results right away. Now with FM 10's script triggers things go even smoother. I personally use a method where I use the SearchField and a calc field ( = SearchField + "zzzzz" ). (Matt Petrowsky has a nice tutorial on this.)

       

      All of the methods I find deal with one single search term. I wonder if there is a way to filter a portal using multiple search terms, in order to have a Boolean AND in my portal search field. So far, everytime when I try to write a 2nd search term the portal doesn't show any results anymore as soon as I hit the space key.

       

      :) MIke 

        • 1. Re: Portal filter using multiple search terms
          philmodjunk
            

          Let's say my portal records have a "FruitName" field and a "Color" field.

           

          In my portal table, create a text calculation field, cNameColor: FruitName & " " & Color

          In my main table define three fields:

          gFruitName, gColor, cPortalKey

           

          gFruitName and gColor are global text fields (they don't have to be).

           

          In cPortalKey, make a text calculation field: gFruitName & " " & gColor

           

          Make your portal relationship: MainTable :: cPortalKey = PortalTable ::cNameColor

          • 2. Re: Portal filter using multiple search terms
            Polarpro
              

            Hey,

             

            Thank you for your fast help. Just tried it.

             

            The thing in my case though is that I need to key in the search terms in one single (global) search field. I'll play with it around a little and'll get back tomorrow, it seems to be the right direction...

             

            Mike 

             

            • 3. Re: Portal filter using multiple search terms
              philmodjunk
                 Not sure why you'd want to do that, but in that case just use a text field in place of the Calculation field in the Main Table. You'd have to enter your terms carefully as spaces and punctutaion can affect your results.
              • 4. Re: Portal filter using multiple search terms
                Polarpro
                   <!--     StartFragment     -->

                Thank you for looking at my problem.

                 

                 


                PhilModJunk wrote:
                Not sure why you'd want to do that

                 

                Let me be more specific:

                 

                My records have a "record title" that consists of several words. For searching a record, I have a specific Find layout where the users can key in their search terms in a global search field. I use script triggers to offer them a search-as-you-type search. This works fine; the users can enter multiple terms and get as result a list with all the records that contain the search terms.

                 

                For a few reasons I put portals on the "full view" layout of my records. These portals show a list of the other records in my file. From these portals records can be chosen to establish relationships between records. The portals work fine, too; but I'd like to give the user an easy opportunity to search for records just like they know it from the Find layout. So, when showing a record in full view, there is a field gSearchField and a portal that relates to itself; and the question now is how to establish the right relationship.

                 

                I looked at your suggestion and tried several things that came into my mind, but wasn't successful. I only was able to achieve an "OR" Find. This here is what I've worked out so far with a simple example table that contains colors:

                 

                My table has the fields:

                Colors

                cColors = Substitute ( Colors ; " " ;"¶" )

                gSearchField

                cSearchField = Substitute ( gSearchField ; "" ; "¶" )

                 

                I created a few simple records that have in the Colors field:

                #1 blue red

                #2 red white

                #3 white yellow

                #4 yellow green

                #5 green purple

                #6 purple yellow

                 

                The relationship looks as follows:

                Table::cSearchField=Table_Self::cColors

                 

                So, when I for example put "white red" in my gSearchField, the portal shows:

                #1 blue red

                #2 red white

                #3 white yellow

                 

                When I for example put "blue green" in gSearchField, the portal shows:

                #1 blue red

                #4 yellow green

                #5 green purple

                 

                So, this searches nicely for one OR the other word in the field Colors, but I am trying to search for one AND the other word in the field Colors.

                <!--     EndFragment     -->
                • 5. Re: Portal filter using multiple search terms
                  philmodjunk
                    

                  No where in my example did I use "¶". Instead, I used a space. That's the difference.

                   

                  The line breaks set up a multi-value relationship where, as you've discovered, the matching is based on "OR". Use the space character instead.

                  • 6. Re: Portal filter using multiple search terms
                    Polarpro
                      

                    PhilModJunk wrote:

                    No where in my example did I use "¶". Instead, I used a space.


                     

                    Yes, but you used the spaces because you put the contents of several fields together. As I have only one field to be searched there is no need to add several fields' contents. I used the line break to cut all the words in gSearchField into individual values that then can be matched.

                     


                    PhilModJunk wrote: 

                    Use the space character instead.


                     

                    But where?

                     

                    :smileyhappy: 


                    • 7. Re: Portal filter using multiple search terms
                      philmodjunk
                        

                      When you posted.

                       

                      #1 White Red

                       

                      I read that as three fields. Are white and red in the same field? That significantly complicates the portal filtering.

                      • 8. Re: Portal filter using multiple search terms
                        Polarpro
                          
                        Yes. There are six records, and blue red is the content of record #1:
                        My table has the fields:
                        Colors
                        cColors = Substitute ( Colors ; " " ;"¶" )
                        gSearchField
                        cSearchField = Substitute ( gSearchField ; " " ; "¶" )
                         
                        I created a few simple records that have in the Colors field:
                        #1 blue red
                        #2 red white
                        #3 white yellow
                        #4 yellow green
                        #5 green purple
                        #6 purple yellow 

                         

                        • 9. Re: Portal filter using multiple search terms
                          philmodjunk
                            

                          I gather the order of the "colors" not significant?

                           

                          In other words:  Red White should match White Red?

                          • 10. Re: Portal filter using multiple search terms
                            Polarpro
                               Yes, exactly.
                            • 11. Re: Portal filter using multiple search terms
                              philmodjunk
                                

                              And there lies the problem. I'm not seeing any way to make this happen using And logic instead of OR.  With the exception of a multi-value format that uses OR logic, filemaker's relationships compare the entire contents of one field to the entire contents of another field. In this case, you are looking for a "contains" operator that would let you match keywords.

                               

                              Sorry, but I just don't see a way to make this work as a filtered portal. Anybody else out there see a way to make this happen?

                              • 12. Re: Portal filter using multiple search terms
                                Polarpro
                                  

                                Thank you very much for your explanation and suggestions. That'd also explain why none of the solutions I looked closer at offered to search for more than one search term.

                                 

                                I wonder if there is a way to implement a find that then displays the results in the portal, so that it looks like searching on the fly. You can imagine, now that the users know and enjoy the search-as-you-type find (incl. multiple search terms), they ask for this feature also at other places.

                                 

                                I'll get back to you on this tomorrow! 

                                • 13. Re: Portal filter using multiple search terms
                                  philmodjunk
                                    

                                  Light bulb just popped on. This may work or it may be too cumbersome.

                                   

                                  Using cColors exactly as you've defined.

                                   

                                  Define two calculation fields cColor1 and cColor2

                                   

                                  cColor1 = MiddleWords ( gSearchField ; 1 ; 1 )

                                  cColor2 = If ( isempty ( MiddleWords ( gSearchField ; 2 ; 2 ) ; leftwords ( gSearchfield ; 1 ) ; MiddleWords ( gSearchField ; 2 ; 2 ) )

                                   

                                  make your relationship

                                   

                                  Table :: cColor1 = Table2 :: cColors AND

                                  Table :: cColor2 = Table2 :: cColors

                                   

                                  That will work for exactly two words entered into your search field. you'd have to enter more cColor fields to support a longer keyword list and this could get ugly.

                                  • 14. Re: Portal filter using multiple search terms
                                    philmodjunk
                                      

                                    Your mention of a script reminded me of an old trick. 

                                     

                                    First give every record a unique serial number.

                                     

                                    In your script:

                                     

                                    Enter Find mode []

                                    Set field [table::Colors ; gSearchList]

                                    Set Error Capture [on]

                                    Perform Find []

                                    Set error capture [off]

                                    Go to record [First]

                                    Set Field [gIdList ; "" ]

                                    Loop

                                      Set Field [table::gIDlist ; table::gIDlist & "¶" table::SerialNumber ]

                                      Go To Record [ Exit after Last ; next ]

                                    End Loop

                                     

                                    Now base your portal on the relationship: Table::gIDlist = Table2::SerialNumber

                                    1 2 3 Previous Next