cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sevket
Level: Powered On

Delegation Issues with Filter() Function

Hey guys,

 

I'm having a "problem" with delegation. I've already gone through the numerous threads on this issue but they didn't help me find a workaround/other solution.

 

I'm currenly building a few quite simple apps (to replace our old Infopath forms) for internal purposes like a vacation/leave request or expenses approval, ...

 

For example, I'm using the following formula to filter all the leave requests specific to the current user:

("Urlaubsanträge" is the sharepoint list which stores all leave reqeusts; UserDispName -> Set(UserDispNam; 'Office365-Benutzer'.MyProfile().DisplayName)) 

 

SortByColumns(Filter(Urlaubsanträge;Author.DisplayName=UserDispNam);"Created";Descending)

I'm pretty sure that we'll reach the 500 (or 2000) "allowed" dataset limit fast, so is there another way to do this?

 

Any help appreciated & thanks in advance!

 

Brgds,


Sahil

1 ACCEPTED SOLUTION

Accepted Solutions
JRaasumaa
Level 10

Re: Delegation Issues with Filter() Function


@dinusc wrote:

 

- Use another criteria that is fully delegable to preload the data in a collection (on applicatino start for example) and then use the filtering on that colleciton. Example: ClearCollect(col, Filter(Urlaubsanträge;Date>SomeDate)

and then

SortByColumns(Filter(col;Author.DisplayName=UserDispNam);"Created";Descending)

The trick here is to ensure the first formula does not attempt to pul lmore than 500 (2000) records. If it does then you would have to find a way to load data in batches.

 


 

This is how we've avoided collection record limits, if there's a chance the records will be larger than 2000 it's either ClearCollected based on a dropdown date field or even more filtering that just OnChange loads new data to the collection. It works quite well and I've yet to find a collection that needs  more than 2000 records loaded at once if filtered with proper delegable fields first.

 

 

I'd recommend this solution.

4 REPLIES 4
Highlighted
Microsoft Employee

Re: Delegation Issues with Filter() Function

Just to confirm, when you say that will be reaching the limit of 500 (2000), do you mean (1) the number of records in the dataset or the number of (2) records that you would like to pull from the dataset?

1. Possible solutions are:

- Add the AuthorName text column to the list so you can avoid delegation problem (AuthorName=UserDispNam should be fully delegable).

- Use another criteria that is fully delegable to preload the data in a collection (on applicatino start for example) and then use the filtering on that colleciton. Example: ClearCollect(col, Filter(Urlaubsanträge;Date>SomeDate)

and then

SortByColumns(Filter(col;Author.DisplayName=UserDispNam);"Created";Descending)

The trick here is to ensure the first formula does not attempt to pul lmore than 500 (2000) records. If it does then you would have to find a way to load data in batches.

 

2. The only solution would be to load data in batches. This would most likely require you to modify your SharePoint list to add some kind of batch ids.

Mike8
Level 10

Re: Delegation Issues with Filter() Function

Hello Sevket,

I would use flow to have the data in Powerapps if possible. If you have your data in a collection you bypass the delegation issues
Take a look at this on how to send a table from flow to powerapps.
https://powerapps.microsoft.com/nl-nl/blog/return-an-array-from-flow-to-powerapps-response-method/

JRaasumaa
Level 10

Re: Delegation Issues with Filter() Function


@dinusc wrote:

 

- Use another criteria that is fully delegable to preload the data in a collection (on applicatino start for example) and then use the filtering on that colleciton. Example: ClearCollect(col, Filter(Urlaubsanträge;Date>SomeDate)

and then

SortByColumns(Filter(col;Author.DisplayName=UserDispNam);"Created";Descending)

The trick here is to ensure the first formula does not attempt to pul lmore than 500 (2000) records. If it does then you would have to find a way to load data in batches.

 


 

This is how we've avoided collection record limits, if there's a chance the records will be larger than 2000 it's either ClearCollected based on a dropdown date field or even more filtering that just OnChange loads new data to the collection. It works quite well and I've yet to find a collection that needs  more than 2000 records loaded at once if filtered with proper delegable fields first.

 

 

I'd recommend this solution.

Super User
Super User

Re: Delegation Issues with Filter() Function

Hi @Sevket!

 

Don't use collections for large datasets! In fact wherever possible let the database do the work rather than the app - Especially if you are going to use mobile apps, you don't want it to be pulling through whole databases of info every time it refreshes over a slow mobile connection.

 

I can help you get around the Filter Delegation issue as I have done so in my own environment this week for multiple fields (one of the Apps currently filters and searches by 5 different delegable items).

 

The issue lies with referencing .DisplayName (or .Value or .ID or anything that is not just the column name),

 

What you need to do is create a static text column which the search can then delegate:

Create a new Text Column in your Sharepoint List and call it something like AuthorDisplayNameValue.

Refresh your data source,

Edit your Gallery to include this new field - set the visible property to false (it's only there so we can use it for filtering)

 

  • Use flow to take Author.Displayname from all your existing records and place it in the new Text Column

OR

  • If your database is not large yet, go through Quick Edit and Copy Paste the text values of this

 

Anywhere in the App where you have an Edit/New Form, add the AuthorDisplayNameValue field, set the Default value of the Textbox within the DataCard to:

Text(Author.DisplayName)

  • This will ensure that any new records you create fill this column by default
  • (I also tested using a Lookup field for ease-of-use, however these are not delegable)

 

Now that the column is filled with data, you can edit your Items property to:

SortByColumns(Filter(Urlaubsanträge; AuthorDisplayNameValue=UserDispNam);"Created";Descending)

 

This should now be delegable and you will not be affected by the item limit as the filtering will be done on the Sharepoint side before it reaches PowerApps Smiley Happy

 

ManCat




Don't forget to 'Mark as Solution' and 'Thumbs Up' those who deserve it!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 190 members 4,373 guests
Please welcome our newest community members: