Skip to main content

Notifications

Power Apps - Error Handling
Answered

Help on delegation work around. enable gallery to show more than 2000 records.

(0) ShareShare
ReportReport
Posted on by 28
Hi,
 
First of, I'm a newbie on this as I'm only a casual user of PowerApps. Please be gentle ;P
 
I have a PowerApps productivity tracker that records the time start and end of the activity. This is recorded in a SP list (Test3) which is utilized by all App users. 
 
My problem is when my SP list reached 2000 records, the gallery will no longer show the latest entries of the user even though it is filtered to only show the "user" entries. 
 
Here's how I formulated the gallery item:
 
//Items//

FirstN(Sort(
   Filter(
      Test3,
      //Check if length of selected is zero, else filter by that selection
      Len(DropdownCanvas1.Selected.Value)=0 || DropdownCanvas1.Selected.Value = NPT.Value,
      Lower('Created By'.Email) = Lower(User().Email)
    ), 
   ID,SortOrder.Descending
   ),
500
)
The requirement is:
1. Filter to show only the users entries
2. Show the latest entry of the user on top
3. Allow population even if records are more than 2000 records
 
Appreciate any advice/help. 
  • ZERAREZ Profile Picture
    ZERAREZ 28 on at
    Help on delegation work around. enable gallery to show more than 2000 records.
    Thank you @WarrenBelz, the change you mentioned fixed the gallery view. Thank you again! 
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,445 on at
    Help on delegation work around. enable gallery to show more than 2000 records.
    The issue is around the two non-delegable functions Lower and FirstN. You should not need Lower as 'Created By' will be a case-sensitive exact email address of the user, which will always match User().Email. You can then deal with FirstN as below, which will be applied to a fully Delegable query output.
    With(
       {
          _Data:
          Sort(
             Filter(
                Test3,
                Len(DropdownCanvas1.Selected.Value) = 0 || 
                NPT.Value = DropdownCanvas1.Selected.Value,
                'Created By'.Email = User().Email
             ), 
             ID,
             SortOrder.Descending
          )
       },
       FirstN(
          _Data,
           500
       )
    )
     
    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
  • Ytalo Silva Profile Picture
    Ytalo Silva 41 on at
    Help on delegation work around. enable gallery to show more than 2000 records.

    Hello, there are a few ways to retrieve records that exceed 2000. If you're having difficulties with this and want to fetch more than 2000 records, one suggestion is to create a collection in the app's OnStart as follows:

    ClearCollection(
    colMore2000Records,
    Test3,
    Lower('Created By'.Email) = Lower(User().Email)
    ) // Up to 2000 records

    // 4000 records
    Collect(
    colMore2000Records,
    Test3,
    Lower('Created By'.Email) = Lower(User().Email),
    ID >= 2000
    )

    This way, it will fetch more than 2000 records.

    Another suggestion is to create a pagination system that adds more records to the collection as you navigate through the pages.

    If you encounter a delegation warning on the ID field, one solution is to create a mirrored column of the ID in number format. This can help resolve the delegation warning.

    I hope this helps provide some guidance.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard

Featured topics