Data Table stuck on "Loading Data"

[pitr]

PowerApp has multiple data tables that leverage the same source of data.

Data is stored in an Azure SQL Database.

The data is first filtered and then sorted.

Each table shows a different subset of the data.

Expectation

Users should be able to scroll through an entire list of results, in this case only about 700 rows.

Experience
When scrolling, we find that frequently (but not consistently) the data decides to not load.  I've experienced the same, even coming back several minutes later only to find that there's been no progress.

The bottom of the data table reads "Loading Data".

This does not happen at the same point each time.

Sometimes refreshing the data source helps so we've ended up adding a button, but ideally the users shouldn't notice the loading.

How can we improve the reliability and performance of the data table?

[pitr]

Same experience.

1. I created a blank PowerApp
2. Added three data tables
3. Pointed the data tables all to the same data source in our SQL Database
   (different than what the other app uses)
4. Scrolled each one until it stopped

They all stopped at the same point.

When I interacted with the first table (clicking on an item), it began loading results again.  I was then able to scroll further down, but it still stopped.

I then tried again, using SortByColumns to sort ascending on one of our integer columns so that I could easily identify how many had loaded.

I repeatedly scrolled on the first item until it hung, clicked on an item, and repeated.

• Hung @100 items
• Hung @200 items
• Hung @400 items (pausing briefly somewhere between)
• Hung @600 items (pausing briefly somewhere between)
• It's not continuing from there.

I tried the same with the remaining two tables and found that they pause and hang in different spots.
It's looking like it will hang after either 100 or 200 items.  I've yet to see it scroll past 200 without me having to take some action to loosen it up.

Where I'm at now:
Table1 is stuck showing "Loading data" after 600 items have been loaded.

Table2 was stuck showing "Loading data" after 600 items.  After several failed attempts to loosen it up, it is now stuck at the 600 item mark and is no longer showing "Loading data"
Table3 is stuck showing "Loading data" after 700 items have been loaded.

[pitr]

And update:
I triggered a refresh and it loaded the remaining items without issue.

Total item count: 947.

I also noticed that clicking refresh does eventually restore my scroll position in the data table, but that's after several seconds of everything jumping around.

[Mr-Dang-MSFT]

@pitr, it is by design that the data table only shows 100 at a time as you scroll to fetch more.

I have forwarded your session id to someone who can look at it, but if you know how to get a fiddler trace of the data tables as you scroll, and PM that to me, that will help.

[pitr]

Thank you for forwarding that along.

In the meantime the users of the app have been instructed on the workaround.

I'll see if I can get a Fiddler trace for you, but it might be little bit before I can get it reinstalled.

[ajyendra24]

@pitr ,

Did u find the exact solution for that. also get same issue the loading data stuck after some records. it would be great help for me if you provide solution. Thanks in advance.

Thanks

Ajyendra

[DeeTronSEAM]

I too am seeing this stuck on data loading issue for a datatable (inside of a fluid grid's data card) using a Papps v3.20091.41 canvas app.

There is only one datatable on the screen and it uses a collection populated on a prior screen's OnVisible for its Items.  That collection is populated without any filterer or sorting from an On-prem (via a gateway) SQL Server's view that returns 780 rows.  The app's setting for max records returned has been raised from the default 500 to 2000.

As would be expected, only when the user tries to scroll the datatable does the control attempt to display records not in its first "batch" ...though instead of the expected 100, it is the first fifty (50) that are displayed before the scrolling ends and the footer "Loading data" is diplayed...where it stays stuck until the app is exited.

I will try adding a second datacard with a gallery using the same collection for its items and see how its behavior compares.

Additionally, the whole reason we created this screen was to debug a problem where we were seeing duplicates and items missing from the displayed collection in a different screen's gallery of the collection.  I.e. the one that loads the collection from the SQL Server.  We were able to initially confirm that although the record count (i.e. CountRows) remains unchanged throughout, certain entries are duplicated and others are removed at some point.  Since the SQL View returns items with a unique primary key AND we apply a ROW_NUMBER function over the PK column, it's almost impossible for that duplication to be coming from the SQL Server side.  It seems to be some sort of corruption to the collection that we cannot track down.  Originally we were developing the app for offline capabilities (using device-sidecaching), but this collection corruption problem has caused us to abandon that as we've struggle to troubleshoot the problem of MIA/duplicated rows.  So now the app is online only and only use the initial loading of the collection from the view once.  Since there is no device-caching in the browser (and thus in PApps Studio) coupled with no Monitor on mobile, troubleshooting/debugging offline caching has been an absolute nightmare to us and the codebase.

[JaimeV]

Hi,

is there any solution for that problem? I am facing something similar.

When I scroll down through the list of records after 200/300 records are loading the app starts to fail. Is not possible to edit a record, "no item to display" after editing the record, and so on...

I have the data stored in Azure SQL database but I had the same problem in excel on onedrive. This is the reason why I moved the data to Azure.

Thanks

Jaime

[DeeTronSEAM]

Using the gallery (as described/proposed in my ‎2020-10-15 post above) we have had no problems with the "stuck" loading data like we saw in the datatable control.  For us, a gallery control was a viable alternative, so we switched.  But if you need the datatable's features, all I can say is "good luck."  

[Rabbanisyed]

I do have the same issue loading but in my case i am using excel to load the data in the Datatable after default 100 records  it does not load the other records. microsoft apps always have limitations. we can not recommand or design apps in powerapps very very limted controls and features. ihave near about 1000 records to load in my design i have a search option which does not work with limited 100 records even i have change the recods to 2000