Delegation Issues with Filter() Function

[Sevket]

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

[dinusc]

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]

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 · Accepted solution]

---

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

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

[florin10abc]

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

[not_a_thing]

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.