Answered

Filter for blank value on person column from Sharepoint list

(0) Share

Report

Posted on by jeffreyropp

I'm trying to define a filter that will return rows from a sharepoint list where a column of type Person "AssignedTo" is either the current user or a blank value (unassigned).

Something like:

Filter([@Issuesx], 'Assigned To'.Email = varUser.Email Or 'Assigned To'.Email = "")

Thanks very much in advance for help!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

CU-18081211-6 9,270 Moderator on at

Like (2)

Report
Copy link

Link copied!

Hi @jeffreyropp ,

Use Blank() function when you work with Sharepoint lists:

Filter([@Issuesx], 'Assigned To'.Email = varUser.Email || 'Assigned To'.Email = Blank())

You can find references here, chapter "Power Apps delegable functions and operations for SharePoint" - point 3.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @jeffreyropp ，

Do you want to filter your SP List records against the "AssignedTo" column is current user or a blank value?

I have made a test on my side, please try the following formula:

Filter( [@Issuesx], 'Assigned To'.Email = varUser.Email || IsBlank('Assigned To') )

Note: I assume that you could only select single one value within the 'Assigned To' field.

If you could select multiple options within the "AssignedTo" column, please modify above formula as below:

Filter( [@Issuesx], varUser.Email in Concat('Assigned To', Email & ";") || IsEmpty('Assigned To') )

Please try above solution, then check if the issue is solved.

Regards,

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report
Copy link

Link copied!

@v-xida-msft - sorry for my intervention, but is IsBlank() a function delegable to Sharepoint or you'll receive a delegation warning ?

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @gabibalaban ,

Thanks for your feedback. Yeah, you are right. IsBlank() function is not delegable within Filter function, but it would be subject to how many records stored in customer's SP List.

If the amount of SP List records is not more than 2000, you could ignore the Delegation warning issue. If the SP list records is more than 2000, I would provide a another solution to the customer.

Regards,

Was this reply helpful? Yes No
jeffreyropp 2 on at

Like (0)

Report
Copy link

Link copied!

Thanks to you both @v-xida-msft and @gabibalaban for the feedback.

I will have more than 2000 rows so delegation is essential.

What might the syntax of the filter look like if the field allowed for multiple persons?

Also (outside my original note's cope I know), but has anyone figured out how to delete an existing person in Power Apps? Works fine in Sharepoint but doesn't using the standard form control (dropdown) in Power apps. I've read several threads but nothing that seemed to present a straightforward solution.

Thanks again for the help!

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @jeffreyropp ,

Have you tried the solution @gabibalaban provided above?

If the Person column is a single value Person type column, I think the solution @gabibalaban provided above could fix this delegation issue.

But if you enable multiple options for the Person type column in your SP List, I think the solution @gabibalaban provided above could not achieve your needs, you may face Delegation warning issue.

If your SP List records is more than 2000, as an fixed solution to get rid of this Delegation warning issue, I think the Collection could achieve your needs. Please check and see if the following video resource could achieve your needs:

https://www.youtube.com/watch?v=2M0zCyu__20

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-combobox-to-show-all-the-results-from-SP-list/m-p/612204#M193064

You could consider bulk-load your SP List records into a collection in your canvas app, then use the collection as data source in your canvas app. Then the Delegation warning issue would be disappeared.

If above solution is helpful for your original issue, please feel free to click "Accept as Solution" to close this thread.

For your new issue (delete an existing person in Power Apps), please consider open another new thread in our Community, then describe your issue there, then we could provide a proper solution there for you.

Regards,

Was this reply helpful? Yes No