Brian Dunning's FileMaker Custom Functions

Intersection Set ( ListA ; ListB )

Outputs a list of the unique values present in both of the value lists supplied

  Average rating: 3.6 (49 votes) Log in to vote

Peter Gort   Peter Gort
Denbigh International Pty Ltd
http://www.denbigh.com.au

Share on Facebook Share on Twitter

  Sample input:
Intersection Set ( "1¶2¶3¶4" ; "3¶4¶5¶6" )
  Sample output:
"4¶3"

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

Outputs a list of the unique values present in both of the value lists supplied in the parameters

Dependencies :
StripEmptyValues( list ) by Peter Wagemans http://www.briandunning.com/cf/105
UniqueValues(list) by Ray Cologon http://www.NightWing.com.au/Filemaker
XORValues ( ListA ; ListB ) by Ray Cologon http://www.NightWing.com.au/Filemaker
NULL a custom function that returns nothing.

Any other variant of UniqueValues can be used instead, eg
UniqueValues( values ) by Jeroen Aarts, http://www.briandunning.com/cf/596

 

Comments

Rudi   Rudi, Toronto
Dec 1, 2009
Where does the dependency on XORValues get called? Intersection Set function seems to break handling lists like this:
ListA = 2¶5¶3¶7¶4¶8¶9¶10¶12¶15¶16¶20
ListB = 2¶5¶3¶7¶4¶33¶10¶12¶15¶16¶20
 
Yannick Van den Eijnden   Yannick Van den Eijnden, Antwerp
Apr 20, 2010
Hi,

I have a little question.

Why do you use NULL in your custom function when FileMaker says it does not recognise this parameter?

How can this easily be solved?
 
Peter Gort   Peter Gort, Denbigh International Pty Ltd
Apr 21, 2010
XORValues is a dependency for UniqueValues(valuelist). Calling UniqueValues(somelist) is equivalent to calling XORValues (somelist ; "").

Sorry for not documenting the NULL. I use a custom function called NULL that returns nothing. It comes in very useful, because custom functions are typecast to the destination field. For example if you assign "" to a date field, you get a ? in the date field, but if you assign NULL, it is typecast to an empty date value and the date field is correctly emptied.
 
John Buckingham   John Buckingham, microGuidance
Jun 22, 2010
PostScript!

I also found, because I was using it to detect common numbers in two lists, that I achieved a more accurate result by changing both PatternCount calcs from:

If ( PatternCount ( listA ; t2 ) ; t2 ; NULL )

to:

PatternCount ( "¶" & list1 & "¶" ; "¶" & t2 & "¶" ) ; t2 ; NULL )
 
John Buckingham   John Buckingham, microGuidance
Jun 22, 2010
I seem to have posted a postscript before the main post, which was:

Hi Peter,

Thank you for a great function.

I found it worked better when I used 'GetValue' rather than 'RightValues' in the function. So with these changes, it became:

t1 = GetValue ( listA ; nA ) ;
t2 = GetValue ( listB ; nB ) ;

Apologies if this has now been posted twice!
 
Peter Gort   Peter Gort, Denbigh International Pty Ltd
Jun 22, 2010
Thank you for the enhancement!
 
LingoJango   LingoJango, Barcelona
Mar 20, 2012
Hi Peter

I simplified this function: since it's an intersection, there is no need to compare listB against listA if you're already going through listA.

I also use the first value, not the last, and switched the remainder function to RightValues. This has the added advantage of preserving the sort order.

Let([
nA = ValueCount( listA ) ;
t1 = GetValue ( listA ; 1 ) ;
remainderA = RightValues ( listA ; nA - 1 ) ;
result = If ( PatternCount ( ¶ & listB & ¶ ; ¶ & t1 & ¶ ) ; t1 ; "" ) & "¶" & IntersectionSet ( remainderA ; listB )
];

Thanks for the function!
 

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.