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

number of records

I am using an excel file for a datasource.  Only the first 500 records are showing on my app.  Is this a coincidence or is there an upper limit of 500 records in a particular table?

7 REPLIES 7
Highlighted
Power Apps
Power Apps

Re: number of records

Yes, indeed this is a known issue with “creating an app from data” scenario.

You can work around it by eliminating the combined sort and filter experience in your app.

For example, to eliminate sort: please select the Gallery in the first screen.

itemsInGallery.jpgChange Items to solely sort. (For example, Filter(<dataSourceName>, TextSearchBox1.Text in Text(<SearchedFieldInDataSource>))

itemsInGallery2.jpg

 The gallery should now load beyond 500 items.

 To learn more about this limitation with build 2.0.410, please refer to our release notes.

Highlighted
Regular Visitor

Re: number of records

Thanks for the help.  I am enjoying PowerApps.  This is going to help a lot.

 

I changed it to this:

 Filter(EmployeeList, TextSearchBox1.Text in Text(EmployeeName))

 

It still only had 500 rows.  So I changed it to just:

EmployeeList

 

saved it, refreshed my datasource, tested it and it still only had 500 rows.  Any idea what I might be doing wrong?

 

Joel

Highlighted
Community Champion
Community Champion

Re: number of records

Hi

First 500 on Pre-Load is acceptable.

Therafter, you must save data from APPS and those saved data will be accepted at later stage.

 

I had already posted another discussion for PG to consider other alternative way.

 

Regards

hpkeong
Highlighted
Power Apps
Power Apps

Re: number of records

Laura is correct that nesting Sort and Filter operations is one of the problems.  However, it isin't the only issue.  The "in" operator is also not supported for delegation. 

 

Also, the data source itself may not support delegation.  The Excel connector does not.  The SQL and SharePoint connectors do, as well as many others. We need to do a better job explaining what each connector is capable of.

 

We know the 500 row limit is problematic.  It shouldn't be a major hurdle for long.  We are actively working to support "in", nesting Sort and Filter, and delegating more data opreations.  As soon as we have better support, we will let you know on the forum and in the release notes. 

 

Why the 500 row limit?  We support a wide variety of data sources, from Excel to SharePoint to SQL to Salesforce.  They all have uneven query capabilities.  PowerApps expression language, based on Excel, is fairly rich, especially compared to many query languages.  As a fallback, we support pulling all the data we need to complete an operation locally, so that we can apply our expression language and not be limited by the capabilities of the data source.  But, pulling all the data locally to filter and sort has significant bandwidth and performance implications if the number of rows is high, especially on mobile devices.  To keep PowerApps snappy we chose to limit how much data we would processin this way and to turn our attention to delegation as the answer for highly scalable apps.

 

Here are some more details that may be helpful, straight from the developer.  Again, these limitations will be only short term.

 

The first argument to Filter and Sort must be the name of a data source.  Hence, no nesting.

 

The sort expression must be the name of a single column.  For example: Sort(CDS, Value).

 

The filter expression can include these binary operators:

    • Equal (=)
    • NotEqual (<>)
    • Less (<)
    • LessEqual (<=)
    • Greater (>)
    • GreaterEqual (>=)
    • And (&&)
    • Or (||)

Operands must be constants or fields.  Here are some examples:

  • Filter(CDS, Value = “abc”)
  • Filter(CDS, Value = “abc” && Result)
  • Filter(CDS, Result && Value = “abc”)
  • Filter(CDS, Result = true && Value = “abc”)
  • Filter(CDS, Result = true, Value = “abc”)
Highlighted
Community Champion
Community Champion

Re: number of records

Hi GregLi:

 

Many thanks to your detail explanation and this help clarify many uncertain issues.

At least, we know that PG is working seriously to make it a success, though not now, but progressive.

 

Now, we don;t feel lonely like in the old forum; at least feeling like back to Project Siena with so many Microsoft staff helping us to solve problems, clarify issues and giev us, very important, CONFIDENT, that POWERAPPS will NEVER DIE.

 

Appreciate and with respect, while awaiting more improvement.

 

I keep making apps for Productivity and keep improving in parallel with PowerApps's improvement.

 

Cheers.

hpkeong
Highlighted
Helper I
Helper I

Re: number of records

Hi GregLi,

 

Could you please confirm that SharePoint does support delegation?

 

I have been building an app and testing using CDM, SharePoint list, and Excel as potential data sources. In my testing, only CDM seems to delegate filter operations to the server.

 

Thanks

Highlighted
New Member

Re: number of records

Thanks for the comment; but the thing is most datasets have more than 500 records. I think it's better to have a default (say 500) and let users change that based on their needs.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,352)