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 - Show more from this author
Denbigh International Pty Ltd http://www.denbigh.com.au |
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 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, 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 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, 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, 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 Jun 22, 2010 |
|
| Thank you for the enhancement! | ||
 |
LingoJango 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! |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.