Jump to content

Comparing List


This topic is 1631 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

  • 1 month later...

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 B)

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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 ).

Link to comment
Share on other sites

This topic is 1631 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.