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
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.
Hi @Jon_Dallas …
Have you tried in “reversed” ?
[User Roles] being the value to search in and
"Musician" the value to search for
In() could help …
Hi @Jon_Dallas , not in computer now but have a look on Find function, and check if the result is positive !
Thanks @Pch! I’ll give this a try
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 …
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
It means something like : Does the
List("Musician") contains the list
[User Roles] ?
And I’d answer no … as the
List("Musician") only contains
Whereas something like :
Would say : Is the
List("Musician") actually in the list
[User Roles] ?
I’d say yes
Same for :
To me this says : Does the list
[User Roles] contain the value “Musician” ?
Based on your screenshot I would also say yes
But that’s only my understanding of
Contains() … And I could be perfectly wrong
A small add-on @Jon_Dallas :
Why do you use
Split() in your
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 …
My understanding is also that the two parameters are not reversable.
Socrates is a man, but not all men are Socrates.
Wow, thanks everyone so much! This is my first time on the Coda community and the responses are much more than I anticipated.
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!
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.
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:
.First() after both refs, e.g.
- or, better, use
.ListCombine() to flatten whatever structure to a list:
More on the topic:
Here’s a riddle for the community