Software Venture Consulting

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

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


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:  

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

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

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:
"4¶3"


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

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

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

This is my Custom Function and I want to edit it

Discuss:

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

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, 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.

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!

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:
City/Location:
Comment:
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 3 + 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. ProperAllWords ( text )
  (Thu, Dec 14, 5:19pm)
2. MatchExist ( SourceTable.Field ; DestTable.Field ; ReturnField )
  (Thu, Dec 14, 1:07pm)
3. BVR_Format ( bvr )
  (Wed, Dec 13, 5:32pm)
4. NumberToHexadecimal(NumberValue)
  (Fri, Dec 08, 8:54am)
5. Get_BaseTable
  (Thu, Dec 07, 4:27pm)
6. JSONCreateVarsFromKeys ( json ; namespace )
  (Wed, Dec 06, 8:21pm)
7. GetTableNzme ( field )
  (Tue, Dec 05, 9:16pm)
8. TimeFormatAsText ( theTime )
  (Mon, Nov 13, 1:59pm)

RSS Feed of Custom Functions