Answered

Filter/Search Function

(0) Share

Report

Posted on by tianaranjo

497

I have a search screen that is currently set to search collections based off an Excel spreadsheet. The spreadsheet has numerous columns. The search screen has both dropdowns and text input search fields. I would like the user to be able to search on whatever search input selections are made. For example, they might search on State and Manager or State only (dropdowns) -- this works.

Or test input a DOT #, (this works).

Or a DOT # (text input) and Status (dropdown) -- this DOES NOT work.

Nor does two text inputs.

My formula for OnSelect is:

ClearCollect(
colSelectedProject,
Search(
Filter(colAllProjects,

Prj_x0020_Ctgry = inputProjCategory.Selected.Title || inputProjCategory.Selected.Title = " *All",
State = inputState.Selected.Title || inputState.Selected.Title = " *All",
DIV = inputDivision.Selected.Title || inputDivision.Selected.Title = " *All",
Sub_x0020_Div = inputSubdivision.Selected.Title || inputSubdivision.Selected.Title = " *All",
Status = inputStatus.Selected.Result || inputStatus.Selected.Result = " *All"),

inputDOT.Text, "Dot_x0020_Num"

)

)

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (2)

Sort by

CarlosFigueira Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

What error do you get? If you click the expression, does something show up as an error, or the search expression simply doesn't return any rows?

In the attached app I have an expression similar to yours. Notice that since the values in the 'Dot #' column were numbers, then the Search function could not work on them - it only works for text columns, so I had to add a new column using the Text function to convert that column to text.

ForumThread122081...

Was this reply helpful? Yes No
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

@CarlosFigueira - Thanks so much for your reply and sharing. I cannot import the file into PowerApps. I will try again soon. Would it be too much to ask for you to send the info here?

Again, much appreciated.

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Here's the formula that is used in my app. Notice that the column names are a little different than yours, but the idea is the same.

Search( Filter( AddColumns(colAllProjects, "Dot_x0020_Num", Text('Dot #')), 'Prj Ctgry' = inputProjCategory.Selected.Title || inputProjCategory.Selected.Title = " *All", State = inputState.Selected.Title || inputState.Selected.Title = " *All", DIV = inputDivision.Selected.Title || inputDivision.Selected.Title = " *All", 'Sub Div' = inputSubdivision.Selected.Title || inputSubdivision.Selected.Title = " *All", Status = inputStatus.Selected.Result || inputStatus.Selected.Result = " *All"), inputDOT.Text, "Dot_x0020_Num")

Was this reply helpful? Yes No
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

@CarlosFigueira

Carlos - the formula in the last works fine in my app as long as the inputDOT.Text is the only text input I add to the formula. My issues is when I start adding the other text input fields, see ex:

ClearCollect(
colSelectedProject,
Search(
Filter(
colAllProjects,

Prj_x0020_Ctgry = inputProjCategory.Selected.Title || inputProjCategory.Selected.Title = " *All",
State = inputState.Selected.Title || inputState.Selected.Title = " *All",
DIV = inputDivision.Selected.Title || inputDivision.Selected.Title = " *All",
Sub_x0020_Div = inputSubdivision.Selected.Title || inputSubdivision.Selected.Title = " *All",
Status = inputStatus.Selected.Result || inputStatus.Selected.Result = " *All"),

inputDOT.Text, "Dot_x0020_Num",
inputProjSeq.Text, "Prj_x0020_Seq_x0020_No",
inputLineSeg.Text, "Line_x0020_Seg"
)

Was this reply helpful? Yes No
Verified answer

CarlosFigueira Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
The Search function works by searching for one single search string value over multiple columns - see the documentation for more information. Since you want to use different search strings for different columns, then Filter (using the Find function) is probably more appropriate:

ClearCollect( colSelectedProject, Filter( colAllProjects, Prj_x0020_Ctgry = inputProjCategory.Selected.Title || inputProjCategory.Selected.Title = " *All", State = inputState.Selected.Title || inputState.Selected.Title = " *All", DIV = inputDivision.Selected.Title || inputDivision.Selected.Title = " *All", Sub_x0020_Div = inputSubdivision.Selected.Title || inputSubdivision.Selected.Title = " *All", Status = inputStatus.Selected.Result || inputStatus.Selected.Result = " *All", Not(IsBlank(Find(inputDOT.Text, Dot_x0020_Num))), Not(IsBlank(Find(inputProjSeq.Text, Prj_x0020_Seq_x0020_No))), Not(IsBlank(Find(inputLineSeg.Text, Line_x0020_Seg))) ) )

Was this reply helpful? Yes No
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

@CarlosFigueira
I certainly appreciate all the time and effort you have been putting in to help on this. Unfortunately, that did not work. It will search on the text box OR the dropdown. It will not produce results if I select something from a dropdown AND have something in a text input. Did it work on your end?

Was this reply helpful? Yes No
Verified answer

tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

Here it is: this works! @CarlosFigueira - You were a HUGE help; thank you!

ClearCollect(colSelectedProject,
Filter(colAllProjects,
Prj_x0020_Ctgry = inputProjCategory.Selected.Title || inputProjCategory.Selected.Title = " *All",
State = inputState.Selected.Title || inputState.Selected.Title = " *All",
DIV = inputDivision.Selected.Title || inputDivision.Selected.Title = " *All",
Sub_x0020_Div = inputSubdivision.Selected.Title || inputSubdivision.Selected.Title = " *All",
Status = inputStatus.Selected.Result || inputStatus.Selected.Result = " *All" ||
Not(IsBlank(Find(inputDOT.Text, Dot_x0020_Num))) ||
Not(IsBlank(Find(inputProjSeq.Text, Text(Prj_x0020_Seq_x0020_No)))
)
)
)

Was this reply helpful? Yes No
CarlosFigueira Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Glad to see it working now!

Was this reply helpful? Yes No
tianaranjo 497 on at

Like
a
(0)

Report
Copy link

Link copied!

@CarlosFigueira - Seems I spoke to soon. Strangely, this will work ONCE and then it stops working and will go back to only filtering on the dropdowns. Have you ever experienced that issue?

Was this reply helpful? Yes No