I have a formula that uses Filter, Search and Sort functions, all of which are delegable. The Search function searches several columns. One of them is the Title field of the source SharePoint list. The column is indexed, but it is still giving me a delegation error. Does anyone know why it is still giving me the error, since all the functions in the formula are delegable?
Formula: Search(Sort(Filter('Staff Information', Department.Value = Dropdown2.Selected.Title Or Dropdown2.Selected.Title = "All"), Name, Ascending),SearchTextInput.Text, "Title", "JobTitle", "Phone", "Email")
Solved! Go to Solution.
Unfortunately Search is not delegable within SharePoint lists (link here). If you removed "Title" from the search parameters, you'll see that the next variable is then highlighted, and if you remove that then you'll see the next, it's not the best delegation warning tbh.
You could re-write your formula as follows, however the searchtext is then limited to the starting characters:
Sort(
Filter(
'Staff Information',
//Condition 1
(
Department.Value = Dropdown2.Selected.Title
||
Dropdown2.Selected.Title = "All"
)
,
//Condition 2
//Using StartsWith as Search is not delegable with SharePoint
(
SearchTextInput.Text = ""
||
StartsWith(Title, SearchTextInput.Text)
||
StartsWith(JobTitle, SearchTextInput.Text)
||
StartsWith(Phone, SearchTextInput.Text)
||
StartsWith(Email, SearchTextInput.Text)
)
)
,
Name, Ascending
)
If your list is small and not likely to grow, you could use 'in' instead of StartsWith,
Let me know if this solved your issue - I have to do 90% of my Apps with SharePoint so have come across the delegation problem quite often 🤔
Cheers,
Sancho
@iAm_ManCat |
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you! |
Thanks! |
Unfortunately Search is not delegable within SharePoint lists (link here). If you removed "Title" from the search parameters, you'll see that the next variable is then highlighted, and if you remove that then you'll see the next, it's not the best delegation warning tbh.
You could re-write your formula as follows, however the searchtext is then limited to the starting characters:
Sort(
Filter(
'Staff Information',
//Condition 1
(
Department.Value = Dropdown2.Selected.Title
||
Dropdown2.Selected.Title = "All"
)
,
//Condition 2
//Using StartsWith as Search is not delegable with SharePoint
(
SearchTextInput.Text = ""
||
StartsWith(Title, SearchTextInput.Text)
||
StartsWith(JobTitle, SearchTextInput.Text)
||
StartsWith(Phone, SearchTextInput.Text)
||
StartsWith(Email, SearchTextInput.Text)
)
)
,
Name, Ascending
)
If your list is small and not likely to grow, you could use 'in' instead of StartsWith,
Let me know if this solved your issue - I have to do 90% of my Apps with SharePoint so have come across the delegation problem quite often 🤔
Cheers,
Sancho
@iAm_ManCat |
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you! |
Thanks! |
Hi @forcedLightning Search is not delegable in SharePoint , You could try
Sort(
Filter(
'Staff Information',
(
Department.Value = Dropdown2.Selected.Title ||
Dropdown2.Selected.Title = "All"
) &&
StartsWith(Title, SearchTextInput.Text) &&
StartsWith(JobTitle, SearchTextInput.Text) &&
StartsWith(Phone, SearchTextInput.Text) &&
StartsWith(Email, SearchTextInput.Text),
Name,
Ascending
)
or if the target list is under 2000 items of you can filter it to this with a delegable query, Search should work on the collection.
Please click Accept as solution 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 Thumbs Up.
Just noticed @iAm_ManCat posted the same solution (my typing must be slow).
Please accept whichever one helps you the most.
Thank you for your response, except I'm confused. If Search is not delegable, what does this refer to?
from: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-functions
Yeah, see in that top paragraph: "...that could be delegated"
..so Search is delegable, as long as the data source supports it - SharePoint sadly does not 😿
If you look at the documentation for each data source, technically of the three listed only SQL connector supports search
Unless I'm working with a collection or a very small subset or list, I try avoid using Search - if they eventually update it to be fully delegable I will be very happy, as StartsWith assumes your users know what it starts with not the words in the middle or end!
Cheers,
Sancho
@iAm_ManCat |
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you! |
Thanks! |
I see the difference now, thank you.
Now with that in mind, I have a final issue: I have tabled information that needs to go within a single list item, and needs to be searchable. Example:
Name | Possessions |
John Smith |
|
I search for "TV", or select "TV" from a dropdown, and this list item displays. Is this simply not achievable within delegable limits?
Within SharePoint's delegation limits, no, within another database yes.
What I usually do with these is create four separate text/choice fields (if you know it's never going to be more than four, otherwise accommodate for how many you think will be available, then you have the visibility of each one dependant on whether anything is selected within the previous.
So you'd have one dropdown initially, then when the user chooses their option, then next dropdown becomes visible, etc etc
Then have one dropdown with all posession types, and in your gallery filter you can do something like:
(REST OF FILTER)
,
dropdown.selected.Value = ""
||
Possession1 = dropdown.selected.Value
||
Posession2 = dropdown.selected.Value
||
Posession3 = dropdown.selected.Value
||
Posession4 = dropdown.selected.Value
Not an ideal situation, but working around delegation limits will unfortunately force these kinds of situations. This is why I'm trying to convince our company's management to buy some licenses (not for everyone obviously), so that I can start working with more complicated databases and not have to create these crazy workarounds 🤔
Cheers,
Sancho
@iAm_ManCat |
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you! |
Thanks! |