cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Title field delegation warning

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Title field delegation warning

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


@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!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

7 REPLIES 7
Highlighted
Super User
Super User

Re: Title field delegation warning

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


@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!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

Highlighted
Community Champion
Community Champion

Re: Title field delegation warning

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.

Highlighted
Community Champion
Community Champion

Re: Title field delegation warning

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

Please accept whichever one helps you the most.

Highlighted
Advocate I
Advocate I

Re: Title field delegation warning

Thank you for your response, except I'm confused. If Search is not delegable, what does this refer to?

 

search delegation.png

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

Highlighted
Super User
Super User

Re: Title field delegation warning

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


@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!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Advocate I
Advocate I

Re: Title field delegation warning

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:

NamePossessions
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?

Highlighted
Super User
Super User

Re: Title field delegation warning

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!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,543)