Brian Dunning's FileMaker Custom Functions

RemoveDuplicates ( Text )

Removes duplicate values from a value list.

  Average rating: 3.8 (52 votes) Log in to vote

Andrew Persons   Andrew Persons
Excelisys
http://www.excelisys.com

Share on Facebook Share on Twitter

  Sample input:
One
One
One
one
Two
one
two
Two
One
Three
Two
One
Two
Three
  Sample output:
One
one
Two
two
Three

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

This function is case sensitive, but the values do not need to be alphabetized, and the duplicates do not need to be next to each other. It will leave the list in the order it received it, minus any duplicates.

 

Comments

Daniel A. Shockley   Daniel A. Shockley, New York, NY
Nov 16, 2010
Important to note: This script wipes out any "pipe" ( "|" )characters from your list. Side effects like that are important to know about.
 
Patrick Neame   Patrick Neame, Ironbridge
Aug 10, 2012
What in the RemoveDuplicates function and why can it not be found?
 
Joshua   Joshua, New Jersey
Jun 6, 2013
I like that it removes the duplicates in a vlaue list but where do i insert this code at to remove my duplicates from my value list?
Also, which fields get renamed from your script?
 
Daniel   Daniel, New Jersey
Sep 20, 2013
This is awesome. Works really well when creating lists from multiple fields. Thank you.
 
Christopher Bailey   Christopher Bailey, Boston, MA
Sep 29, 2014
Instead of using the | character in the function (which might very well be used in a list element), I use a random sequence of characters that nobody would ever type. to wit:

//==================================
Let ( [

TheValue = LeftValues ( Text ; 1 ) ;
TheCount = ValueCount ( Text ) ;
NewText = Replace ( Substitute ( "¶" & textoo & "¶" ; [ "¶" ; "¶%%%UUU%%%" ] ; [ "%%%UUU%%%" & TheValue ; "" ] ; [ "%%%UUU%%%" ; "" ] ) ; 1 ; 1 ; "" )

] ;

Case ( TheCount > 0 ;

TheValue & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) )

)

)

//
//
// In the unlikely event that any actual data contains %%%UUU%%%, you may need to tweak this function :)
//
//
//===================================
 
Jon   Jon, Grande Prairie
Feb 27, 2015
Why do you even need the pipe bar or any other string for substitution? It works great by just using carriage returns. unless im missing something...
 
Jon   Jon, Grande Prairie
Feb 27, 2015
Let ( [

TheValue = GetValue ( Text ; 1 ) ;
NewText = Replace ( Substitute ( ¶ & Text & ¶ ; ¶ & TheValue ; "" ) ; 1 ; 1 ; "" );
TheCount = ValueCount ( NewText )

] ;

TheValue & If( TheCount > 0 ; ¶ & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) )
/*Stop*/
)

)

//
//
// This Allows you to not worry about any character that might be removed through this Function
//
// PLUS the Stop section allows you to insert any suffix you prefer, I like my list to return with no carriage return at the end so this works great for me.
//
 
Jon   Jon, Grande Prairie
Feb 27, 2015
nvm, my function removes "the" from "theater".. I forgot that leftValues puts a carriage return at the end. But the order it evaluates is still usable for preventing a pesky carriage return at the end of your list
 
James   James, London
Feb 18, 2016
This is great, did exactly what I needed
 
franck   franck, Be
Jan 4, 2017
is it possible to remove duplicate plural ? for exemple "toy game toys brand"
so it wll done "toy game brand"

?
 
Andy Persons   Andy Persons, Sierra Vista, AZ
Jan 4, 2017
That's possible, but it would require some extra work. Also, certain plurals would be more problematic, such as jelly/jellies.
 
franck   franck, Be
Jan 4, 2017
could you help me to make it work ? bad luck for jelly )))

i m trying since few hours with this "If ( Right ( text ; 1 )="s" ; Left ( text ; Length ( text )-1 ) ; text )"
 
Andy Persons   Andy Persons, Sierra Vista, AZ
Jan 4, 2017
After giving it more thought, I'm not sure how well it would work in practice. You'd have to do multiple passes to remove plurals that were encountered before their singulars, among other issues.

Unfortunately, this will take more time than I can spend pro bono. Good luck!
 
Scott   Scott, UK
Jun 5, 2017
Could this be adjusted to remove duplicate strings of text as opposed to just a single word?
 
Aaron   Aaron, Wooster, OH, USA
Jun 8, 2017
Scott, I think it does that already. Did you test it? The parameter the function requires is a string; and it looks for identical strings.
 

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.