Search () Dataverse Table

[iwonder]

Thank you for taking the time to read my question.

I have an app connected to Dataverse tables. I have a Gallery that has the following Items formula:

Sort(If(IsBlank(txtCustomerFilter_1),Customers_1, Search(AddColumns(Customers_1,"txtCustCustNum",Text(CustCustNum)),txtCustomerFilter_1.Text,"cra98_addressname","txtCustCustNum")),AddressName)

I have a Text Input box where the user can type in a customers name or ID. It works great.... so I thought...

I was using a few customers as examples and they were always found. I starting trying a bunch of different customers and now many of them are not found.

I was under the impression Search() worked in Dataverse... apparently not. I guess the customer examples I was testing were in the first 500 rows. My data table has almost 5000 and could grow from there.

Is there a workaround by chance as StartsWith() is not really a great alternative as users don't always know exactly how the text is and having the ability to return results based on a substring within a string is VERY effective.

Will Search() ever be added to Dataverse?

Thanks,

[timl · Accepted solution]

Hi @iwonder 

The issue here is the call to AddColumns. I see why you call this, in order to covert CustCustNum into text so that it can work with the Search function. However, since AddColumns executes on the client rather than in Dataverse itself, it will search within the first 500 rows only (or the number specified in the datarow limit setting).

A possible workaround is to create a calculated column in Dataverse that returns CustCustNum as text. You can then call Search against this calculated column, and the results should then apply to your entire table. 

[iwonder]

Brilliant!!!!!!

Thank you thank you @timl 

That absolutely did the trick! Sorry for all the !!!!, I'm just so happy I can use Search() again.

have a great day!

[timl]

You're welcome @iwonder! Glad that worked for you.