ListLogic_Casesensitive ( type ; ListA ; ListB )

Casesensitive comparing lists of values with eachother using NOT, AND or XOR criteria

Be the first to rate this function Log in to vote

Menno van Beek - Show more from this author
Van Beek Zakelijke Software
https://www.vbzs.nl

Sample input:

ListLogic_Casesensitive ( "NOT" ; "AB¶CD¶EF" ; "cd¶EF¶gh" ) /*Values in ListA missing in ListB*/
ListLogic_Casesensitive ( "AND" ; "AB¶CD¶EF" ; "cd¶EF¶gh" ) /*Values both in ListA and in ListB*/
ListLogic_Casesensitive ( "XOR" ; "AB¶CD¶EF" ; "cd¶EF¶gh" ) /*Values exclusive in one or the other*/

Sample output:

AB
CD /* NOT: Values in ListA missing in ListB */

EF /* AND: Values both in ListA and in ListB */

AB
CD
cd
gh /* XOR: Values in only one of both lists */

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

/* 
################################################################## 
#  
#  ListLogic_Casesensitive function
#  Format:   
#  ListLogic_Casesensitive ( type ; ListA ; ListB )
# 
#  Parameters:
#  Type: "NOT","AND" or "XOR" 
#  ListA: List of textvalues
#  ListB: List of textvalues
# 
#  Data type returned:
#  text   (all true values will be returned, the function IS Case-Sensitive )
#  
#  Description:   
#  Compare 2 lists of values with each other with "NOT","AND" or "XOR" 
#  the result will show respectively:
#  NOT: Values in ListA that are not found ListB
#  AND: Values that are in both lists
#  XOR: Values in only one of both lists
# 
#  Author:
#  Menno van Beek
# 
#  20190522 	Initial function published: https://www.briandunning.com/cf/2246 
#  20230219 	Rewritten adding case-sensitivity as separate function
#  
#  ©2023 - Menno van Beek - Van Beek Zakelijke Software - the Netherlands
# 
################################################################## 
*/

Let ( [ 

   type = Left ( type ; 3 ) ; 
   result = Case ( type = "NOT" ; 
	Let ( [ 
	    listb = If ( IsEmpty ( listb ) ; "tempvalue" ; listb ) ; 
	    blist = UniqueValues ( listb & ¶ ; "" ; "Unicode_Raw" ) 
	] ; 
	    Substitute ( Case ( 
		IsEmpty ( lista ) ; "" ; 
		UniqueValues ( List ( blist ; lista ) ; "" ; "Unicode_Raw" ) ) ; 
	    [ blist ; "" ] ) 
	) ; 

   type = "AND" ; 
	While ( [ 
	    l = ListLogic_Casesensitive ( "NOT" ; lista ; listb ) ; 
	    r = ¶ & lista & ¶ ; 
	    i = 0 
	] ; 
	    i < ValueCount ( l ) ; 
	[ 
	    i = i + 1 ; 
	    v = GetValue ( l ; i ) ; 
	    r = Substitute ( r ; ¶ & v & ¶ ; ¶ ) 
	] ; 
	    r 
	) ; 

   type = "XOR" ; 
	Let ( [ 
	    nota = ListLogic_Casesensitive ( "NOT" ; lista ; listb ) ; 
	    notb = ListLogic_Casesensitive ( "NOT" ; listb ; lista ) 
	] ; 
	    Case ( 
		lista = "" ; UniqueValues ( listb ; "" ; "Unicode_Raw" ) ; 
		listb = "" ; UniqueValues ( lista ; "" ; "Unicode_Raw" ) ; 
		List ( nota ; notb ) )
	    ) ; 

	"ERROR: Incorrect function type. Choose from: NOT, AND & XOR" )

] ; 

    Substitute ( 
	TrimAll ( 
	   Substitute ( result ; 
		[ " " ; "@$space$@" ] ; 
		[ ¶ ; " " ] ) ; 
	1 ; 1 ) ; 
    [ " " ; ¶ ] ; 
    [ "@$space$@" ; " " ] ) 

)

Simple function to compare (value)lists with each other, considering the charactercases. This function is derived from https://www.briandunning.com/cf/2246, which is case-insensitive.

The function compares The second list to the first list in 3 possible ways:
1) NOT: Values in the first list missing in the second
2) AND: Values that are in both lists
3) XOR: Values that are not in both lists

This function is case-sensitive and each found true-value are returned.

Requirement: FileMaker 18+ because of the use of the While-function

The XOR- and the AND-types are recursive, NOT isn't.

Comments

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

ListLogic_Casesensitive ( type ; ListA ; ListB )

Comments

Support this website.

Support this website.