cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tianaranjo
Continued Contributor
Continued Contributor

Filter/Search Function

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"


)

)

2 ACCEPTED SOLUTIONS

Accepted Solutions

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)))
    )
)

View solution in original post

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)))

)
)
)

View solution in original post

9 REPLIES 9
CarlosFigueira
Power Apps
Power Apps

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.

 

 

@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.

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")

@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"
)

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)))
    )
)

View solution in original post

@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?

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)))

)
)
)

View solution in original post

Glad to see it working now!

@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?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,555)