Which filter formula is better to use - as it does not bring the same result

(0) Share

Report

Posted on by MIA27

when I tested with 266 records, then below both worked fine. It filters same number of records

Filter(TblSvcHist,StartsWith(SerialNo,TextSearchBox1.Text))

Filter(TblSvcHist,TextSearchBox1.Text in SerialNo)

BUT when I tested with around 3000 records, both does not bring the same result

Filter(TblSvcHist,StartsWith(SerialNo,TextSearchBox1.Text)) - This brings correct result

Filter(TblSvcHist,TextSearchBox1.Text in SerialNo) - This bring abnormal all the search having the same result of 100 records

Advantage/preference I give to second formula of "TextSearchBox1.Text in SerialNo" as it does the work same like

*text*

Confused, how to solve this, the one i prefer is bringing the wrong result. while both should bring the same result.

Please advise

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

Pstork1 69,039 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

The problem with the second one is that the 'in' operator isn't delegable so it won't get all the matches when used with a large list. StartsWith is delegable, but will only find records where the search value is at the beginning of the SerialNo field. 'in' will find the search value anywhere in the SerialNo field, but as I said its not delegable so it will only search the records up to the Data Row limit.

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply.
But my need to have *text* option.
What about Search(), does it can help ?
Please advise

Was this reply helpful? Yes No
Verified answer

Pstork1 69,039 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Search is also not delegable. Your only real choices if you have a large list and need to search inside a field is to

1) Use a different Data source like Dataverse where the in operator is delegable

2) Find a way to pre-filter the list to less than the data row limit of records before using the 'in' operator. In other words nest a delegable filter() inside a non-delegable filter.

Some would also suggest you could copy the entire data source to a collection because delegation doesn't apply to local collections. I don't recommend that because it causes more problems than it solves. First it is time consuming for large lists and it also usually leads to data corruption since changes made while you have the data local will not be applied to your data.

Was this reply helpful? Yes No