# Intersection Set ( ListA ; ListB )

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

 Peter Gort - Show more from this author Denbigh International Pty Ltd http://www.denbigh.com.au

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

 Rudi, TorontoDec 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, AntwerpApr 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, Denbigh International Pty LtdApr 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, microGuidanceJun 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, microGuidanceJun 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, Denbigh International Pty LtdJun 22, 2010 Thank you for the enhancement!
 LingoJangoMar 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!

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

# Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: