Search by Keyword on SharePoint (delegation suggestions)

[Sam4]

Hi, 

I'm building an app and the data are stored in a SharePoint List. This list can have more than 2000 records (around 10000 as a maximum). The list has one field "Title" than can be a sentence of 6 or 7 words with no particular structure. For example, Something like this:

Row 1) "This is the building XXXXX"

Row 2) "The problem with this car is XXXXX" 

Row 3) "There is an opportunity of XXXXX"

Row 4) "Building 1 has a problem with "xxxx

So if I type in a search box "problem" my gallery should return Rows 2 and 4.

 In the app I have a textinput element to type in the word I would like to search for. The solution that comes to my mind is to create a collection something like:

CreateCollect(Coltemp1, MySPList, Rows 1 to 2000);

Collect (ColTemp2, MySPList, Rows 2001 to 4000);

Collect (ColTemp3, MySPList, Rows 4001 to 6000);

Collect (ColTemp4, MySPList, Rows 6001 to 8000);

Collect (ColTemp5, MySPList, Rows 8001 to 10000);

ColTotal = ColTemp1 + ColTemp2 + ColTemp3 + ColTemp4 + ColTemP5 

Then use ColTotal collection in a Gallery setting the items property to

Filter(colTotal, Filter_Search.Text in Text_Title)

I would like to avoid the creation of multiple collection as I don't know how it will impact in the performance but I don't see any other way of doing this.

Is there any way of searching (or filtering) in a SharePoint List by keyword?

Kind Regards

[timl]

Hi @Sam4 

Have you considered using the Search function instead of Filter? From recollection, I think it's delegable against the SharePoint title field.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-filter-lookup 

[Sam4]

Spoiler

Hi @timl many thanks for your quick reply, 

Unless I'm missing something I think Search is not delegable agaisnt SharePoint Lists as it is not listed here https://docs.microsoft.com/en-gb/connectors/sharepointonline/.

I've tried in any case something as the below and it is not working (it works, but limited to the 500 records as I haven't extended to 2000 limit yet)

Search('SharepointListName', SearchBarName.Text, "Title")

[v-siky-msft]

Hi @Sam4 ,

Yes, the in and Search function are all undelegable functions,. From my experience, the workaround you post is the most appropriate way.

To reduce the load and optimize the performance, you can try to use ShowColumns function, which will only store the columns you need to Collection. Reference: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping 

Best regards,

Sik

[Anonymous]

@Sam4 That code structure to create multiple collections won't work, at least I've never gotten it to work using SP. It maxes out at 2000 records.

What I'd suggest is create a "Keyword" column in your List and use Flow to fill it. Aim to keep the number of any keywords for any given scenario to a max of 2000 eg Keyword = 'problem'; Number of SP items with keyword = 'problem' <= 2000.

You could use the Switch function in flow to check for certain words then fill your new column with matching words. You could even have a Default result of the keyword/s "No Match". Then back in your app, use this column to pull in your data that you want.

The more I use PowerApps the more I'm finding that sometimes creative solutions need to happen on your data source end rather than as part of the App build.

[timl]

Hi @Sam4 

Apologies about that, you're correct that Search isn't delegable. I was thinking of SQL Server.

Hopefully, some of the other suggestions here will work better for you. Good luck!

[Sam4]

Many thanks for your replies,

@timl No worries, I wasn't sure and was good to double check :).

@Anonymous I've been able to create the Main collection using this solution from Gidrone_NS (with some amendments). It works fine at the moment. Now I'm working in optimise the load using only the columns that I need.

@v-siky-msft I'm trying to use the ShowColumn option you mentioned to optimise the performance. It works but I'm not getting the full set of items yet. It seems it is something related to the sintaxis. I'll fix it next time 🙂

Thanks all again!

[Anonymous]

@Sam4  thanks for that link. That's a solution I hadn't come across as yet for collecting large datasets into an app. I've tried a number of solutions, even built my own that works, but the trade-off with build time or data source setup just isn't worth it for my users needs, hence I've stopped trying. However, I may at some point give this a whirl.

I hope you find your solution