Question

A canvas app displays a gallery bound to a Dataverse table named Orders that contains over 80,000 records. You configure the gallery's Items property to sort the orders by the OrderTotal column in descending order and show only orders where Status equals 'Open'. Testers report the gallery appears to only work with a small subset of the data, and a blue underline warning appears in the formula bar. You need to ensure all matching records are considered while keeping the query delegable to the server. Which approach should you use?

Accepted Answer

Use SortByColumns(Filter(Orders, Status = 'Status'.Open), "ordertotal", SortOrder.Descending) so both the filter and sort are delegated to Dataverse.

Answer

Wrap the expression in a ForAll loop over Orders and manually build a collection sorted in memory.

Answer

Increase the app's data row limit for non-delegable queries to 2,000 in the app settings and keep using Sort with a nested Filter that references a variable.

Answer

Load the entire Orders table into a collection with ClearCollect on OnStart, then apply Filter and Sort against the collection in the gallery.