Why is Contains() working only for the first entry of a list?

I need help with using Contains() to compare two lists.

The problem is that Contains() only returns True if the “value” is the FIRST entry in the “search” list.

I have a doc that multiple users can access. I have a large table and I would like only specific users to access specific rows. To accomplish this, I am attempting to use a filter and a “visibility” column. The “visibility” column indicates which role(s) should be able to see each row.

Each user is assigned a specific role in the organization (e.g. President, Local Chapter Leader, Local Assistant, etc.). We have a Team table the indicates each user’s role(s). So I am trying to write a formula that checks if the list of role(s) in the “visibility” column contains any of the role(s) the current user has been assigned.

I’ve created a list of the current user’s roles using the code Team.Filter(Name=User()).[Role(s)].Split(",") which outputs: Local Chapter Leader, Local Assistant, LCL Coordinator, Project Assistant, Music Coordinator, Game Designer, Social Media Manager, Musician, Presenter, Researcher

The code list("Local Chapter Leader").contains([User Roles]) returns True, but the code list("Musician").contains([User Roles]) returns False even though both “Local Chapter Leader” and “Musician” are contained in the User Roles list.

Thanks ya’ll!

Hi @Jon_Dallas :blush:

Have you tried in “reversed” ? :blush:

[User Roles].contains("Musician")

[User Roles] being the value to search in and "Musician" the value to search for

Maybe In() could help …

2 Likes

Hi @Jon_Dallas , not in computer now but have a look on Find function, and check if the result is positive !

1 Like

Thanks @Pch! I’ll give this a try :slight_smile:

As far as I understand, Contains() should work the same in either order, because it checks if any value of list 1 matches any value of list 2. Am I wrong?

Well, I’m honestly not sure of what I’m going to say but I’ve never seen “the value to search for” and “value to search in” Contains() takes as inputs as being interchangeable … :thinking:

I tend to write all my Contains() as “in the list of values X, see if it contains this (list of) value(s) Y”…

So, for me, when you write

List("Musician").Contains([User Roles])

It means something like : Does the List("Musician") contains the list [User Roles] ?
And I’d answer no … as the List("Musician") only contains "Musician" :blush:

Whereas something like :

List("Musician").In([User Roles])

Would say : Is the List("Musician") actually in the list[User Roles] ?
I’d say yes :blush:

Same for :

[User Roles].Contains("Musician")

To me this says : Does the list [User Roles] contain the value “Musician” ?
Based on your screenshot I would also say yes :blush:

But that’s only my understanding of Contains() :blush: … And I could be perfectly wrong :innocent:

A small add-on @Jon_Dallas :

Why do you use Split() in your Filter() ? :blush:

Team.Filter(Name=User()).[Role(s)].Split(",")

This might be why Contains() might only work in one case but not in another one …

I don’t think Split(",") is necessary here as Team.Filter(Name=User()).[Role(s)] should already return the appropriate list …

HI Pch

My understanding is also that the two parameters are not reversable.

Socrates is a man, but not all men are Socrates.

P

1 Like

Wow, thanks everyone so much! This is my first time on the Coda community and the responses are much more than I anticipated. :pray:

Still no luck here. Any thoughts?

@Piet_Strydom and @Pch Thanks! But the screenshot you provided is for ContainsAll(), not Contains(). The description of Contains() is “Checks if a list contains any value from a list.” With that description, the value and search should be reversible, no?

listOfAllMen contains Socrates, but also the list “Socrates” contains at least one value from the listOfAllMen, so it should be true either way. Or am I thinking about this wrong?

And I am using split because the User Roles are in a single Select cell. I don’t know how Select data is stored in Coda, but I figured if it’s separated by commas, then a split would be helpful. But I’ve removed it. Thanks!

And I’m using Contains() rather than Find() or In() because I have multiple values in my search and multiple values in my value.

I have two lists and I am trying to build a formula that checks if ANY value from list 1 matches ANY value from list 2.

Thanks so much for your support!

1 Like

Oops.
But the logic still applies to contains () as well.

I am not at a computer at the moment.

But I think what will work is to use the foreach formula to cycle through one of the lists, and check for each of its members whether it exists in the second list.

Nie the second question becomes, “do you stop after you found the first match”? It do you want a list of trues, or the count of trues?

I know what the problem is. I will put my answer under a spoiler because I sent this thread to my students as an illustration of what I talked about in a recent lecture.

The answer

Look closely at both the User Roles and Visibility. Those are not lists – those are lists of lists as indicated by the icons in the reference chips as well as by the [] square brackets you can see in the formula popup.

You’re definitely forgetting a .First() somewhere after a filter. E.g. you’re selecting User Roles in a multi-select cell in a table but then you’re reading it from the whole column - e.g. Table.[User Roles] or such, and as a result you get an outer list (for all the cells in a column, even if just one) of an inner list of values (from a multi-select).

Then, of course, if you apply .Contains() on it, it tries to find the whole sublist in a list of sublists, not just one or any reference element in a list of reference elements.

Ideally you should fix your underlying formulas so that you always get what you expect to get – in this case a list of references.

A quick fix would be to either:

  • add .First() after both refs, e.g. [User Roles].First().Contains([Visibility].First()),
  • or, better, use .ListCombine() to flatten whatever structure to a list: [User Roles].ListCombine().Contains([Visibility].ListCombine())

More on the topic:
Here’s a riddle for the community

2 Likes