Can I check a LIST to see if anything in another LIST is in it.
Iโm checking in a volunteer rota to see if someone is already working at the times in the session they are being added to.
I have a LIST of times they are working in other sessions and a list of times in this session.
Iโve compared them by counting and seeing if they match, but that doesnโt work in a form, so doesnโt warn until synced.
Also I need to take 1 off one count as there is always a blank in one list, which is a pain.
If I can remove the blank I think I can remove the session times from the volunteer times and check if the LISTS match.
Iโd really like to remove the volunteer from the REF dropdown if they are already working at the times in the session, but this is frying my brain, so Iโm leaving that for next, but if thatโs actually easier please suggest something.
Thanks
This is what I need right now!!
@gpjoseph did you figure this out? Iโm just coming back to it now.
Try ISNOTBLANK(INTERSECT(list1, list2))
.
Iโve tried that, but it says both lists need to be the same type.
As far as i can see they are.
one list is a SELECT of the other list
SELECT(All Days[List Session Text Times 15 mins],[Volunteer]=[_THISROW].[Volunteer])
Do you know any reason it isnโt working?
INTERSECT() has some issues. This isnโt the first time Iโve seen this. Iโd hoped itโd been fixed.
Try this:
ISNOTBLANK(
INTERSECT(
(LIST() + list1),
(LIST() + list2)
)
)
In general, you can subtract lists to remove the items in one list from the others (if they exist), fo example:
If A = {1, 3, 5} and B = {2,3} then:
A - B = {1, 5}
Using this concept (and some math) you can get the interception of 2 lists by using A - (A - B). In the example above:
A - (A - B) = A - {1, 5} = {3}
So the expression ISBLANK(A - (A - B))
will let you know if the 2 lists have anything in common
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |