Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Filter for blank value on person column from Sharepoint list

(0) ShareShare
ReportReport
Posted on by 2

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!

  • Verified answer
    v-xida-msft Profile Picture
    v-xida-msft on at
    Re: Filter for blank value on person column from Sharepoint list

    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,

  • jeffreyropp Profile Picture
    jeffreyropp 2 on at
    Re: Filter for blank value on person column from Sharepoint list

    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 Profile Picture
    v-xida-msft on at
    Re: Filter for blank value on person column from Sharepoint list

    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,

  • CU-18081211-6 Profile Picture
    CU-18081211-6 9,261 on at
    Re: Filter for blank value on person column from Sharepoint list

    @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 Profile Picture
    v-xida-msft on at
    Re: Filter for blank value on person column from Sharepoint list

    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,

  • CU-18081211-6 Profile Picture
    CU-18081211-6 9,261 on at
    Re: Filter for blank value on person column from Sharepoint list

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard