cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d3ell Helper V
Helper V

Seachbox delegation issues - Help with Formula -Please

Hi All,

 

I have a gallery that shows its content via the below formula. I have a blue wavy delegation line under "BellRef" which is the column that we are searching.  Source content is via sharepoint list.

 

Is there a workaround or alternative formula where i will have the same outcome without delegation issues?

 

Search(Filter(CustomerCareLog,Complete="0"), Searchbox_1.Text,"BellRef")

The search basically checks the whole data source to see if a job is uncomplete (ie. "0" boolean value) and then as we have hundreds of these, which will continue growing i have a searchbox where the user can type in the referance to narrow the search down,

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Seachbox delegation issues - Help with Formula -Please

 I think I made an error in my StartsWith() function. It should be StartsWith(BellRef,Searchbox_1.Text) 

see General-Discussion/StartsWith-in-combination-with-amp-amp-and-filter/m-p/24400#M10500

View solution in original post

12 REPLIES 12
Microsoft
Microsoft

Re: Seachbox delegation issues - Help with Formula -Please

Hello @d3ell,

The reason for the warning is that "Search" function is not delegable in SharePoint (see this page for more details: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list)

Looking at your formula, I think you should be able to avoid the "Search" function by changing your formula as following:

 

Filter(CustomerCareLog,Complete="0" And BellRef=Searchbox_1.Text)

You may have to use Lower(BellRef)=Lower(Searchbox_1.Text) for ignoring letter cases. (more details can be found here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-lower-upper-proper)

 

 

d3ell Helper V
Helper V

Re: Seachbox delegation issues - Help with Formula -Please

Hi, Your formula works, however, not really for my application as with this formula i only see the records in the gallery once we enter the "BellRef" into the search box.  What i need is for the gallery to perform as per the previous scenario where all the records that are not complete ("0") can be seen and scrolled through, but when you enter text into the search box it then narrows down the search to the selected record.

 

Is this possible?

 

Thanks

Microsoft
Microsoft

Re: Seachbox delegation issues - Help with Formula -Please

Got it. Thanks for exmplaning.

How big is your list? The reason I'm asking is because if it's less than the maximum number of records that can be pulled in PowerApps then you can safely ignore the delegation warning. The maximum number of records is set to 500 and can be increased to up to 2,000 from application settings.

d3ell Helper V
Helper V

Re: Seachbox delegation issues - Help with Formula -Please

Currently have just over 1000 records, i have already changed the delegation to 2000, i guess i just wanted to future proof it as i'm not sure how i'll know to re-check this when the records go above 2000 and what potential issues it could cause.

Microsoft
Microsoft

Re: Seachbox delegation issues - Help with Formula -Please

I don't think there's currently an easy way to work around that limitation, at least not in SharePoint (other data abses, such as SQL Server, do support delegation on Search function).

As a non-easy solution, you can try to break your SharePoint list in chunks of 500 records (field value chunk = 1  for records 0 - 499, chunk=2 for records 500-999 and so on), preload all the chunks into a collection and run a search there. For example: Collect(InMemoryCol, Filter(SPList, chunk=1));Collect(InMemoryCol, Filter(SPList, chunk=2)), ...

This is not an elegant solution but will ensure that you never get affected by the number of rows limitation.

 

d3ell Helper V
Helper V

Re: Seachbox delegation issues - Help with Formula -Please

Thanks for your reply and your proposed solution. I’ll have a look at this in the office tomorrow, really appreciate your time and effort with helping out.

With regard to the delegation I’m trying to work out if the delegation of 2000 records is from my filtered list? Ie in this case where the job is incomplete (“0”) or of the delegation is from the list as a whole? Was thinking the delegation was from the filtered list then I could possibly add another filter to reduce the amount of records before the search?

Does that make sense?

Ie. Let’s say I hypothetically have 3000 but 50% of them are complete (ie “1” Boolean value) then would the search still work as it would only be searching 1500 records?

If that’s the case by adding a 2nd filter I could reduce the search qty quite dramatically.

Thanks
Super User
Super User

Re: Seachbox delegation issues - Help with Formula -Please

Hi d3ll, The formula for your Items property: Filter(CustomerCareLog, Complete="0" && StartsWith(Searchbox_1.Text, BellRef)) should work fine without delegation issues in a SharePoint list.
Microsoft
Microsoft

Re: Seachbox delegation issues - Help with Formula -Please

In your example with 3000 records, half of them being complete, the result unfortunately would be incosistent. Since the "Search" function cannot be delegated, it runs on the client (as opposed to the server). What will happen in this case is max rows of unfiltered data (2000 rows) will be downloaded to the client and then the search will be applied on those 2000 rows. 

 

Please note that "Search" does not work the same as "StartsWith". "Search" works closser to "in" ("pattern" in "Value") but "in" is not delegable either in SharePoint. However, if StartsWith satisfies the intended behavior then @Drrickryp 's suggestion is a great alternative.

d3ell Helper V
Helper V

Re: Seachbox delegation issues - Help with Formula -Please

With this i'm still getting a lot of blue squiggly lines and delegation warnings. See attached.

 

Also, all the gallery items arent displaying initially? - Should the gallery only popularte when you start typing? however, even when i start typing in the text box, nothing happens?

 

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,579)