Power Platform Community Forum Thread Details

Hi Members, I'm working on a Power Apps app connected to a SharePoint list called Dev Payroll Tracker which contains over 1 lakh records. In the Power Apps dashboard:

I have a Gallery connected to this list.
It uses pagination (Next & Previous buttons).
It has filters:
- Buttons: "My Records"," Pending", "Completed", "All"
- Search bar (TextInput)
- Region (Radio control)

Expected Behavior: The gallery should:

Show newest records first (sorted by Created)
Filter by button values [My Records, Pending, Completed, All], region, and search
Load 100 records per page
Work reliably even with over 10,000 matching records

The Issue: The gallery is not showing the records after 2000, data is missing in the gallery.

Formulas, I used in the app is:

App.OnStart property:

Set(varPageNumber, 1);
Set(varPageSize, 100);
Set(varGalleryFilterMode, "All");

Now, in the Dashboard Screen there is four Buttons:

Completed Button:

Set(varGalleryFilterMode, "Completed");
Set(varCurrentPage, 1);

Pending Button:

Set(varGalleryFilterMode, "Pending");
Set(varCurrentPage, 1);

My Records:

Set(varGalleryFilterMode, "My");
Set(varCurrentPage, 1);

All Records:

Set(varGalleryFilterMode, "All");
Set(varCurrentPage, 1);

Pagination Next Button:

If(
    CountRows(
        Filter(
            'Dev Payroll Tracker',
            Switch(
                varGalleryFilterMode,
                "Completed", Status.Value = "Completed",
                "Pending", Status.Value = "Pending",
                "My", 'Created By'.Email = User().Email,
                true
            ),
            IsBlank(Radio1.Selected.Value) || Region.Value = Radio1.Selected.Value
        )
    ) > varPageNumber * varPageSize,
    Set(varPageNumber, varPageNumber + 1)
)

Pagination Previous Button:

If(varPageNumber > 1, Set(varPageNumber, varPageNumber - 1))

Pagination Label text:

"Page " & varPageNumber & " of " &
RoundUp(
    CountRows(
        Filter(
            'Dev Payroll Tracker',
            Switch(
                varGalleryFilterMode,
                "Completed", Status.Value = "Completed",
                "Pending", Status.Value = "Pending",
                "My", 'Created By'.Email = User().Email,
                true
            ),
            IsBlank(Radio1.Selected.Value) || Region.Value = Radio1.Selected.Value
        )
    ) / varPageSize,
    0
)

Gallery1.Items property:

With(
    {
        filteredData:
            Sort(
                SortByColumns(
                    Filter(
                        'Dev Payroll Tracker',
                        // 🔹 Status
                        Switch(
                            varGalleryFilterMode,
                            "Completed", Status.Value = "Completed",
                            "Pending", Status.Value = "Pending",
                            "My", 'Created By'.Email = User().Email,
                            "All",true,
                            true
                        ),
                        // 🔹 Region
                        IsBlank(Radio1.Selected.Value) || Region.Value = Radio1.Selected.Value,
                        // 🔹 Search
                        IsBlank(TextInput1.Text) ||
                            StartsWith('Processor Name'.DisplayName, TextInput1.Text) ||
                            StartsWith(Month.Value, TextInput1.Text) ||
                            StartsWith(ProcessName.Value, TextInput1.Text) ||
                            StartsWith(Region.Value, TextInput1.Text) ||
                            StartsWith('Request Type/Subtask', TextInput1.Text) ||
                            StartsWith('Request ID / Emp ID', TextInput1.Text)||
                            StartsWith('Requestor Email',TextInput1.Text)||
                            EndsWith('Processor Name'.DisplayName, TextInput1.Text)
                    ),
                    "Created", SortOrder.Descending
                ),
                Modified, SortOrder.Descending
            )
    },
    FirstN(
        DropColumns(
            LastN(
                filteredData,
                CountRows(filteredData) - ((varPageNumber - 1) * varPageSize)
            ),
            ID
        ),
        varPageSize
    )
)

Please help me resolve this — the gallery should be able to display at least 5000 records in gallery without missing any data, also the gallery should support filtering, sorting search, and display latest records first.
Thanks in advance!

Categories:

Connectors

Using Apps Using Formulas

Mobile App

Connecting to Data

Copilot

Hi @CU05121057-0,

That would be the expected behaviour - there are a couple of things I can see in there for a start. CountRows() is not Delegable and FirstN needs to have a "starting point". Fundamentally however, the maximum data set you can retreive "locally" in one pass (such as your exercise) is your Data Row Limit (maximum 2,000).

The only way to "stack" a collection beyond this to do it with ForAll(Collect . . .)) as per this blog of mine. You can then use the Collection as the gallery Items.

There is a route using Power Automate - this video should be a good start for you.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee