# UniqueValues ( values )

Retains only unique values from list

Sample input:
UniqueValues( "a¶b¶b¶c" )
Sample output:
"a¶b¶c"

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

Takes a return-seperated list as parameter and outputs a list of the unique values from this list. This function can be used in conjunction with the FileMaker List() function to mimick the good old value list items behavior, without creating a ValueList.

 Erik Wegweiser, Intelligent Database, LLCJan 9, 2011 With thanks to Jeroen, whose elegant, single-parameter function I have used frequently, here is my modification of this formula, producing the same results without an extra return character appended. Let([ startlist = values; line1 = GetValue( startlist; 1 ); newlist = RightValues( startlist; ValueCount( startlist ) - 1) ]; Case( IsEmpty( startlist ); ""; ValueCount( FilterValues( startlist; line1 )) > 1; UniqueValues( newlist ); List( line1; UniqueValues( newlist ) ) ) )
 Vaughan BromfieldMar 3, 2011 Thanks to both Jeroen and Erik.
 Ted Hoagland, Santa Barbara, CAAug 4, 2011 Nice function. However, I would like the function to always return the values in the same order that they appear in the original list. I am using UniqueValues combined with List, and the unique values are returning in a different order than the values produced by List alone. For example, List (relatedField) = a¶b¶a. UniqueValues ( List(relatedField) ) = b¶a. Any suggestions? Thank you.
 Joan Martensen, Washington, USAJan 1, 2012 Ted, check out the custom functions "SortArray" and "SortArray_Merge". Both are here on Brian's site.
 jonas, stockholmFeb 7, 2012 an smaller alteration to remove the final ¶ is changing: v & "¶" to v & If(newL;"¶") Let([ l = values; v= GetValue(l; 1); newL = RightValues( l ; ValueCount( l ) - 1) ] ; Case( l = "" ;"" ; If( ValueCount(FilterValues(l; v)) > 1 ; "" ; v & If(newL;"¶") ) & uniqueValues( newL ) ) )
 André, Halle, GermanyMay 21, 2012 Unfortunately the function doesn't work. I want to list the values of a field in a related table. But instead of listing all values that are unique just the first value of the relation is displayed. Any solutions?
 André, Halle, GermanyMay 21, 2012 My fault. I forgot to use the List function within the brackets. But it would be nice to modify the function in a way that this step is included.
 Doug Staubach, Denver, Colorado USAFeb 6, 2014 Hello: If you modify the code slightly, it will return the values in the same order/sequence. Like so: //cleans up a value list, leaving only unique values //created Jan 30, 2014 by Doug Staubach //inspired by https://www.briandunning.com/cf/596 //modified to preserve the original order of values and to drop empty values Let([ OLDLIST = ValueList; THISVALUE = GetValue( OLDLIST ; ValueCount( OLDLIST ) ); NEWLIST = LeftValues( OLDLIST ; ValueCount( OLDLIST ) - 1) ] ; Case( OLDLIST = "" ;"" ; _UniqueValues( NEWLIST ) & If( ValueCount ( FilterValues ( OLDLIST ; THISVALUE ) ) > 1 ; "" ; If ( THISVALUE="" ; "" ; THISVALUE & "¶") ) // Note recursion here ) )
 Jason Witherspoon, Fairfax, CAOct 10, 2014 Great functions! I do notice, however, that both of them (Jeroen's original & Doug's variation) return an extra "¶ " at the end. So if I'm concatenating the returned unique list into a single field using a Substitute command, I'll get an extra "; " at the end. My solution is far from graceful, but works: Left ( Substitute ( UniqueValuesSorted (List (TABLE::Name)) ; ¶ ; "; ") ; Length ( Substitute ( UniqueValuesSorted (List (TABLE::Name)) ; ¶ ; "; ") ) - 2) ...but if I understood Custom Functions better, I'd try to fix this at the source. Any thoughts?
 Doug Staubach, Denver, Colorado USAOct 20, 2014 Hi Jason: The idea behind NOT removing the trailing return is to make sure this function behaves similar the built-in FileMaker functions. After I process a list through a custom list function (like this one), I use a different custom function to remove the trailing returns. - There any many different variations, but I use one similar to this one: => http://www.briandunning.com/cf/1528 You can always "nest" the two functions, like so: Trim_CR ( UniqueValues ( text ) )
 Dale Miller, Little Rock, ARApr 1, 2015 Thank you for the function. However, I notice that if I have multiple (or any) null values e.g. "a¶¶b¶¶b¶c" the return is "a¶¶b¶¶¶c¶" rather than the expected "a¶b¶c¶". The following change will deal nicely with this case: Let([ l = values; v= GetValue(l; 1); newL = RightValues( l ; ValueCount( l ) - 1) ] ; Case( l = "" ;"" ; v = ""; UniqueValues( newL ); // Eliminate null values here If( ValueCount(FilterValues(l; v)) > 1 ; "" ; v & "¶" ) & UniqueValues( newL ) ) )
 Jeroen Aarts, Antwerp, BelgiumApr 2, 2015 Dale, Good point, thanks! I changed the function definition accordingly. - Jeroen
 Dave, Birmingham, ALFeb 23, 2016 Could you use a global variable \$\$list_of_values as the argument values?
 Jeroen Aarts, Antwerp, BelgiumFeb 24, 2016 Dave, yes you can! Just pass your global variable as a parameter to the function, as in: Set Field ["NewList" ; "UniqueValues ( \$\$list_of_values) "]
 Rich, Lewiston, ID, USAMay 17, 2017 GREAT function. Thanks! I was looking for an alternative to producing this kind of output without using a portal. Quick question: Is there a way to filter a value using this CF? For example, say your (finished) list is: alpha bravo charlie ...and you want to omit 'bravo' from the list, is there a calc I can wrap around this CF to do that? Cheers, Rich
 DanielJun 15, 2018 Beware of using this function if you are running FileMaker 16 or greater, on server or client. FM 16 introduced an identically named function, and this causes conflict. Please rename this CF!
 Jeroen Aarts, Antwerp, BelgiumJun 15, 2018 Daniel, You are absolutely right. This function has become obsolete since FileMaker implemented it - finally :-) - itself.

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

# Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 16 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: