Bug? Filtering list is not able to get around 500 item limit

[Anonymous]

Hi all,

We have a list on SharePoint which we use to store additional user data which we cannot access through PowerApps' native Office365Users() connector (e.g. unique employee number). This list has approximately 600 items on it (one record for each user). Each item has unique permissions which only allows the user in question, and site admins to access each record.

In one of my apps, I am trying to access data for the currently logged in user by filtering based on their email address, matching the userProfile of the list record. I found that some records were OK, whilsts others were not pulled succesfully. Initially thought it may have been a SharePoint bug, so i re-indexed the list several times and still had the issue occuring.

After some tinkering, I figured out that PowerApps appears to get only the first 500 items overall, and then filter results based on that. Some evidence of this below.

Here is a screenshot of the 500th record in the list:

And here is a screenshot of the 501st record:

Here is what happens when I try to grab the above records using the "Employee Number" as a filter:

According to this documentation here: (https://powerapps.microsoft.com/en-us/blog/powerapps-now-supports-working-with-more-than-256-items-i...), we are able to work with large lists by filtering the data from it but this doesn't appear to be the case right now? Am I doing something wrong in filtering the data or is this a bug within PowerApps?

Any help appreciated!

Cheers

[mr-dang]

Hi, it looks like your formula includes CountIf which is an aggregate function like Sum(), Average(), CountRows(), etc. Currently PA isn't able to get those operations delegated on the other side. This means it can't return the information you're expecting, so it only works on the first 500 records. You should be seeing a blue dot above the object.

In a recent video from the blog, it was announced that PA now has access to aggregate functions for SharePoint, but it may not necessarily roll out right away: https://youtu.be/PuePMMuj5ps?t=39m29s

[Anonymous]

Hi @mr-dang, it seems the standard delegation (Filter) function is working strangely too though. That was how I found this problem initially.

If I filter by the employee number, it returns the correct result:

However if I filter by the email address of the user related to that employee number, the 501st user is not returned:

Both of these are indexed columns on the list.

[Anonymous]

Just tested something else, and I could probably figure out a way to use this as a workaround, but I'll leave this thread open as it seems something is wrong when access a Person field from SharePoint list with > 500 items.

I created a new fiell userEmailAddress (single line of text) and copied the email of the users in question into it. This is succesfully able to lookup the 501st user:

[mr-dang · Accepted solution]

@Anonymous,

Filter will be able to get the 501st record as long as the conditions in the filter are simple enough. 

I think of it this way:

• Avoid calculations on the fields of the datasource: Value(field)+1
• Validate data types within your datasource when possible
• Keep what you're comparing in the conditions as constant as possible

Here's some more specific reading:

Understanding Delegation

Delegable datasources

[Anonymous]

Thanks for the info @mr-dang, I had a read through the "understanding delegation" article a little more. Finally understand the little blue dot thing some more. So it looks like in this case, it is by design that People fields essentially cannot be filtered. I've figured out a workaround, but that's a bummer 😞

Would you know whether Microsoft have this in the pipeline at all? It seems strange that I can write javascript or C# that is able to natively query a Person field, but whatever is behind PowerApps is unable to.

[Anonymous]

I have a list of 5000 items. I am trying to apply filter on the Calculated column in the list. Filter is working for first 500 items. It is not workinh when i am trying to search record beyond 500. Even it is not working for other fields beyond 500 items. The filter query is mentioned nelow.

GroupBy(Filter(SourceList,txtInput1.Text in  SearchColumn &&  txtInput2.Text in SearchColumn),"Record_x0020_ID","RecordDetail") 

Please help