cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Issue with Search and Filter with dropbox by collection

I have a simple dropdpwn looking up a collection made from the Onvisible in Screen.  The below function works for getting the dropbox fine.

//For All
If(Dropdown2.Selected.Result="All",
GroupStudents,


//This is for Selected
Dropdown2.Selected.Result<>"All", 
Filter(GroupStudents, GroupTitle = Dropdown2.Selected.Result)))

 

When I introduce the Search box I cant get it to work

filtererror.png

Its something simple I'm sure.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @leyburn19 

 

Thank you for your awesome response.  Actually using your method I got an error for some reason.

filtererror.png
But instead of using Search I use you method of using StartsWith I have success with the following and don't have to use the label 🙂

If(Dropdown2.Selected.Result="All",GroupStudents,Filter(GroupStudents, GroupTitle = Dropdown2.Selected.Result And StartsWith(Title, TextSearchBox3.Text)))

 

View solution in original post

7 REPLIES 7
Memorable Member
Memorable Member

When I use dropdowns to filter I use a label for the dropdown results,  then filter on the label.  I hide the label.  Example:

 

label text = If(Dropdown?.Selected.Result="All","",Dropdown?.Selected.Result)

 

This either makes the label blank if it is All or puts the result in

 

So now if I add a search box as well I do a filter like

 

Filter(Source,Startswith(Colum,Label?.Text) And StartsWith(Column, TextInput?.Text))

 

This filter finds all records based on the dropdown and the textinput.  If the dropdown is All and the textinput blank is shows all. If the dropdown is a selection and the textinput is blank the result is all the dropdown matches etc and so on

Hi @leyburn19 

 

Thank you for your awesome response.  Actually using your method I got an error for some reason.

filtererror.png
But instead of using Search I use you method of using StartsWith I have success with the following and don't have to use the label 🙂

If(Dropdown2.Selected.Result="All",GroupStudents,Filter(GroupStudents, GroupTitle = Dropdown2.Selected.Result And StartsWith(Title, TextSearchBox3.Text)))

 

View solution in original post

Your error was becuse you use the = instead of the , . You can still use startswith. See below. I use the Label as I often have the dropdown to create the blank for the startswith but still have the all. I hav ebeen able to filter on 5 dropdowns at once using this process

 

 

If(Dropdown2.Selected.Result="All",GroupStudents,Filter(GroupStudents, tartsWith(GroupTitle,Dropdown2.Selected.Result) And StartsWith(Title, TextSearchBox3.Text)))

Hi @leyburn19 

Yes you are right, thank you.  It works now with the All as well.  using this code how can I bring in a second Dropdown to say lookup the "StudentSurname"

 

The issue with student names would be Delegation in a lookup. if there are more that 500 or extended 2000 all names would not be searchable.  It would also not be a good use of resources unless you put them in a collection first.  The way I search for names is just add another text box.  Only issue is the delegation as well which if more than 2000 you cannot use the 'in' operator only Startswith.

 

So add a text box for the surname and add 'And StartsWith(Surname,Textinput?.Text) to the filter

 

If delegation is no an issue the use And Surname in TextInput?.Text

 

If the textbox is blank it does not impact on the other search boxes until you add text

 

 

Notes - The first one you must start spelling with correct letter.  The second one.  If there are blanks it will automatically remove those from the gallery before the search but allows you find any string in the list

 

 

Hi @leyburn19 

When I select a group from the dropdown they will always filter to less than 500 (as our groups are not that big) so I can actually use the name search in the searchtext box as I have that set to search student Title.  I just wanted to know how the function would be written.  You said you have down multiple dropdowns...

 

 

Yes I have 29 Sites inputting 10000 orders per month  so Large lists to filter.  As a part of the orders the list will have site name,  Order group, sub group, surname etc.

 

So I have teh filter to teh gallery capable of filtering on dropdowns based on site, order group, sub category, surname,  order ID and two other group IDs based on special bulk orders etc.  See image below.

 

I have a filter that separates the IDs from the rest as this will always be unique searches,   then act on the rest together or as one.  Note the Alls,  I have hidden labels for these searches.  So This Gallery filters on currently 25000 records in an SP list.  there are no delegation issues at any time and it is very responsive..  

 

demo.PNG

 

Example filter for the above is:

 
If(
    IsNumeric(Value(TextInput6.Text)),
    Filter(
        ClothingWork,
        ID = Value(TextInput6.Text) || JobID = Value(TextInput6.Text)
    ),
    If(
        IsNumeric(Value(TextInput6_2.Text)),
        Filter(
            ClothingWork,
            MasterIDContractor = Value(TextInput6_2.Text)
        ),
        If(
            IsNumeric(Value(TextInput6_4.Text)),
            Filter(
                ClothingWork,
                BOrderID = Value(TextInput6_4.Text)
            ),
            Filter(
                ClothingWork,
                StartsWith(
                    SURNAME,
                    TextInput6_3.Text
                ) And StartsWith(
                    STATUS,
                    Label23.Text
                ) And StartsWith(
                    STORE,
                    TextInput15.Text
                ) And StartsWith(
                    'JOB FAMILY',
                    TextInput16.Text
                ) And StartsWith(
                    'JOB TASK',
                    TextInput17.Text
                )
            )
        )
    )
)

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (24,841)