DeduplicateList ( input ; output )
Removes duplicates from a return-delimited list of values
Average rating: 4.4 (39 votes) Log in to vote
|
Peter Roots N/A N/A |
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, 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, 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. Data Sample: Consultingcn| Consultingcn| Australiacn| Herbivorecn| Australiacn| etc. |
||
|
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, 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, Monk Media 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.