PowerApp not loading beyond 500 records from data source in vertical gallery

[michaeldanieal]

Hi guys,

I encountered this issue while working in a canvas app using vertical gallery.

First I gave the data source directly to the

gallery(Items=Filter(TestTable,Column1=SearchInput.Text))

and it worked fine, loading records in batches of 100 as I scroll down the gallery

Initially,

As I scroll down to the last item,

And it goes on until the end of the list.

Now I want to add row number to each of the record to show the records in alternate colors, so what I did was

gallery->Items=

With(

    {varListItems: Filter(TestTable,Column1=SearchInput.Text)},

    ForAll(

        Sequence(CountRows(varListItems)),

        // this patch is used to add the row numbers to the obtained records

        Patch(

            Last(

                FirstN(

                    varListItems,

                    Value

                )

            ),

            {RowNumber: Value}

        )

    )

)

Now the problem is this ,

even if I Scroll to the last row it doesn't load the next batch of records. Why is this happening? Is there any alternative way to do this? Thanks in advance

@mdevaney @CarlosFigueira @elseb @Ethan_R 

[mrbrooksshawn]

Hi Michaeldanieal. Instead of connecting directly to your data source, have you tried to create a collection using Collect or ClearCollect? It would be "Collect(colColName, yourdatasource)" or "ClearCollect(colColName, yourdatasource)". Please let me know if that works for you.

[michaeldanieal]

Thanks, but still same results

I tried with both 

Collect(colListItems,TestTable);  and

Clearcollect(colListItems,TestTable);

in the OnStart() of the App

[mrbrooksshawn]

I think you need Pagination. I've done it before and that helped me in a similar situation. Here's a good video from Deepak Shrivastava. Give it a try: https://youtu.be/M2033jUWqRY?si=N9L0Mbu8F76faPBB

[michaeldanieal]

The requirement is to load it on scroll, so I can't use pagination.

[mrbrooksshawn]

I have a couple of ideas. One is Go to File > Settings > General. Find "Data row limit" and increase it to a higher value (up to 2000).

The other is to add another gallery (GalleryTabs) that you can use as tabs that hold only the DISTINCT category (for example, "Karl Marx" as one entry in the category). In your main gallery, filter it by category. I just uploaded a video on a Periodic Table in Power Apps last night with this feature. Take a look and let me know what you think and if that makes sense.

[elseb]

HI, 

"With" is the culprit I'm afraid,

It's non delegable.

@WarrenBelz explains it below:

With() Statement managing Delegation – Practical Power Apps

[WarrenBelz]

@michaeldanieal ,

I will add a comment as @elseb has referenced my blog.
With() is a "client side" operation and never goes to the data source, so technically Delegation does not come into it (sorry if that is a play on words, but the difference is important). It is a "container" (call it a very temporary Variable) in this case a Table, which is no different to a Collection and is subject to your Data Row Limit (as is a non-Delegable query), however the difference is important in that it will run a Delegable query inside it and hold from the result of the query record numbers up to your Data Rows Limit. The output of a With() statement is then processed "locally". It is a great tool, but you still need to be aware of the possible limited output from it.

[michaeldanieal]

Ok, I tried like this, but still unable to load more than 500 records and now the site lags a lot.

ForAll(

Sequence(CountRows(Filter(TestTable,Column1=SearchInput.Text))),

// this patch is used to add the row numbers to the obtained records

Patch(

Last(

    FirstN(

        Filter(TestTable,Column1=SearchInput.Text),

        Value

    )

),

{RowNumber: Value}

)

)

What can I do better?

Thank you.

[michaeldanieal]

Thank you. But I'm unable to open the link. I checked with various browsers and even VPN but couldn't open it. Thanks for replying.