cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tianaranjo
Level 8

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
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filter/Search Function

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

tianaranjo
Level 8

Re: Filter/Search Function

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
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filter/Search Function

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.

 

 

tianaranjo
Level 8

Re: Filter/Search Function

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

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filter/Search Function

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")
tianaranjo
Level 8

Re: Filter/Search Function

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

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filter/Search Function

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

tianaranjo
Level 8

Re: Filter/Search Function

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

tianaranjo
Level 8

Re: Filter/Search Function

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

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filter/Search Function

Glad to see it working now!

tianaranjo
Level 8

Re: Filter/Search Function

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

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 169 members 5,836 guests
Please welcome our newest community members: