Skip to main content
Community site session details

Community site session details

Session Id : o89G7Vg3Fb0EmVHAyS0yUf
Power Apps - Building Power Apps
Answered

Sharepoint List Delegation Issue with APP. Better formula?

Like (0) ShareShare
ReportReport
Posted on 18 Jan 2024 22:09:55 by 72

Running into delegation issues with a formula used for a Gallery Table connected to a SharePoint list with a search box and sort Ascending to Descending Button. Are there any experts out there that can evaluate this formula and help me come up with something that won't have a delegation issues? Or is that a dream never to come true?I don't see this app every moving away from a SharePoint list and it most likely going to exceed the 2000 record limit....   The app is a visitor management system.

 

The default sort I am using does not appear to function correctly either. I'm no expert and Frankensteined this formula together and somehow got it to work. LOL

 

If(!IsBlank(varVisitorSort), SortByColumns(Filter('DataSource', (SearchVisitor.Text in 'Visitor Name') || SearchVisitor.Text in Organization || SearchVisitor.Text in 'Checkin DateTime ' || SearchVisitor.Text in 'Reason For Visit' || SearchVisitor.Text in Status ), "CheckinDateTime", If(varVisitorSort = true, SortOrder.Ascending, SortOrder.Descending)), 'Visitor Registration Log')

 

Grateful for any help with this. 

 

  • FishinKmac Profile Picture
    75 on 19 Jan 2024 at 14:07:32
    Re: Sharepoint List Delegation Issue with APP. Better formula?

    I personally don't like using the search bar for every field in the gallery so if you can make something a choice field like status or organization, i would do it. This also makes it easier to pull in the content using collections if you ever hit that 2k limit. I very rarely connect directly to my datasource in my gallery, and i find using collections is much faster. 

     

    If you dont think you will have much more than 2k visitors a year you could do something like this to help not hit those limits

     

    Set(varDate, Today()-365);
    Concurrent(
     ClearCollect(colApproved, 
     Filter('DataSource', Check In Date<=varDate && Status.Value="Approved")),
     ClearCollect(colDenied, 
     Filter('DataSource', Check In Date<=varDate && Status.Value="Denied")));
    ClearCollect(colAll, colApproved, colDenied)

    .

    Use the search bar to then search for the organization using the in function since there can be many orgs to search through. 

     

    Your Gallery would be 

    SortByColumns(
    Filter(colAll,SearchVisitor.Text in Organization ||SearchVisitor.Text in 'Visitor Name' || SearchVisitor.Text in 'Reason for Visit')

    Then you could also implement a filter for the date range instead of searching the date range. You can do that through a date range picker or you can have a drop down if you want specific dates.  

  • Verified answer
    WarrenBelz Profile Picture
    148,894 Most Valuable Professional on 19 Jan 2024 at 01:56:37
    Re: Sharepoint List Delegation Issue with APP. Better formula?

    Hi @PowerPete13 ,

    Your fundamental issue is that the in Filter is not Delegable with SharePoint (the 2,000 restriction is only on non-Delegable queries) . I am not sure what you are doing what the If() statement at the top as a yes/no field should not be blank (only true or false). However fundamentally you have two choices - use StartsWith (assuming here that all of those fields are Single Line of Text)

    SortByColumns(
     Filter(
     'DataSource', 
     StartsWith(
     'Visitor Name',
     SearchVisitor.Text
     ) ||
     StartsWith(
     'Checkin DateTime ',
     SearchVisitor.Text
     ) ||
     StartsWith(
     'Reason For Visit',
     SearchVisitor.Text
     ) ||
     StartsWith(
     Status,
     SearchVisitor.Text
     )
     ),
     "CheckinDateTime", 
     If(
     varVisitorSort, 
     SortOrder.Ascending, 
     SortOrder.Descending
     )
    )

    or is the newest 2,000 records will get all the records you need

    With(
     {
     _Data:
     Sort(
     'DataSource',
     SortOrder.Descending
     )
     },
     SortByColumns(
     Search(
     _Data, 
     SearchVisitor.Text
     "Visitor Name",
     "Organization",
     "Checkin DateTime",
     "Reason For Visit",
     "Status"
     ), 
     "CheckinDateTime", 
     If(
     varVisitorSort, 
     SortOrder.Ascending, 
     SortOrder.Descending
     )
     )
    )

     

  • PowerPete13 Profile Picture
    72 on 18 Jan 2024 at 22:22:02
    Re: Sharepoint List Delegation Issue with APP. Better formula?

    1. Right now - not much the app hasn't been published. Just a few entries from testing.

     

    2. Organizations - is a text field so there could be numerous different ones entered. 

     

    3. The list needs to go back at least one year. 

     

    4. 'Status values' is also a text field but there will only be two: Approved or Denied. 

     

    Thanks for taking a look at this. 

  • FishinKmac Profile Picture
    75 on 18 Jan 2024 at 22:14:23
    Re: Sharepoint List Delegation Issue with APP. Better formula?
    1. How much data is in the list?
    2. How many Organizations are there?
    3. How far back in the date do you need to go?
    4. What are the different status values?

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

Announcing our 2025 Season 2 Super Users!

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

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete