cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
landonjochim
Resolver I
Resolver I

Filter Person Field that is Blank while avoiding Delegation

I have a Gallery in my PowerApp that is pulling back a filtered SharePoint List. Currently I am getting a delegation warning when I use a IsBlank(PersonField). So I am trying to work through this and the warning goes away if I do PersonField.DisplayName="", however this does not filter the list and remove the items that the PersonField is Blank/Empty.

 

SharePoint List name: PurchaseRequest_PurchaseRequestList

Results needed: 

'Submitted by' = Me

And either 

Status = "Pending"

or

'Assigned to' = [empty] AND Status = "Approved"

 

The 'Submitted by' field and 'Assigned to' fields are both Person/Group fields within the SharePoint List. 'Status' is a choice field.

 

Here is my formula that is returning the filtered data correctly, but is giving me delegation warnings:

Sort(Filter(PurchaseRequest_PurchaseRequestList, 'Submitted By'.Email=currentUserProfile.mail && (Status.Value="Pending" || ((IsBlank('Assigned To') && Status.Value="Approved")))), Submitted, Descending)

 

Here is my formula that is returning items that are 'Submitted by' = Me and 'Status' = Pending. However, it is not returning the ones where 'Assigned to' = [empty] && 'Status' = Approved. But I am not getting any delegation warnings with this formula.
Sort(Filter(PurchaseRequest_PurchaseRequestList, 'Submitted By'.Email=currentUserProfile.mail && (Status.Value="Pending" || (('Assigned To'.DisplayName="" && Status.Value="Approved")))), Submitted, Descending)

I am ultimately looking for a way to check if the 'Assigned to' field is empty/blank without causing delegation issues. and yes, my list is > 2000 and I need to be able to return those items so increasing the data row value is not an option.

Thanks!

landonjochim_1-1620254464468.png

 

 

landonjochim_0-1620254387952.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

@WarrenBelz - Thanks for the response. I ended up opting for using a collection on load of the screen. I performed all my delegable filtering in the clearcollect and then only did my non-delegable filtering on the collection. Works great. 

View solution in original post

3 REPLIES 3
WarrenBelz
Super User III
Super User III

Hi @landonjochim ,

Complex field types such as Person Fields are not Delegable - there is a way of getting rid of the warning, but this will simply work only on list sizes up to your Delegation limit.

I have a blog on Delegation that may assist with some workarounds.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

@WarrenBelz - Thanks for the response. I ended up opting for using a collection on load of the screen. I performed all my delegable filtering in the clearcollect and then only did my non-delegable filtering on the collection. Works great. 

View solution in original post

Thanks @landonjochim ,

That is the way I was referring to . . .

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,140)