Using In and ExactIn for a text search in sharepoint. I need a delegable solution.

(0) Share

Report

Posted on by martinav

3,347

I have used search statements like this:

ParNumText = "14323,91723,17391,20981"
varSearchText = "91723"

varSearchText

Filter(SQL_Database, varSearchText In PartNumText)
Filter(SharePoint_Database, varSearchText In PartNumText)

In the figurative example above, the record is returned for the SQLDatabase, and there is no delegation warning.

If the example is for the SharePoint_Database, it returns the same result, but delegation warning.

Is there a way to do this for a sharepoint database, without having a delegation problem??

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@martinav

All solutions for using 'in' with SharePoint are workarounds. Also, ExactIn is delegable to either source.

2 SharePoint workarounds that I'm aware of are:

1. Filter first. Use filtering options to reduce the number of records returned to under 2000, then use in. For example

With( { wList: Filter(SharePoint_Database, [... some filter here that reduces no. records ...]) }, Filter( wList, varSearchText in PartNumText) )

Note: take using With here. It can 'hide' the delegation warning but it can still be there to the unsuspecting user.

2. Pull your data into a collection, then use 'in' and/or 'ExactIn' without issue. I have a method that pulls list data in dynamically. On my work network I can use this equation to calc the time taken to collect records

(No. Records + 1100) / 1.35 = No milliseconds to return data, ie

Hope that helps.

Was this reply helpful? Yes No
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@EddieE ,

Unfortunately, I cannot do a staged filter. The search will require access to the entire list. outside of splitting up the record in batches of 2000, and doing a forall() over the batches, and Collect() the results. But, that would be very expensive. I was hoping to avoid making a duplicate SQL database, but I guess I cannot avoid it. I have done this in other instances, but I was hoping for some improvements in the delegation protocol for SP. Apparently, there has not.

Was this reply helpful? Yes No