Comparing List

[AnthonyDixon]

I’m am looking for a calculation or custom function that will compare two list to indicate if any value from ListA appears in ListB regardless of position in the lists. I have tried experimenting with PatternCount without success. The values must be an exact match.

[OlgerDiekstra]

I do something similar with MD5 hashes.

I read a list of MD5 hashes in one variable ($LIST1), and another list of MD5 hashes in another variable ($LIST2)

Then I loop through $LIST1 and do a patterncount match with $LIST2

Set Variable [$LIST1]
Set Variable [$LIST2]

Set  Variable [$COUNT; Value: 1]
Loop
  If [PatternCount( $LIST1; GetValue( $LIST2; $COUNT ) )]
     Do Something
     End If
  Set Variable [$COUNT; Value: $COUNT + 1]
  Exit Loop If [$COUNT > ValueCount( $LIST2 )]
  End Loop

 

[AnthonyDixon]

Thank you for your quick reply. It looks like I may have to go the script route after all. I was hoping for a custom function that might do the job. Thank you again for your solution.

[comment]

1 hour ago, AnthonyDixon said:

indicate if any value from ListA appears in ListB regardless of position in the lists.

The FilterValues() function does that: the expression =

not IsEmpty ( FilterValues ( ListB ; ListA ) )

will return 1 (True) when ListB contains any value from ListA, 0 (False) otherwise.

 

 

Edited August 21, 2019 by comment

[AnthonyDixon]

Thank You! Exactly what I was looking for.

[stanmcman]

How do you take this one step further? I want to compare two lists and show a list of values not being used yet...

Example:

List A = 1 2 3 4 5 (All Available Options)

List B = 2 3 5 (Selected Options)

I want List C = 1 4 (Options Not Yet Selected... virtually List A minus List

[Josh Ormond]

https://www.briandunning.com/cf/193

For that, I usually either script it, or use this Custom Function from Ray Cologon. Probably other ways to handle it, but I know this works.

[comment]

4 hours ago, stanmcman said:

I want to compare two lists and show a list of values not being used yet...

Unfortunately, this is not as simple. Over the years, a number of approaches have been proposed, with varying levels of efficiency in different scenarios. There was an interesting thread comparing the performances, but sadly it can no longer be found - see:
https://fmforums.com/topic/98609-missing-thread/

An interesting approach was suggested recently by Alex Zueiv on another forum, which I have simplified to:

Let ( [
combined = UniqueValues ( List ( UsedValues ; AllValues ) )
] ;
RightValues ( combined ; ValueCount ( combined ) - ValueCount ( UsedValues ) )
)

I haven't tested this against alternative solutions.

 

Edited October 8, 2019 by comment

[Josh Ormond]

On 10/8/2019 at 4:49 PM, comment said:

Unfortunately, this is not as simple. Over the years, a number of approaches have been proposed, with varying levels of efficiency in different scenarios. There was an interesting thread comparing the performances, but sadly it can no longer be found - see:
https://fmforums.com/topic/98609-missing-thread/

An interesting approach was suggested recently by Alex Zueiv on another forum, which I have simplified to:

Let ( [
combined = UniqueValues ( List ( UsedValues ; AllValues ) )
] ;
RightValues ( combined ; ValueCount ( combined ) - ValueCount ( UsedValues ) )
)

I haven't tested this against alternative solutions.

 

Looks like you also need to make sure the 2 lists are also unique. Duplicate values in either of the lists break the result.

[comment]

8 hours ago, Josh Ormond said:

you also need to make sure the 2 lists are also unique

You need to make sure the "blacklist" is unique, if you suspect it might contain duplicates (which is not the case here, nor in practically any scenario I can think of). Otherwise you'll be wasting time and electricity on redundant processing (so popular in the FM community...).

I should also add that if the "whitelist" is not unique, and you only wish to remove the "blacklisted" values from it and retain the remaining duplicates, then this method is not for you.

 

Edited October 10, 2019 by comment

[Josh Ormond]

9 hours ago, comment said:

You need to make sure the "blacklist" is unique, if you suspect it might contain duplicates (which is not the case here, nor in practically any scenario I can think of). Otherwise you'll be wasting time and electricity on redundant processing (so popular in the FM community...).

I should also add that if the "whitelist" is not unique, and you only wish to remove the "blacklisted" values from it and retain the remaining duplicates, then this method is not for you.

 

Honestly, I would be hard pressed to find a regular scenario where they would not be unique also. However, I've already run into a scenario where it ( read by someone else ) was used in a way that presented duplicates and/or extra returns.

I tend to try and guard against that, if it's possible and doesn't present other issues. In this case, running UniqueValues ( ) on both lists and removing any extraneous CRs is worth it to me. And doesn't carry a lot of extra weight.

And I completely agree with your last comment. The use-case for this is not retaining duplicates.

[comment]

I am a minimalist and I don't believe in piling up code just because someone reported problems using code they did not understand. 

This is especially true when answering questions here on the forum: I expect the reader to understand the answer, not to copy/paste it blindly into their solution. Therefore I concentrate on the problem at hand and leave additional potential issues to side notes, if at all.

Speaking of understanding: if you think that both lists must be unique, then you too don't understand how this works.

 

Edited October 11, 2019 by comment

[Josh Ormond]

Yeah, I get the approach. I've always appreciated your focus on the issue itself, and not the surrounding. It's partly why I didn't post the version I am using.

As for understanding, I do understand how it works. I also ran it through a bunch of unit tests and saw how it breaks. We work with some junior devs that sometimes need to just copy and paste code from other sources to make something work. I both walk them through how and why, but I also don't have the time to chase minor bugs that take up precious time later. That is the reason for the unit tests. I try to make it as simple as possible, but not any simpler.

My purpose of bringing it up was to prompt other readers to understand how it works. The default behavior, by most, would be just to throw 2 lists at it, and then complain it doesn't work. You and I, both, know that is the common behavior.

[comment]

Well, I think it would be sufficient to note that this solution assumes that the "blacklist" does not contain duplicate values. Then, if a reader that wishes to implement this deals with a scenario where this is not the case, they need to ask how to remove duplicates from their list before using it here. This is a completely separate problem, that has nothing to do with the question being discussed here.

This, BTW, is a major issue with so many custom functions published in the community: they come to solve one problem and end up struggling with assorted side issues that only obfuscate the solution. For example: if your function expects a number as its input, say so - and don't do GetAsNumber ( input ) "just in case".

--
P.S. It might also be worth noting that the above solution is based on the observation that UniqueValues() leaves the first value in place and removes any subsequent duplicates. And that since this behavior is undocumented, it could change in future versions without notice.

 

Edited October 10, 2019 by comment

[Josh Ormond]

I do agree with you about the duplicate values and discussions on the forums. For the most part, I handle all the other error checking and rule handling in scripts. It's easier to debug, and provide feedback that way.

The GetAsNumber ( input ) thing is a different discussion. But that's for another thread. FileMaker's datatype casting has some inconsistencies, especially when dealing with the JSON functions, and also some variable passing ( if you use dictionary functions ).