Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages

Shopping Cart
Shopping Cart


Free Newsletter


Privacy Policy

FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.


 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

Intersection Set ( ListA ; ListB )

Rate this function:  

  Average rating: 3.6  (49 votes)
  Discuss this Custom Function

Peter Gort, Denbigh International

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

Sample Input:
Intersection Set ( "1¶2¶3¶4" ; "3¶4¶5¶6" )
Sample Output:

 Then copy & paste into FileMaker Advanced'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
UniqueValues(list) by Ray Cologon
XORValues ( ListA ; ListB ) by Ray Cologon
NULL a custom function that returns nothing.

Any other variant of UniqueValues can be used instead, eg
UniqueValues( values ) by Jeroen Aarts,

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

This is my Custom Function and I want to edit it


5 most recent comments | Show all 7 comments

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.

Peter Gort, Gunnedah, NSW, Australia
April 21, 2010 12:10am


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 )


PatternCount ( "¶" & list1 & "¶" ; "¶" & t2 & "¶" ) ; t2 ; NULL )

John Buckingham, Maidstone, Kent, UK
June 22, 2010 5:19am

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!

John Buckingham, Maidstone, Kent, UK
June 22, 2010 5:24am

Thank you for the enhancement!

Peter Gort, Gunnedah, NSW, Australia
June 22, 2010 2:33pm

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.

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!

LingoJango, Barcelona
March 20, 2012 2:38am

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 4 + 6 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. HexUUIDToNum_cf (_uuid)
  (Sat, Jun 16, 1:55pm)
2. httpResponseCode ( responseHeaders )
  (Tue, Jun 12, 10:40pm)
3. FindDuplicateCharacters ( string )
  (Fri, Jun 08, 5:01pm)
4. FieldRepetitionLast ( field ; maxRepetition )
  (Wed, Jun 06, 6:05pm)
5. CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
  (Mon, May 28, 8:23am)
6. JSONArrayLength ( JSONArrayStr )
  (Fri, May 25, 7:46am)
7. interpolation (y1; y2; y3; x1; x2; x3; x1y1; x2y1; x1y2; x2y2; rnd)
  (Sat, May 19, 3:55am)
8. @JSONFormatElements ( json )
  (Tue, May 15, 3:31am)

RSS Feed of Custom Functions