XORvalues ( ListA ; ListB )

Returns all lines that are different in two lists.

Average rating: 4.3 (40 votes) Log in to vote

Ray Cologon - Show more from this author
NightWing Enterprises
http://www.nightwing.com.au/FileMaker

Sample input:

XORvalues ( "Apple¶Pear¶Orange¶Banana"; "Lemon¶Apple¶Orange¶Grape" )

Sample output:

Pear
Banana
Lemon
Grape

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

If (
IsEmpty ( ListA & ListB ); "";

Let ( [ MasterList = ListA & Left (¶; Right(ListA; 1) ≠ ¶) & If ( not (FilterValues(ListB; ListA) = ListB ); ListB );
          Item = LeftValues ( MasterList; 1 ); 
          NewMaster = Substitute ( "·¶" & MasterList; [¶; "¶¶"]; [¶ & Item; ¶]; ["¶¶"; ¶]; ["·¶"; ""]; ["·"; ""]);
          CommonList = FilterValues ( ListB; ListA );
          CombinedList = If ( IsEmpty ( FilterValues ( Item; ListB )); Item) & XORvalues ( NewMaster; FilterValues ( CommonList; NewMaster ) );
          ProcessList = If ( Right ( CombinedList; 1) = ¶; Left ( CombinedList; Length ( CombinedList) - 1); CombinedList)]; 
If ( Left ( ProcessList; 1) = ¶; Right ( ProcessList; Length ( ProcessList) - 1); ProcessList )
      )
)

This function returns a single instance of all lines that are in ListA but not in ListB *and* of all lines that are in ListB but not in ListA. In other words lines that are in either field but *not* in both.

XORvalues has many applications in complex text processing, including but not limited to comparison of value lists (and lists of field values) in the synchronization of databases.

Note that a useful spin-off is that if one of the supplied parameters is null (""), the function will operate to return UNIQUE values from the other list - Ie it will eliminate duplicates. A single instance of each value will be returned, in the order of their first occurrence in the list.

The function as set out here is 'tuned' to return a combined list without a trailing carriage return. This behavior can readily be adjusted to line up with FileMaker's other 'values' functions if preferred.

Comments

		Katy Butterworth, New Zealand Oct 1, 2009
Is it possible to use this funtion and just have the items that are different in List B to List A returned? At the moment it returns differences between the two which I am using to see what has changed. I am trying to report changes but only want what it has changed "to", as it currently stands I am getting "changed to and changed from" which is useful in other reports but in this instance I only want to see what is different in list B when compared to List A. Any help appreciated.

		Bruce Robertson Jun 17, 2010
If A = (1,2,3) and B = (2,3,4); XOR( List( A; B); A)

		Daniel A. Shockley, New York, NY Nov 16, 2010
Note: This function will wipe out any of the "Â·" characters (ASCII 225) you might have in your value list. Unlikely, but important to note side effects like this.

		Alistair Hay, UK Jan 26, 2018
Excellent. Exactly what I needed. Thank you very much.

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

XORvalues ( ListA ; ListB )

Comments

Support this website.

Support this website.