ContainsAllExact ( Text ; SearchStrings )

Checks whether all values in SearchStrings are exactly (capitalization) found in Text

Average rating: 4.5 (2 votes) Log in to vote

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

Sample input:

ContainsAllExact ( "Jim Charlie Frank Jane Michelle George Kim Chris" ; "Jim,Chris,Jane" )
ContainsAllExact ( "CA¶CO¶DE¶IA¶MN¶ND¶SD" ; "CA¶DE¶Mn" )

Sample output:

1
0 (not exact)

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

/*******************************
* Bill Thurmes 2017
* Syntax: ContainsAllExact ( 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 all of the strings contained in SearchStrings ('and' logic),
*   and that those strings are exact matches (useful when ESQL queries depend on the function)
* Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
* Restriction: the following !-containing strings may not be part of SearchStings: !c! !s! !p! ~`_~`_~`_
*
* Examples: ContainsAllExact ( "24128" ; "412" ) [= ContainsAllExact ( 24128 ; 412 )]; Result = 1
* ContainsAllExact ( "Jim Charlie Frank Jane Michelle George Kim Chris" ; "Jim,Chris,  Jane" ) = 0 (note 2 spaces before Jane)
* ContainsAllExact ( "Jim,Charlie,Frank,Jane,Michelle,George,Kim,Chris" ; "Jim,Chris,Jane" ) = 1
* ContainsAllExact ( "CA¶CO¶DE¶IA¶MN¶ND¶SD" ; "CA¶DE¶Mn" ) = 0 (MN, Mn not exact match)
* ContainsAllExact ( "Jim,Jane" ; "m\,J" ) = 1
********************************/

Let ( [ 
Text = Substitute ( Text ; ¶ ; "!p!" ) ;
SearchStrings = Substitute ( 
		SearchStrings ; 
			["\," ; "!c!" ] ; 
			["\;" ; "!s!" ] ; 
			["\¶" ; "!p!" ] ; 
			[ "," ; ¶ ]; 
			[ ";" ; ¶ ] 
	) ;
interim = Case ( 
		ValueCount (SearchStrings ) > 1 ; /*IF*/
		/*THEN*/
		If ( /*used to convert PatternCount to 1 or 0*/
			PatternCount ( 	/*use this to count nonsense string*/
				Substitute ( /*use this to exactly match the value from SearchStrings...*/
							Text ; 
							Substitute ( 
								GetValue ( SearchStrings ; 1 ) ; 
								[ "!c!" ; "," ] ; 
								[ "!s!" ; ";" ] 
								) ;
							"~`_~`_~`_"		/*... and replace it with nonsense string*/
							) ;
				"~`_~`_~`_"
				) ;
			1 ;
			0 ) /*End If*/
			&
		ContainsAllExact ( /*note: if CF name is changed, change here too*/
				Text ; 
				RightValues ( 
					SearchStrings ; 
					ValueCount ( SearchStrings ) - 1 ) 
				) ;
			
		If ( /*used to convert PatternCount to 1 or 0*/
			PatternCount ( 	/*use this to count nonsense string*/
				Substitute ( /*use this to exactly match the value from SearchStrings...*/
							Text ; 
							Substitute ( 
								GetValue ( SearchStrings ; 1 ) ; 
								[ "!c!" ; "," ] ; 
								[ "!s!" ; ";" ] 
								) ;
							"~`_~`_~`_"		/*... and replace it with nonsense string*/
							) ;
				"~`_~`_~`_"
				) ;
			1 ;
			0 ) /*End If*/
	) /*End Case*/
	] ;
  
If ( PatternCount ( interim ; "0" ) ; 0 ; 1 ) //if no 0s were found, they must be all 1s
 )

Purpose: Determine whether Text contains all of the strings contained in SearchStrings ('and' logic), and that those strings are exact matches (useful when ESQL queries depend on the function)
Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
Restriction: the following !-containing strings may not be part of SearchStings: !c! !s! !p! ~`_~`_~`_
4/27/17 - A shortcoming of this occurred to me: this only checks the first incidence of the SearchStrings value in Text. Thus, ContainsExact ("Remark Mark"; "Mark") would evaluate as 0 because the first "mark" is not an exact match for the SearchString. I haven't yet come up with a fix for this. 4/27/17 - Solved it! Combo of Substitute and PatternCount.

Comments

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

ContainsAllExact ( Text ; SearchStrings )

Comments

Support this website.

Support this website.