multiple search functions

[Chenhui]

Hello,

May i know if it is possible to have to search functions used in a single gallery search(search(table, textinput1.text, column1), textinput2.text, column2). I tried this originally and it worked but on my new table when i tried this i had an error message:

The requested operation is invalid. Server Response: table failed: invalid filter clause: unsupported operation. only single 'eq','ne','contains','startswith' or 'endswith' is currently supported. 

I have tried using search(filter(...)) too however it doesnt work either. 

Thank you,

chenhui

[v-qiaqi-msft · Accepted solution]

Hi @Chenhui,

Could you please post your complete Search() formula?

Do you want to search multiple columns?

If you want to search multiple columns, you should use Filter() as below:

Filter(Table,TextSearchBox1.Text in Column1||TextSearchBox2.Text in Column2|| TextSearchBox3.Text in Type of Column3)

[Chenhui]

hello @v-qiaqi-msft 

my search formula is:

Search(Search(apple, TextInput1.Text,"Market"), TextInput2.Text,"Product")

i have also tried 

Search(Filter(apple, StartsWith(Market, TextInput1.Text)), TextInput2.Text, "Product")

I would like to search by two columns but with different criteria eg. name of market as well as the product code so two key words when im searching

May i clarify if the code for my case should be:

Filter(apple,Textinput1.Text in Market || Textinput2.Text in Product)

I tried this code but it says:

Delegation warning. The highlighted part of this formula might not work correctly on large data set. The "Pr" operation is not supported by this connector. The data source might not be able to process the formula and might return an incomplete data set. Your application might not return correct results or behave correctly if the data set is incomplete. 

Im sorry i forgot to mention i am currently using data from an excel sheet not sure if that changes the issue.

Thank you!

Chenhui

[v-qiaqi-msft]

Hi @Chenhui,

The delegation warning is not an error message.

If the records in your excel are not more than 2000, you should try the formula I mentioned which should work on your side.

[Chenhui]

Hello @v-qiaqi-msft,

Okay noted! Thank you! I realise my excel is exceeding 2000 by 100, does that mean the formula will not work? If i split my table into two tables will the formula work

In addition may i confirm if this formula is correct? 

Filter(apple,Textinput1.Text in Market || Textinput2.Text in Product)

apple is the name of my table

Market and Product are the column names i would like to search in

Textinput1 and 2 are the two text boxes for users to search in for each column 

Thank you very much for the help!

Best Regards,

Chenhui

[v-qiaqi-msft]

Hi @Chenhui,

Yes, this should work if you split your table into 2 separate table that less than 2000 records. However you should note that you should write two Filter() function for 2 Gallery because you have split one table into 2 separate tables.

You could check the test result on my side, which work for my data source that less than 2000 records.

[Chenhui]

Hello @v-qiaqi-msft,

Ive tried to use half of my table with less than 2000 cells, however when i input into the textinputs, the gallery is a bit faulty eg. i key in 48 into the product code search bar, it displays numbers without 48 inside.

Best Regards,

Chenhui

Best Regards,

Chenhui

[Chenhui]

Hello @v-qiaqi-msft ,

Apologies for the multiple messages. Upon further testing i realise the gallery with the filter doesnt respond much to the textinputs. when i keyed in my text input for the market the gallery did not change, where as for the product code, some of the rows were filtered out but not all were filtered correctly. And now the whole filter is not working, really sorry for the trouble, i am not sure whats wrong. Thank you again for the help!

Best Regards,

Chenhui

[v-qiaqi-msft]

Hi @Chenhui,

The Filter() formula will filter records meet either the search box1 or search box2, so you should re-check your condition that if you want to filter the records meet the one of the conditions or both of the conditions.

You could check the screenshot in my scenario, I filter the records related to the Title column or Price column, which means that all the records meet the Title key words or Price key words could display in the Gallery.

If I replace the operator "||" with "&&", it will display the records that each record will meet the Title key words and the Price key words. Note that the Title column and the Price column are both from the same list.

Hope this could help you understand the search logic.

For more detail about the logic operator, please check the following doc:

And, Or, and Not functions in Power Apps - Power Apps | Microsoft Docs

[v-qiaqi-msft]

Hi @Chenhui,

That's OK.

I'm sorry I have to leave work soon, I won't start until 9am BST, so if you have any questions in the meantime you can just leave a message. I'll get back to you after I get to work.