cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sevket
Helper I
Helper I

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
Memorable Member
Memorable Member


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

View solution in original post

6 REPLIES 6
dinusc
Microsoft
Microsoft

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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member


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

iAm_ManCat
Super User
Super User

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 🙂

 

ManCat

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


florin10abc
New Member

Hello ,

how can I set up the helpdesk to stop receiving mail on all tickets in progress.

if a new ticket appears, send me the older ones as well.
do you think I can solve this situation

thank you in advance


 Updating Media

 

 

Hello @iAm_ManCat and thank you for your solution. Your reply should be the proper solution to the topic, not the one chosen. Boy, have I gone far and long to find a solution on a similar problem with delegation issues.

Do you have any advice on how to deal with the similar problem in CDS?

I am trying to pull off some increased inside a filter function, like:

 

Filter(Contacts,
Lower(emailaddress1)=Lower(Text([@ModelDrivenFormIntegration].Item.emailaddress1)),

//crosscheck on necessary combinations between mob.phone and telephoneX

Substitute(mobilephone," ","")=Substitute([@ModelDrivenFormIntegration].Item.mobilephone," ","")||
Substitute(mobilephone," ","")="str_prefix" & Substitute([@ModelDrivenFormIntegration].Item.mobilephone," ","")||
"str_prefix" & Substitute(mobilephone," ","")=Substitute([@ModelDrivenFormIntegration].Item.mobilephone," ","")||

Substitute(mobilephone," ","")=Substitute([@ModelDrivenFormIntegration].Item.telephone1," ","")||
Substitute(mobilephone," ","")="str_prefix" & Substitute([@ModelDrivenFormIntegration].Item.telephone1," ","")||
"str_prefix" & Substitute(mobilephone," ","")=Substitute([@ModelDrivenFormIntegration].Item.telephone1," ","")

 and so on, the list is filter criteria is long.

 

What I am trying to achieve is to be able to find possible duplicate contacts by filtering through very unnormalized  dataset (trying to remove the spaces and prefixes in phone numbers). And while Filter function can be delegated, Substitute, Lower and other text manipulation functions cannot.

I tried your solution on a small subset of data, but it only semiworks. App starts to be very slow when I try it on around 100k contacts.
Or should I just do several consecutive Filter calls and just put the resulting data in a table? The resulting data is maybe 10-20 items max.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,255)