cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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

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:

powerappsbug-1.PNG

 

And here is a screenshot of the 501st record:

powerappsbug-2.PNG

 

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

powerappsbug-3.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

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

@rolycityaccount,

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

Microsoft Employee
@8bitclassroom
6 REPLIES 6
mr-dang
Level 10

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

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

Microsoft Employee
@8bitclassroom

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

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:

powerappsbug-5.PNG

 

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

powerappsbug-4.PNG

 

Both of these are indexed columns on the list.

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

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:

powerappsbug-6.PNG

mr-dang
Level 10

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

@rolycityaccount,

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

Microsoft Employee
@8bitclassroom

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

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 Smiley Sad

 

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
Not applicable

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

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

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

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

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 34 members 3,995 guests
Please welcome our newest community members: