DrewVoge
11 months ago

# Matching part of a user-list to another user list

I have two list-user fields - List1 and List2

I want to compare these two fields in a formula-checkbox field and have the result be true if ONE OR MORE user(s) from list1 appears in list2.

Example 1:

List1 - user1,user2

List2 - user2,user3,user4

Result would be true because user2 exists in both list1 and list2

Example 2:

List1 - user1,user2

List2 - user1,user2,user3,user4

Result would be true because one or more (both) user1 and user2 exist in both lists

Example 3:

List1 - user1,user2

List2 - user3,user4,user5

Result would be false

Any help?

------------------------------
Drew Voge
------------------------------
• It's not ideal - but you'll essentially have to break out list1 and see if any of them exist in list 2 using a combo of functions. Best I can come up with quickly is:

var text list1 = ToText(UserListToEmails([user list 1]));

Includes([user list 2],ToUser(Part(\$list1,1,";"))) or

Includes([user list 2],ToUser(Part(\$list1,2,";"))) or

Includes([user list 2],ToUser(Part(\$list1,3,";"))) or

Includes([user list 2],ToUser(Part(\$list1,4,";"))) or

....

repeat until you hit what you imagine is the practical end of the number of users you might expect.

------------------------------
Chayce Duncan
------------------------------
• I fixed (I believe) one part where you need to wrap the individual user values in a touserlist() to compare to the other user list.  Being said, it works sometimes, but not always. Here's what i figured out so far:

If userlist1 has 4 users in it, and the formula tests 4 parts of list1, it works correctly.

If userlist has less than 4 users in it, and the formula tests 4 parts of list 1, it returns positive no matter what.

Basically, it only seems to work if the number of "tests / comparisons" you make is equal to the number of users in the userlist1.  Make sense?

This is what the formula currently looks like:

var text activeusers = ToText(UserListToEmails([User - Current - User and Covered]));

Includes([Users - Active For - Final],ToUserList(ToUser((Part(\$activeusers,1,";"))))) or
Includes([Users - Active For - Final],ToUserList(ToUser((Part(\$activeusers,2,";"))))) or
Includes([Users - Active For - Final],ToUserList(ToUser((Part(\$activeusers,3,";"))))) or
Includes([Users - Active For - Final],ToUserList(ToUser((Part(\$activeusers,4,";")))))

------------------------------
Drew Voge
------------------------------

• Kind of. You could try and swap out 'Includes' with contains() to avoid the userlist transformation - thats what I commonly would use instead of Include. I was going through seeing if there was a more elegant solution specific to two user lists before I responded and found that. As a secondary measure you could check first to see if there actually is a user in that position before checking for contains. Something like:

var text list = ToText(UserListToEmails([user list 1]));

if( trim(Part(\$list,1,";")) != "", Contains([user list 2],ToUser(Part(\$list,1,";"))), false) or

if( trim(Part(\$list,2,";")) != "", Contains([user list 2],ToUser(Part(\$list,2,";"))), false) or

if( trim(Part(\$list,3,";")) != "", Contains([user list 2],ToUser(Part(\$list,3,";"))), false) or

if( trim(Part(\$list,4,";")) != "", Contains([user list 2],ToUser(Part(\$list,4,";"))), false) or

if( trim(Part(\$list,5,";")) != "", Contains([user list 2],ToUser(Part(\$list,5,";"))), false) or

if( trim(Part(\$list,6,";")) != "", Contains([user list 2],ToUser(Part(\$list,6,";"))), false) or

if( trim(Part(\$list,7,";")) != "", Contains([user list 2],ToUser(Part(\$list,7,";"))), false) or

.....................

------------------------------
Chayce Duncan
------------------------------