Brian Dunning's FileMaker Custom Functions

DeduplicateList ( input ; output )

Removes duplicates from a return-delimited list of values

  Average rating: 4.4 (39 votes) Log in to vote

Peter Roots   Peter Roots
N/A
N/A

Share on Facebook Share on Twitter

  Sample input:
DeduplicateList ( "apple¶pear¶apple¶orange" ; "" )
  Sample output:
apple¶pear¶orange

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

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"

 

Comments

unin   unin, japan
Jan 21, 2010
How about case sensitive version ?

RemoveDuplicate( str )

Case ( ValueCount ( str ) < 2 ; str ; Let ( v1 = GetValue ( str ; 1 ) ; List ( v1 ; RemoveDuplicate ( Let ( s = Substitute ( ¶ & str & ¶ ; [ ¶ & v1 & ¶ ; ¶ ] ; [ ¶ & v1 & ¶ ; ¶ ] ) ; Middle ( s ; 2 ; Length ( s ) - 2 ) ) ) ) ) )
 
David Owen   David Owen, Bloomfield, CT
Feb 1, 2010
I am wondering if this CF will dedup a return separated list of NUMBERS vs text?
 
Stefan Schutt   Stefan Schutt, Finland
Oct 10, 2010
Yes it does. Just try it. :-)
 
Darren Terry   Darren Terry, Pacific Data Management, Inc.
Jan 25, 2011
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.
 
Jerry Friedman   Jerry Friedman, Wash DC
May 29, 2012
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|
Australiacn|

etc.
 
Thomas Seidler   Thomas Seidler, London
Jul 30, 2012
/*
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 )

)
 
Tim Owen   Tim Owen, Western USA
May 16, 2013
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.
 
Rob   Rob, NH
May 25, 2014
Trying to figure out how to modify this custom function so that it deletes both values when a duplicate is found. Any ideas??
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.