Filter for SQL any words from the field

(0) Share

Report

Posted on by vladimir84

322

Hi,

Let's say I have a field that says: 'Fresh Orange Juice' and I want to list all the inventory that contains in the description field 'Fresh', 'Orange', or 'Juice'.

I managed to do the Filter( Concat( Split(field, " "), Result&", ") in SQLField) but that's not the solution, as it will still look for all three words together, now concatenated.

Anyone has any ideas? (Do not say Flow, I wanna see if there is a PowerApps way to do this)

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

eka24 20,923 on at

Like (0)

Report

Using search function will work for you.
I guess you have a Gallery. Insert a Textbox named SearchText on your screen where user will type what to search for.

Then on the Items property of the Gallery put;
Search(Datasource,SearchText.Text,FieldORcolumnName)

Change Datasource and FieldORcolumnName
To the actual names in the SQL table
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
vladimir84 322 on at

Like (0)

Report

Incredible! I completely ignored the Search function for the data filtering.
This has solved a huge Flow that I was constructing to extract all the combinations from SQL 😄
Thank you so much.

Was this reply helpful? Yes No
vladimir84 322 on at

Like (0)

Report

Unfortunately, it's not the solution after all 😞
it searches for the whole string, so from my example "Fresh Orange Juice" it will display only the fields that contain all of the words, and I need it to filter out any of the words (and most ideally, any combination of individual words).

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Kindly show a sample of the data in your table and the formula you used because that is what search function is supposed to solve.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No

vladimir84

322 on at

Like (0)

Report

I used exactly the same formula that you provided.

Due to the confidentiality of the data, I cannot provide you with a sample, but I believe that the example is very simple and very clear - there is a string composed of many words and I want PowerApps to filter out all the records in the table which contain any of the individual words inside that string.

so, if the string is: "Fresh Orange Juice", the target search result would be:

1	This is a fresh beverage	OK (1 word contained in the string found)
2	this is an orange	OK (1 word contained in the string found)
3	This is used to make juice	OK (1 word contained in the string found)
4	this juice is made out of fresh orange	OK (3 words contained in the string found)
5	Lime jelly	Ignored (no words correspond to the string)

Was this reply helpful? Yes No

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Hi @vladimir84

Take a look at https://powerusers.microsoft.com/t5/Building-Power-Apps/Searching-with-multiple-key-words/m-p/376698#M108986 and see if it does the trick.

Filter( CollectionName, IsBlank( Find( "0", Concat( Filter( Split( SearchBox.Text, " " //or any delimiter. ), !IsBlank(Result) ), If( TrimEnds(Result) in ColumnName1 || TrimEnds (Result) in ColumnName2, // etc. "1", "0" ) ) ) ) )

This formula works for any number of keywords and can search in one or more fields. The keywords can be separated by spaces or by any delimiter (comma, semicolon, etc). There is no native formula in PowerApps that will do what you would like, so a formula is necessary. It can be the Items property of a gallery or listbox.

Was this reply helpful? Yes No
vladimir84 322 on at

Like (0)

Report

Thank you @Drrickryp , I will try when I get a chance.
Btw, is it delegable?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@vladimir84

I don't know for sure but I test for delegation by lowering your advanced settings data row limit to 1 and seeing if the formula still works on a dataset. In any event, in SQL you can use a View for sequential batches based on the ID and combine them into a collection in PowerApps ref. https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/ and then apply the formula. As my dad used to tell me: "It seems that nothing worthwhile is ever easy."

Was this reply helpful? Yes No
vladimir84 322 on at

Like (0)

Report

Thank you again for the info. I will try and see. As for what your dad used to say:

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Was this reply helpful? Yes No