Title field delegation warning

[forcedLightning]

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")

[iAm_ManCat · Accepted solution]

Hi @forcedLightning 

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

[WarrenBelz]

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.

[WarrenBelz]

Just noticed @iAm_ManCat posted the same solution (my typing must be slow).

Please accept whichever one helps you the most.

[forcedLightning]

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

[iAm_ManCat]

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

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

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

[forcedLightning]

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	House Car TV Hat

I search for "TV", or select "TV" from a dropdown, and this list item displays. Is this simply not achievable within delegable limits?

[iAm_ManCat]

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