You’re offline. This is a read only version of the page.
54
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.
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
Share
Report
All responses (
Answers (
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.
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
)
)
)
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.
#1
WarrenBelz
146,618
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,957
Most Valuable Professional
