While looking for how to search/filter a portal in a Card window, I found this 2010 article by Danny Kohn, which includes the custom function "FindWordPartsInText ( needles, haystack, start )": Dynamic Portal Filtering While You Type – FileMaker Inspirations
Using that function as a guide, I built the Custom Function SearchMultipleTerms ( searchTerms ; searchWithin ; matchAll ) that I can use to filter terms that appear in any of a number of fields in my related portal. The function acts similarly to Danny's but with some changes to suit my needs:
- It no longer requires the search term(s) to be at the start of the word (e.g. will return true when searching for "art" within "part")
- I've added the matchAll setting to specify whether to use an AND or OR search.
The rest of the portal filtering functionality works the way as described in the linked article by Danny.
With this function, and especially the matchAll variable, I was able to conditionally format the fields in the filtered portal results so that if there is a partial match (matchAll = 0) it highlights the field a bit, and if there is a full match (matchAll = 1 ) it highlights them with greater emphasis.
This variable could also easily be used to allow the user to toggle the portal filter between an AND and an OR search.
Here's the text of custom function SearchMultipleTerms ( searchTerms ; searchWithin ; matchAll )
This custom function allows you to search for a string of multiple words within another string (inlcuding in the middle of the string).
E.g. It will return true if searching for "abc def xyz" within the text "abcdefghixyz"
If matchAll is set to 1 or True, it will only return True if all of the search words to be present (i.e. false if searching for "abc pqr" within "abcdef").
If matchAll is set to 0 or False, it will return True as soon as it finds a single match (i.e. true if searching for "abc pqr" within "nopqrstu").
Let ( [
searchTerm = LeftWords ( searchTerms ; 1 ) ;
found = PatternCount ( searchWithin ; searchTerm ) > 0 // can't use PatternCount on its own because 2 will not match a matchAll value of 1
( found ≠ matchAll ) or WordCount ( searchTerms ) < 2 ; found ;
SearchMultipleTerms ( RightWords ( searchTerms ; WordCount ( searchTerms ) - 1 ) ; searchWithin ; matchAll )
) // Case
) // Let
/* How this works/Logic table:
1. If found & not matchall, return True (no need to evaluate further)
2. If not found & matchall, return False (no need to evaluate further)
Therefore 1+2: If found ≠ matchall, return found (no need to evaluate further)
3. If found & matchall, evaluate next or return True if last iteration
4. If not found & not matchall, evaluate next or return False if last iteration
Therefore 3+4: If found = matchall, return found if last iteration, else evaluate next level down.
(I've verified that matchall = found even if one is true and the other is 1)
I've found a couple of threads where people are asking about how to achieve this functionality (i.e.Re: How do I filter a portal with certain text from a field having multiple words? and filtering portal using multiple words and Filter Portal Search Results by Partial Match and Re: Search box to filter portal ). Since they're mostly old and none had a response suggesting this method, I'm posting this as a new thread.
I considered adding a variable to toggle whether to require the search terms to be at the start of words, but that's too complicated for something I don't need.
I hope it's useful!