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.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!