Brian Dunning's FileMaker Custom Functions

UniqueValues ( values )

Retains only unique values from list

  Average rating: 3.9 (59 votes) Log in to vote

Jeroen Aarts   Jeroen Aarts
ClickWorks
http://www.clickworks.be

Share on Facebook Share on Twitter

  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   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   Vaughan Bromfield
Mar 3, 2011
Thanks to both Jeroen and Erik.
 
Ted Hoagland   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   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   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é   André, Halle, Germany
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é   André, Halle, Germany
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   Doug Staubach, Denver, Colorado USA
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   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   Doug Staubach, Denver, Colorado USA
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   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   Jeroen Aarts, Antwerp, Belgium
Apr 2, 2015
Dale,

Good point, thanks! I changed the function definition accordingly.

- Jeroen
 
Dave   Dave, Birmingham, AL
Feb 23, 2016
Could you use a global variable $$list_of_values as the argument values?
 
Jeroen Aarts   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   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   Daniel, Rhode Island, USA
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   Jeroen Aarts, Antwerp, Belgium
Jun 15, 2018
Daniel, You are absolutely right. This function has become obsolete since FileMaker implemented it - finally :-) - itself.
 

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.