Filter for blank value on person column from Sharepoint list

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

[gabibalaban]

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.

[v-xida-msft]

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,

[gabibalaban]

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

[v-xida-msft]

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,

[jeffreyropp]

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!

[v-xida-msft · Accepted solution]

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

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,