DataTable sometimes only retrieves 100 items

[MarrinerDev]

We have a DataTable control in a Canva app connected to an Azure SQL datasource.  

[It gets data from a SQL View via a SortByColumns( Search ( Filter( ViewName, xxx))) construct which is built via nested Switch statements depending on a number of checkboxes which control which fields are used in the query.]

This usually all works fine. There are no delegation warnings and the DataTable control automatically pages to the next 100 items whenever the user scrolls down to the bottom of the list and there are more items to retrieve (currently a few thousand).

However, this paging mechanism is NOT reliable. When I play the app from within the editing studio, the paging seems to always work. i.e. when I scroll to bottom of the list, the next 100 records are always retrieved and the DataTable dynamically updates.

But when the app is run from the web player (i.e. the published app is run outside the editor), sometimes this paging mechanism occurs and sometimes it doesn't. i.e. it shows the first 100 records and then gets stuck, never showing any more even if the user scrolls to the bottom. Sometimes a mouse click on the last row of the DataTable forces a get of the next record but more often than not it doesn't.

Some questions:

* Is there a known bug with the DataTable control that causes this behaviour? Do I need a particular runtime version to avoid this? 

* Is there a setting somewhere which I need to alter? (Please don't mention the 500/2000 limit as this has nothing to do with it as the query is fully delegable and only 100 items are retrieved anyway).

* Are there particular query constructs which the SQL connector doesn't parse reliably?

* Are there things to look for in Monitor which might give clue as to what is failing?

Thanks in advance.

[poweractivate]

@MarrinerDev 

1a. If the delegable limit is set to 100, it is still theoretically possible to get this issue. It is also possible to get the issue even without a warning.

1b. If the functions Set, Collect, ClearCollect are used anywhere against a data source directly, there will be no warning, but the limit will apply anyway if the data source contained more records than the current limit when the function was called. These functions might only work as expected when each time they are called against a data source that is not another Collection, the batch contains less than the delegable limit.

I think in your case, however, it's probably not the delegable limit.

If the pagination is working in some cases and not others, it depends how exactly you have the pagination implemented, maybe there is some issue with it. 

How exactly do you have the pagination implemented in your case?

[MarrinerDev]

Thanks for your reply @poweractivate . Because they have so many limitations and we are generally using large data sets via SQL Server, we rarely use Collections (Collect, ClearCollect etc ) in our apps. Microsoft likes to wheel out these functions in their samples but in apps developed by grown-ups, they aren't used much (except perhaps for subsidiary data, populating option drop-downs etc). All data shaping is done server-side via SQL Views, not client-side in stack-busting Javascript arrays (i.e. Collections).

Anyways, the paging is handled directly by the DataTable widget and we don't have any direct control over it. The main point of making this post was to see if anyone had experienced the issue themselves and had received any advice from Microsoft about it.

[Drrickryp]

@MarrinerDev 

I'm curious if you get the same problem if you use a gallery with horizontal labels. With a row of labels across the top it looks exactly like a datatable but is more flexible.