DeduplicateList ( input ; output )
Removes duplicates from a return-delimited list of values
Average rating: 4.4 (39 votes) Log in to vote
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
DeduplicateList ( "Apple¶pear¶apple¶orange" ; "" ) =
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, Bloomfield, CT
Feb 1, 2010
|I am wondering if this CF will dedup a return separated list of NUMBERS vs text?|
Stefan Schutt, Finland
Oct 10, 2010
|Yes it does. Just try it. :-)|
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, 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.
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.
_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 ) ) ;
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.|
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??|
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.