Forum Discussion

DrewVoge's avatar
DrewVoge
Qrew Cadet
6 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
------------------------------

4 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    6 months ago

    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
    ------------------------------
    • DrewVoge's avatar
      DrewVoge
      Qrew Cadet
      6 months ago

      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
      ------------------------------

      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        6 months ago

        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
        ------------------------------