10 Replies Latest reply on Jul 19, 2014 5:32 PM by nihmbrisby

    filtering portal using multiple words

    nihmbrisby

      Title

      filtering portal using multiple words

      Post

           I've been using the following calulcation (which I think I nabbed from the filemaker invoice starter) as my go to filter (though I'm using a non-global search field):

           If
           IsEmpty ( TableA::non_global_search_field) ; 1 ; 
           PatternCount ( 
           List ( 
           TableB::FirstName; 
           TableB::LastName ) ; 
           TableA::non_global_search_field ) > 0
           )
            
           If my search term is 2 or more words, it searches for that phrase (as one would expect).  'Jo mi' does not find John Smith.  However, I would like it to.  Furthermore I would like "Jo mi" to find John Smith AND Michelle Jones BUT NOT Joe Williams.  
            
           Any pointers?  I'll be trying to craft something of my own, but this is one of those things that community input usually serves to prevent me from using some super slow or error prone method.  Thanks.

        • 1. Re: filtering portal using multiple words
          philmodjunk

               Let ( [ f = Leftwords ( TableA::non_global_search_field ; 1 ) ;
                         L = Rightwords ( TableA::non_global_search_field ; 1 ) ] ;
                        ( PatternCount ( TableB::FirstName ; f ) and PatternCount ( TableB::LastName ; L ) ) or
                        ( PatternCount ( TableB::LastName ; f ) and PatternCount ( TableB::FirstName ; L ) )
                     ) // Let

               This assumes that you always enter a two word search pattern and like your original expression searches for that text anywhere in the field. There are other ways to set this up that limit the matching to a "starts with this text" rule.

               Note: Anytime you need a Boolean result (Result must be True or False), such as in a portal filter, conditional format expression, If script step, If function, Case function...., a number result that is not zero or empty will be treated as True and results of 0 or empty will be treated as false, so there is no need to use > 0 to test for nonzero results from the PatternCount function.

          • 2. Re: filtering portal using multiple words
            nihmbrisby

                 Thanks Phil.  I actually require at least 5 word searches across three or more fields.  My first instinct was to think 'loop'- noob that I am.  There is no loop calculation!  I did some research and it seemed like the only way I could keep the number of and's & or's from going exponential was a custom function used recursively.  But I'd rather not do that.  I'm hoping this will work instead.  

                  

                 If ( IsEmpty ( TableA::search) ; 1 ; 
                  
                 Let ( [ 
                 $i = WordCount ( TableA::search ) ;
                 $search = TableA::search ;
                 $x = TableB::Field1 ;
                 $y = TableB::Field2 ;
                 $z = TableB::Field3 ;
                 $list = List ( $x ; $y; $z )
                 ];
                  
                 Case ( 
                  
                 $i = 1; 
                 Let ( 
                 $a = LeftWords ( $search ; 1 ) 
                 ; 
                 PatternCount ( $list ; $a )
                 ) 
                 ;
                  
                 $i = 2; 
                 Let ( 
                 [ 
                 $a = MiddleWords ( $search ; 1 ; 1) ; 
                 $b = MiddleWords ( $search ; 2 ; 1) 
                 ]; 
                 PatternCount ( $list ; $a ) and PatternCount ( $list ; $b ) 
                 )  
                 ; 
                  
                 $i = 3; 
                 Let ( 
                 [ 
                 $a = MiddleWords ( $search ; 1 ; 1) ; 
                 $b = MiddleWords ( $search ; 2 ; 1) ; 
                 $c = MiddleWords ( $search ; 3 ; 1)
                 ]; 
                 PatternCount ( $list ; $a ) and PatternCount ( $list ; $b ) and PatternCount ( $list ; $c ) 
                 )  
                 ; 
                  
                 $i = 4; 
                 Let ( 
                 [ 
                 $a = MiddleWords ( $search ; 1 ; 1) ; 
                 $b = MiddleWords ( $search ; 2 ; 1) ; 
                 $c = MiddleWords ( $search ; 3 ; 1) ;
                 $d = MiddleWords ( $search ; 4 ; 1) 
                 ]; 
                 PatternCount ( $list ; $a ) and PatternCount ( $list ; $b ) and PatternCount ( $list ; $c )  and PatternCount ( $list ; $d ) 
                 )   
                 ; 
                  
                 $i = 5; 
                 Let ( 
                 [ 
                 $a = MiddleWords ( $search ; 1 ; 1) ; 
                 $b = MiddleWords ( $search ; 2 ; 1) ; 
                 $c = MiddleWords ( $search ; 3 ; 1) ;
                 $d = MiddleWords ( $search ; 4 ; 1) ;
                 $e = MiddleWords ( $search ; 5 ; 1) 
                 ]; 
                 PatternCount ( $list ; $a ) and PatternCount ( $list ; $b ) and PatternCount ( $list ; $c )  and PatternCount ( $list ; $d ) and PatternCount ( $list ; $e ) 
                 ) 
                 ; 
                  
                 0 
                 )
                 )
                 )
                  
            • 3. Re: filtering portal using multiple words
              philmodjunk
                   

                        I actually require at least 5 word searches across three or more fields.

                   You might want to consider using ExecuteSQL for something that complex. An ExcuteSQL query can produce a return separated list of primary keys that can then be used as the parent record's match field in the portal relationship.

                   You also might want to evaluate your data model to see if it is really an optimum design for what you want to do here given the need for such a complex portal filter expression--which will tend to update very slowly as you get larger numbers of records into your table than you might have during the development process.

              • 4. Re: filtering portal using multiple words
                nihmbrisby

                     Thanks Phil.  My situation is basically this-  users of my database will be looking at a portal showing all records from a table other than the table on which the layout is based (just a simple cartesian join).  Lets say this other table is books.  The portal is a navigational aid (one of many).  Generally, if the user wants to find great expectations, they'd just type dickens, or expectations.   This would filter the portal down to 5 or 10 - from which they'd select the book (just a standard button on the portal) to open in a new window.  

                     Frequently they would want to just type shake mids to look up A Midsummer Night's Dream.  Every now and then they'd get even more complex- a few digits from a catalogue number + plus a few characters of the title + a few characters of the author.

                     I'm not required to provide this level of functionality, but I'd very much like to.  If five terms across five fields was really to much a hit on performance, I could always do less.  

                • 5. Re: filtering portal using multiple words
                  philmodjunk

                       If five terms across five fields was really to much a hit on performance, I could always do less.

                       How big a "hit" this is will vary greatly with the design of your database, your network capabilities (LAN vs. WAN for example) and the processor speed of the client and server involved. (iOS clients are generally much slower than Windows or Mac clients)

                       So you may need to do your own testing with a realistically sized data set and see how it works for you.

                       But you may want to consider other options as "plan B" if you encounter long delays getting the system to respond to you.

                       A value list can be used to control the value of a match field used in the relationship so that the user can narrow by category before entering search text, such as selecting "Fiction" over "nonFiction" or Science Fiction over Romance.

                       would be one such method for narrowing the search down.

                       Don't know if you've seen this demo file that does the type of partial name matching via search portal like you describe or not, but if not, you may find it a useful source of ideas: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                       It's in the older file format so users with FileMaker 12 or newer will need to use Open from FileMaker's File menu to open this file and convert it to the newer file format.

                  • 6. Re: filtering portal using multiple words
                    nihmbrisby

                         Phil- as always, I hugely appreciate your input.  Plan B's have been floating around in my head, and your advice gives me a good framework in which to think about the problem.  I'm going to take a close look at the demo file.

                    • 7. Re: filtering portal using multiple words
                      nihmbrisby

                           What would be the significance of switching from a three term search in one global field used as a portal filter to 3 global search/filter fields which would then be used as match fields (ie a new relationship using these three fields as the match fields)? 3 global match fields vs 1 portal filter (using the above calculation)?

                      • 8. Re: filtering portal using multiple words
                        philmodjunk

                             I can't quote you any benchmarks on this so your mileage may vary, but I'd expect to see faster updates with the match field based "filtering". "How much faster"? well that's tough to say without running tests where all other factors are kept equal.

                        • 9. Re: filtering portal using multiple words
                          nihmbrisby

                               Thanks Phil.  I'll have to give this some thought.

                          • 10. Re: filtering portal using multiple words
                            nihmbrisby

                                 So I was finally able to test this out on a WAN with 20,000 records.  

                                 *sigh*

                                 It's slow.  I can deal with the speed it takes to filter (~1.5 seconds).  The problem is the speed of the pop up.  This portal is in a pop up, meaning (as I've read on the tech net) that the portal is not evaluated until the pop up is triggered.  Which is great- I generally prefer it that way, and if it didn't lag on pop up it would just be lagging on record load presumably.  Anyways, it takes about 2.5 seconds to pop up which is a no go.  I tried the method outlines on this page (which I was referred to by the weetbicks blog): http://filemakerinspirations.com/2009/01/google-like-search-through-relationship-filtering/

                                 Basically it takes my 'search' field and creates to new fields by calculation: "searchterm" and "searchtermzzzz." It then matches to the target table like so: _searchterm<=Target_Table::Field AND Target_Table::Field<=searchtermzzzz.

                                 It's pretty quick and works well.  The main problem is that I'd really like for 'mit' to match 'smith' (ie, in terms of wildcards, I want mit to be interpretted as *mit* as my previous method does).  However this 'range' method only matches from the beginning of the word.