AnsweredAssumed Answered

UniqueValues Anomaly

Question asked by BruceRobertson on Mar 26, 2018
Latest reply on Mar 28, 2018 by siplus

Agnes Barouh recently published a very interesting custom function, ExcludeValues(MyList ; Out)

 

See FileMaker Custom Function:ExcludeValues(MyList ; Out)

 

It appears to depend on a subtle difference in how lists of values are compared in the function UniqueValues vs FilterValues.

To be a bit more specific, it depends on how the zero width space character is treated. This character can be generated with char( 8203 ).

 

Let's say you have two lists.

ListA: "Kevin¶Bruce"

ListB: "Kevin"

We wish to drop items in ListB from ListA.

To step through what the function does.

1. Append a trailing ¶ to ListB

2. Do a substitution in ListB: Substitute( ListB & ¶ ; ¶ ; char(8203) & ¶)

For purposes of discussion, I am going to make the zero width space visible by calling it ZWSP.

So what we have so far is "KevinZWSP¶"

3. Now concatenate ListB and ListA

"KevinZWSP¶Kevin¶Bruce"

4. Now get UniqueValues of this list.

"KevinZWSP¶Bruce"

5. Now use the FilterValues function to show only items in ListA that exist in this new version of ListB.

FilterValues( "Kevin¶Bruce"; "KevinZWSP¶Bruce" )

Result: "Bruce"

 

So can we consider this to be a reliable result in all environments?

Outcomes