Brian Dunning's FileMaker Custom Functions

RemoveCommonValues ( Array ; ComparisonArray )

Filter array by comparison array to remove the common values

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

Bob Kalus   Bob Kalus
KaluBo
bobearl.org

Share on Facebook Share on Twitter

  Sample input:
RemoveCommonValues ( 1¶2¶3¶4¶5¶6¶7¶;2¶4¶6¶8¶10¶12¶14¶ )
  Sample output:
1¶3¶5¶7

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

Function removes the values an array has in common with a second array. I found I needed this function to check that two fields used as a multikey for a relationship (first field in equijoin, second field in a non-equijoin) are mutually exclusive.

 

Comments

Erik Jutzi   Erik Jutzi, Germany
Mar 13, 2010
Hi Bob,
very good function, just searched for this functionality to subtract lists from each other to get an exclusive list.
I jut want to say, that I removed the "& ¶" after leftValues(Array;1) in the 5th line. This creates no empty lines in the result list

Cheers
Erik
 
Vincent Robinson   Vincent Robinson, San Francisco
Sep 7, 2012
Bob,

As Erik notes, very useful. To be precise, I notice it returns the left-hand complement. That is, if you run

RemoveCommonValues ("1¶2¶3"; "2¶3¶4")

the return value is "1" (not, "1¶4").

This is fine -- just means you have to run it twice, with the arrays in reverse order, to get the complete complementary set. Doing so, and concatenating with FilterValues on the same pair of arrays gives you the index of the combined arrays -- nice! Needless to say, this could itself be written as a custom function.

Vincent
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.