How to LookUp Multi-Person SharePoint Column

(1) Share

Report

Posted on by CU23121026-0

Hello Everyone,

I can't figure out how could I fetch a multi-person column on a SharePoint, to record if the current user of the app is in the list or not. I tried multiple methods, but no working solution. This was one of the first:

If(
   !IsBlank(
      LookUp(
         'My_List',
         CurrentUserEmail in Owners.Email
      )
   ),
   Set(UserPresence,true),
   Set(UserPresence,false)
)

My_List is the SharePoint list and Owners is the multi-person column.

Also, there is the problem of the usual delegation warning. If that can be overcome that would be great, but at this point I would be happy if even the base functionality would work.

Thank you in advance if anyone has any recommendations!

Categories:

General Questions

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

AndrianaO 213 Moderator on at

Like (1)

Report

How to LookUp Multi-Person SharePoint Column

Hi! I got delegation warning as well, but you can try this:

If(

!IsBlank(

Filter(

'My_List',

CurrentUserEmail in Owners.Email

)

),

Set(

UserPresence,

true

),

Set(

UserPresence,

false

)

)

Let me know if it works!

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (1)

Report

How to LookUp Multi-Person SharePoint Column

upd: This will not help to overcome the Delegation warning, it will just mask it. Read later comment by @WarrenBelz for references.

To overcome Delegation warning statement I came up with the following Filter() function:

Filter(

AddColumns(

'My_List',

allOwnerEmails,

Concat(

Owner,

Email,

";"

)

),

User().Email in allOwnerEmails

)

You need to put this Filter() in my previous formula in place of Filter() which is already there. I am not sure if this is the best approach and maybe someone else will suggest something better.

In any case, me know if this works as well!

Was this reply helpful? Yes No
CU23121026-0 10 on at

Like (0)

Report

How to LookUp Multi-Person SharePoint Column

Hey Adriana,

Thank you for reply. The code looks good, but when I test it on my end, it returns True although my user is not in the column. There are different users in it but those should not match with my email.

That was the issue with my previous functions as well. Do I miss something here?

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (0)

Report

How to LookUp Multi-Person SharePoint Column

Oh, I see the problem. Try using IsEmpty() function in my formulas instead of IsBlank(), it should help.

Was this reply helpful? Yes No
WarrenBelz 151,635 Most Valuable Professional on at

Like (2)

Report

How to LookUp Multi-Person SharePoint Column

@CU23121026-0 and @AndrianaO

Just a note here - the structure "masks" the Delegation warning, but does not make it Delegable (utilising the full data set). AddColumns is a "local" function and the output is essentially a local Variable (so limited in record number to your first [Data Row Limit] records). These records can then be filtered using a non-Delegable filter as the process is carried out locally on the device.

There is however no Delegable workaround when filtering a multi-value SharePoint column unless you head down the Power Automate track.

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (1)

Report

How to LookUp Multi-Person SharePoint Column

@WarrenBelz thank you very much for the explanation!

Was this reply helpful? Yes No
CU23121026-0 10 on at

Like (0)

Report

How to LookUp Multi-Person SharePoint Column

Hey @AndrianaO and @WarrenBelz, thanks for the explanation too!

In the meantime I got to try it with IsEmpty instead of IsBlank. For me it is still bad, but now in the opposite way :D So now it always returns false, even if my user is actually in the column.
Did it work on your end?

Was this reply helpful? Yes No
CU23121026-0 10 on at

Like (0)

Report

How to LookUp Multi-Person SharePoint Column

Hey All,

In the meantime I figured out what caused the issue on my end. I ignored the fact that our UPN (User().Email is differs from the actual email. That's why the IsBlank / IsEmpty did not trigger any change to the variable.
Thank you once again for the suggested answers!

Was this reply helpful? Yes No