cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
forcedLightning
Level: Powered On

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

7 REPLIES 7
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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

WarrenBelz
Level 8

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.

WarrenBelz
Level 8

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.

forcedLightning
Level: Powered On

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
forcedLightning
Level: Powered On

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?

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,160)