Answered

Sharepoint List Delegation Issue with APP. Better formula?

(0) Share

Report

Posted on by PowerPete13

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

FishinKmac 75 on at

Like (0)

Report
Copy link

Link copied!
How much data is in the list?
How many Organizations are there?
How far back in the date do you need to go?
What are the different status values?

Was this reply helpful? Yes No
PowerPete13 76 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,781 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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 ) ) )

Was this reply helpful? Yes No
FishinKmac 75 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No