cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joel
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
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.

Joel
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

hpkeong 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”)
hpkeong 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
csm 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

pjamili
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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,652)