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

DeduplicateList ( input ; output )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.5  (40 votes)
  Discuss this Custom Function

Peter Roots, N/A
N/A

Removes duplicates from a return-delimited list of values

Sample Input:
DeduplicateList ( "apple¶pear¶apple¶orange" ; "" )
Sample Output:
apple¶pear¶orange


 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:

Removes duplicates from a return-delimited list of values, such as would be provided by FileMaker's List function. Not case-sensitive; retains the first instance of each value.

- input is the initial list;
- output is initially empty

For example,
DeduplicateList ( "Apple¶pear¶apple¶orange" ; "" ) =
"Apple¶pear¶orange"

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 8 comments

One thing to be aware of: If there are any blank values in the list to be de-duplicated, the function returns a ?. You need to remove blanks before deduplicating the list.

Darren Terry, San Jose, CA
January 25, 2011 11:41am

This did not work on a list of 34,076 items. The custom function returned 7 values but FileMaker index shows 10 values.

Data Sample:

Consultingcn|
Consultingcn|
Australiacn|
Herbivorecn|
Austra liacn|

etc.

Jerry Friedman, Wash DC
May 29, 2012 5:07pm

/*
Bigger & badder ;)
Author: Tom Seidler, 2012 [based on Peter Roots CF: http://www.briandunning.com/cf/1109]
Method: tail end recursion and use of substitute means this can handle lists of (theoretically) infinite size with as many as 50k variants in the list.

An essential aspect: ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]
This allows the guaranteed removal of all occurences of _this, even if they are next to each other, in a list...

Also borrows Ray Cologon's Trim4 methodology to clear all empty values.
*/

Let (

[
_this = GetValue ( input ; 1 ) ;
_null = If ( Left ( input ; 1 ) = "¶" ; 1 ) ;
_valueList = If ( not _null ; Substitute ( "¶" & input & "¶" ; ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]; ["¶" & _this & "¶" ; ""] ; ["¶¶";"¶"] ) ; input ) ;
_l1r = If ( Left ( _valueList ; 1 ) = "¶" ; 1 ) ;
_r1r = If ( Right ( _valueList ; 1 ) = "¶" ; 1 )
] ;

Case ( Length ( _valueList ) ;
Value_Dedupe ( Middle ( _valueList ; 1+_l1r ; Length ( _valueList ) - ( _l1r + _r1r ) ) ; List (output ; _this ) ) ;
output )

)

Thomas Seidler, London
July 30, 2012 8:09am

It may or may not be obvious from Tom Seidler's comment that his 'Bigger & badder' version of this recursive function has the name/parameters 'Value_Dedupe ( input ; output )'. I have tested the performance of both custom functions, and the performance is about the same for input lists of up to 5,000 values. For input lists of around 9,000 values, the original custom function takes about twice as long to evaluate as the Seidler variant (17 seconds versus 8 seconds on an Intel Core i7 3.4GHz Win Pro 7 machine). For input lists with more than 10,000 values Peter Roots' version fails and either gives incorrect results or generates a runtime error, undoubtedly because of FileMaker's call stack limit of 10,000, which is avoided by the Seidler version, which uses tail recursion. I have tested the Seidler version for input lists of over 40,000 values and although the evaluation time increases significantly (on the order of minutes rather than seconds), the results appear to be correct.

Tim Owen, Western USA
May 16, 2013 8:24am

Trying to figure out how to modify this custom function so that it deletes both values when a duplicate is found. Any ideas??

Rob, NH
May 25, 2014 6:17pm

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 4 + 8 =
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. decFromHex ( hexValue )
  (Mon, Oct 16, 12:34pm)
2. MonthYearList ( startmonth ; startyear ; numbermonth ; short )
  (Mon, Oct 16, 3:38am)
3. ErrorDescription ( errorNumber )
  (Wed, Sep 27, 2:51am)
4. decodeEntities (text)
  (Wed, Sep 27, 1:22am)
5. HexidecimalToNumber ( HexidecimalValue ; counter )
  (Mon, Sep 25, 12:39pm)
6. FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1
  (Fri, Sep 15, 12:34pm)
7. ShannonEntropy ( text )
  (Thu, Sep 07, 5:59am)
8. LetterCount ( text ; summary )
  (Thu, Sep 07, 5:53am)

RSS Feed of Custom Functions