Combine data shaping functions "Search" and "Filter"

(0) Share

Report

Posted on by mose_brancato

Hello Guys,

I'm following this tutorial to learn how to shape data in canvas app.

Using the "Filter" function, this command works correctly:

Filter(Items, Origin = "China")

showing the two items from Cina.

Using the Search function:

Search(Items, SearchInput.Text, "Name", "Description")

also this command give me the right results.

Combining the two functions in this way:

Search(Filter(Items, Origin = "China"), SearchInput.Text, "Name", "Description")

if I don't write any text in "SearchInput" control, I can see the rows with Origin = Cina, but whatever string I try to find in the items showed, the result is null.
I am attaching the datasource excel file, and the datalog of the monitor, hoping it will be useful.

Please help me, because I don't understand where is the error.

Categories:

Building Power Apps

shape-data-learn.zip

PowerAppsTraceEve...

I have the same question (0)

All responses (6)

Answers (1)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Your syntax looks correct. If you put something in the SearchInput.Text box it should show you records where that texit is in name or description and where the origin is China. It won't show you records where the records don't originate in China.

Was this reply helpful? Yes No
mose_brancato 14 on at

Like (0)

Report
Thanks for your reply Pstork.
Let me put some screenshots to be more clear.
This is the excel file "shape-data-learn.xlsx" used for Item datasource:
shape-data-learn.xlsx

This below is the canvas app I've created, where I select the "Origin" in the dropdown control on the left. You can see also the formula writed for the vertical gallery item.
If I select "Cina" in the dropdown, the results is:
Filter by Origin = "Cina"

If I select "South Africa" in the dropdown, the results is:
filter by Origin = "South Africa"

So I think the filter function in the formula is working well.

Now I try to search for "Rose" in the items where Origin = "Cina". NOTHING!
Search for "Rose" in Items where Origin = "Cina"

If try to search for "Tea" in the items where Origin = "Cina". NOTHING!
Search for "Tea" in Items where Origin = "Cina"

Then I modified the formula in the gallery to use only the Search function:
Search(Items, "", "Name", "Description")
Search(Items, "", "Name", "Description")

BUT whatever string I try to search, the result is null. For example:
Search(Items, "Tea", "Name", "Description")

So the issue is in the Search function I think, but I don't understand where is the error....

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Let me try to duplicate the issue you are seeing. As I said everything looks like it should work.

Was this reply helpful? Yes No
mose_brancato 14 on at

Like (0)

Report

Thank you very much!🙂

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
Ok, I found you a solution. The syntax you are using is correct if you are using anything other than Excel. But Excel seems to have a problem identifying that the columns you want to search are text columns. But if you use them in some way and designate them as text then it will work. Try this formula.

Search(AddColumns(Filter(Table1,Origin = Dropdown1.Selected.Value),"Search1",Text(Name),"Search2",Text(Description)), SearchInput.Text,"Name","Description")

Something about designating them as Text in the AddColumns makes the Search recognize them as Text and Searchable. You could also use Search1 and Search2 after you have added them, but that doesn't seem to be required.

Was this reply helpful? Yes No
Verified answer

mose_brancato 14 on at

Like (0)

Report
Hello Pstork1,
many thanks for your help and the time you've spent on this issue. If I copy and paste your formula in the vertical gallery item, it gives me error:

In the mean time I've found a workaround that consists in two steps:
1) I create a collection "ItemsFiltered" when the Origin dropdown change:
ClearCollect(ItemsFilterd, Filter(Items,Origin = Dropdown1.SelectedText.Value))

2) I use the collection in the vertical gallery search function:
Search(ItemsFilterd,SearchInput.Text,"Name","Description")

I'm not a powerapps expert, but I have the impression that it has bugs.
Thanks again for the help.

Was this reply helpful? Yes No