Search data by writing a part of the text not only the begining

[Alex_Reig]

Hi all,

I have created a power app where I want to search data by writing only a part of the text in the searching field but now it only appears if I write the beginning of the text due to in the formula, I have StartsWith.

SortByColumns(Filter('Imputar horas'; StartsWith(Cliente.Value; TextSearchBox1.Text);'Creado por'.Email = User().Email); "Fecha"; If(SortDescending1; Descending; Ascending))

For example, I want to search all the registers that have across I have to write Across but I want to also see this registers if I write Health or hea or another part of the text.

What is the formula that I have to put instead of StartsWith?

Thank you very much,

Àlex Reig

[C-Papa · Accepted solution]

Hi, you need to use 'in' as shown below, if you are using SharePoint, this will give a delegation warning

SortByColumns(Filter('Imputar horas'; TextSearchBox1.Text in Cliente.Value;'Creado por'.Email = User().Email); "Fecha"; If(SortDescending1; Descending; Ascending))

[RandyHayes]

@Alex_Reig 

You will need to use the Search function to perform the type of search you want.  However, keep in mind that Search is not delegable and will not work on large data sources properly.

However, you have a "narrowing filter" in your formula, so you can leverage that with the Search function to avoid delegation issues.  

Your formula would be such as this:

With({_items: 
    Filter('Imputar horas'; 'Creado por'.Email = User().Email)
    };
    SortByColumns(
        Search(_items; TextSearchBox1.Text; Cliente.Value);
        "Fecha"; 
        If(SortDescending1; Descending; Ascending)
    )
)

I hope this is helpful for you.

[RandyHayes]

@Alex_Reig 

Cross posted with @C-Papa !  That formula will have the same delegation issues when using the in operator.  You can structure as shown in my formula that uses search to use in if you would rather use the in operator.

[Alex_Reig]

Thank you @C-Papa  🙂

It worked

[Alex_Reig]

Hi @RandyHayes 

Thank you for answering 🙂

I tried your formula but Power app show me that there was a problem with Search

[RandyHayes]

@Alex_Reig 

No problem.  I am seeing that you have a Cliente.Value that you are trying to search - so that is not a Text column.  Didn't notice at first.

So with that particular Choice or Lookup column, there are a couple ways to go about using search on it, but the reality is that the in operator will do the trick also if it is just that column you are looking to search.

But to incorporate that into your formula and not get the delegation warning, your formula should be:

With({_items: 
    Filter('Imputar horas'; 'Creado por'.Email = User().Email)
    };
    SortByColumns(
        Filter(_items; TextSearchBox1.Text in Cliente.Value);
        "Fecha"; 
        If(SortDescending1; Descending; Ascending)
    )
)