ContainsAll ( Text ; SearchStrings )

checks to see whether all values in SearchStrings are contained in Text

Average rating: 4.6 (24 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

ContainsAll ( "24128" ; "412" )
ContainsAll ( "Jim,Charlie,Frank,Jane,Michelle,George,Kim,Chris" ; "Jim,Chris,Jane" )
ContainsAll ( "Jim,Charlie,Frank,Jane,Michelle,George,Kim,Chris" ; "Jim,Chris, Jane" )
ContainsAll ( "Jim,Jane" ; "m\,J" )

Sample output:

1
1
0
1

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*******************************
* Bill Thurmes 2016
* Syntax: Contains ( Text ; SearchStrings )
* Text = any text, including a delimited value list
* SearchStrings = Delimited value list, with accepted delimiters being commas, semicolons, or ¶
* To include commas, semicolons, ¶ (to be matched) in SearchStrings, include \ before them
*
* Purpose: Determine whether Text contains any of the strings contained in SearchStrings ('or' logic)
* Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
* If using two delimited value lists, FilterValues is probably faster (use with ValueCount)
* Restriction: the following !-containing strings may not be part of SearchStings: !c! !s! !p!
*
* Examples: ContainsAll ( "24128" ; "412" ) [= ContainsAll ( 24128 ; 412 )]; Result = 1
* ContainsAll ( "Jim,Charlie,Frank,Jane,Michelle,George,Kim,Chris" ; "Jim,Chris, Jane" ) = 0 (note space before Jane)
* ContainsAll ( "Jim,Charlie,Frank,Jane,Michelle,George,Kim,Chris" ; "Jim,Chris,Jane" ) = 1
* ContainsAll ( "CA¶CO¶DE¶IA¶MN¶ND¶SD" ; "CA¶DE¶MN" ) = 1
* ContainsAll ( "Jim,Jane" ; "m\,J" ) = 1
********************************/

Let ( [ 
Text = Substitute ( Text ; ¶ ; "!p!" ) ;
SearchStrings = Substitute ( 
		SearchStrings ; 
			["\," ; "!c!" ] ; 
			["\;" ; "!s!" ] ; 
			["\¶" ; "!p!" ] ; 
			[ "," ; ¶ ]; 
			[ ";" ; ¶ ] 
	) ;
ThisValue = Substitute ( 
					GetValue ( SearchStrings ; 1 ) ; 
			[ "!c!" ; "," ] ; 
			[ "!s!" ; ";" ] ) ;
interim = Case ( 
		ValueCount (SearchStrings ) > 1 ; /*IF*/
		If ( 
			Position ( /*THEN*/
				Text ; 
				ThisValue ; 
				1 ; 
				1 
				) ;
			1 ;
			0 
			)
			&
		ContainsAll ( /*note: if CF name is changed, change here too*/
				Text ; 
				RightValues ( 
					SearchStrings ; 
					ValueCount ( SearchStrings ) - 1 ) 
				) ;
			
		If ( 
			Position ( /*THEN*/
				Text ; 
				ThisValue ; 
				1 ; 
				1 
				) ;
			1 ;
			0 
			)
	) /*End Case*/
	] ;
  
If ( PatternCount ( interim ; "0" ) ; 0 ; 1 )
 )

Determine whether Text contains ALL of the strings contained in SearchStrings ('and' logic)
In SearchStrings strings should be separated by commas, semicolons, or ¶
To include commas, semicolons, ¶ (to be matched) in SearchStrings, include \ before them
Restriction: the following !-containing strings may not be part of SearchStings: !c! !s! !p!
Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
Written in response to request from John Vermeulen
Updated April 2017 with no functional change, just easier-to-understand formatting and comments
A shortcoming of this occurred to me: the entirety of Text is evaluated for each new SearchStrings value. Thus, ContainsAll ("Mike,Pete" ; "Pete¶ete¶e\,P") would evaluate as 1, even though it's finding the same text over and over. The same would happen with the SearchString being "Pete¶Pete".

Comments

		John Segers Jun 12, 2019
Thanks a lot, just the function I was looking for for so long but didn't manage to create on my own. And it works perfectly ;)

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ContainsAll ( Text ; SearchStrings )

Comments

Support this website.

Support this website.