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?
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.
Solved! Go to Solution.
I think I made an error in my StartsWith() function. It should be StartsWith(BellRef,Searchbox_1.Text)
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)
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?
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.
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.
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.
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.
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?
Watch Microsoft Business Applications Summit sessions on-demand.
And the winner is...
The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!
Features releasing from April 2020 through September 2020