UniqueValues ( values )
Retains only unique values from list
Average rating: 3.9 (59 votes) Log in to vote
|
Jeroen Aarts - Show more from this author
ClickWorks http://www.clickworks.be |
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.
Comments
 |
Erik Wegweiser, Intelligent Database, LLC Jan 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 Bromfield Mar 3, 2011 |
|
| Thanks to both Jeroen and Erik. | ||
 |
Ted Hoagland, Santa Barbara, CA Aug 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, USA Jan 1, 2012 |
|
| Ted, check out the custom functions "SortArray" and "SortArray_Merge". Both are here on Brian's site. | ||
|
jonas, stockholm Feb 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é May 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é May 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 Feb 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, CA Oct 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 Oct 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, AR Apr 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, Belgium Apr 2, 2015 |
|
| Dale, Good point, thanks! I changed the function definition accordingly. - Jeroen |
||
|
Dave, Birmingham, AL Feb 23, 2016 |
|
| Could you use a global variable $$list_of_values as the argument values? | ||
|
Jeroen Aarts, Antwerp, Belgium Feb 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, USA May 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 |
||
 |
Daniel Jun 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, Belgium Jun 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.