compareLists ( listA ; listB ; function )

This recursive custom function compares two lists (AND, OR, NOT, XNOT)

Average rating: 5.0 (1 vote) Log in to vote

Kaai Hanhart - Show more from this author
goFlowApps
https://www.goflowapps.nl

Sample input:

compareLists ( "A¶A¶B¶C¶¶";"B¶D¶¶D¶"; "or" )

Sample output:

A
B
C
D

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

/*
This recursive custom function compares two lists. 

Allowed values of function are
AND 	item in both lists
OR 	item in list A or in list B
not	item is in list A but not in list B
Xnot	item is in list B but not in A;

Written by Kaai Hanhart, goFlowApps, November 5th 2020
Corrected November 12th 2020: In case of NOT or XNOT in the first cycle an closing enter is needed

*/

Let([
temp=listA; 
function=Case(function≠"";function;"AND");	// if no function is given we assume AND
$cf_cycle=$cf_cycle+1; 					// we count the cycles (the odd name is meant to prevent disturbance other variables)

listA=Case( 
$cf_cycle=1 and function="AND"; listA&¶; 		// just add enter
$cf_cycle=1 and function="OR"; listA&¶&listB&¶; 	// if OR we add both list in the first cycle
$cf_cycle=1 and function="NOT"; listA&¶; 		// just add enter
$cf_cycle=1 and function="XNOT"; listB&¶;		// if XNOT we switch the lists in the first cycle
listA										// in next cycle listA stays listA
);

listB=Case(

$cf_cycle=1 and function="AND"; listB&¶; 		// just add enter
$cf_cycle=1 and function="OR";"";  				// first cycle OR start with the first result
$cf_cycle=1 and function="NOT"; listB&¶; 		// just add enter
$cf_cycle=1 and function="XNOT"; temp&¶; 		// if XNOT we switch the lists in the first cycle
listB  									// in next cycle list B stays list B
);

// we work through the items of listA…
value= GetValue(listA;1);
exist= FilterValues(listB;value)≠"";
rest=MiddleValues(listA;2;ValueCount(listA));

// decide what is the result of this cycle
add= Case( 
value="";"";
function="AND" and exist;value&¶; 
function="OR" and not exist;value&¶; 
function="NOT" and not exist;value&¶;
function="XNOT" and not exist;value&¶
);

listB=Case(
function="AND";listB;					// if AND listB stays listB
listB &add								// if OR, not and XNOT listB grows with the new result to prevent doubles.
);

$cf_cycle=Case(rest≠"";$cf_cycle;"")  		// we clear the variable after last item

];

add & Case(rest≠""  ;compareLists(rest;listB;function))

)

This recursive custom function compares listA and target listB, which values overlap (AND), which values they have in common (OR), which values are only in list A (NOT) or which values are exclusively in listB (XNOT).
It cleans doubled values in both list and it neglects empty values. The result ends with an enter ¶.

Allowed values of function are
AND item in both lists
OR item in list A or in list B
NOT item is in list A but not in list B
XNOT item is in list B but not in listA;

Comments

		Tom G Jan 29, 2024
I understand this is a function you developed years ago, but wondering if you have any ideas about an "XAND" function. One that lists items that are neither in listA nor listB. Thanks

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

compareLists ( listA ; listB ; function )

Comments

Support this website.

Support this website.