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.
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
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"
Whereas something like :
List("Musician").In([User Roles])
Would say : Is the List("Musician") actually in the list[User Roles] ?
I’d say yes
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
But that’s only my understanding of Contains() … And I could be perfectly wrong
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.
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())
…
