Seachbox delegation issues - Help with Formula -Please

[d3ell]

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.

[dinusc]

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]

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

[dinusc]

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]

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.

[dinusc]

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]

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

[Drrickryp]

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.

[dinusc]

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]

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?