Have a search bar on app, but want to allow the data to be filtered by check boxes.
E.g. search will search all results, but if 'open' is ticked then it will only search 'open' results.
Anyone offer assistance?
Sort( If( Checkbox.Value = true,
Filter(
'example',
StartsWith( Search_x0020_Name, "OPEN"
)
),
Search(
'example',
Upper(
TextBox.Text
),
Search_x0020_Name
)
),
Search_x0020_Name, Ascending
)
Pretty much what's currently in there.
Solved! Go to Solution.
If anyone stumbles across this looking for a solution, I ended up using a collection for all of the data & setting that to collect on page load.
Then I've set the checkboxes to filter the collection & the search to search the collection.
Works smoothly & it a lot quicker than filtering/searching straight from SQL.
Note: Collections only fetch 500 items at a time, so if you have more you'll have to look at splitting up your 'collection' and collecting seperately.
E.G. ClearCollect(Collection, Filter(*LOCATION*, *FilterForFirst500*)); *RepeatForNext500*
In Gallery:
Sort(
If(
CheckBox.Value = true && IsBlank(Search.Text),Filter(Collection,*FilterOptions*),
CheckBox2.Value = true && IsBlank(Search.Text),Filter(Collection,*FilterOptions*),
Not(IsBlank(SearchBox.Text)),
Search(
If(
CheckBox.Value = true, Filter(Collection,*FilterOptions*),
CheckBox2.Value = true, Filter(Collection,*FilterOptions*),
Collection),
Upper(SearchBox.Text),*SearchField*),
Filter(Collection, *RegularFilterOptions*),
*SortByField*, Ascending)
Probably a much nicer way of doing this but I'm not great with code, but it works so hey 🙂
Try this to keep from making spaghetti. Filter works well with text. (even tricking delegation into using current user if you pop it into a label first) Have a corresponding hidden text box for each check, and put the "true false" logic on the check box clicks to load up the text boxes with your search parse params. This way all you do is search based on text box contents and remove the if logic from your sort verb. Future maintenance will be much cleaner that way 😉
If anyone stumbles across this looking for a solution, I ended up using a collection for all of the data & setting that to collect on page load.
Then I've set the checkboxes to filter the collection & the search to search the collection.
Works smoothly & it a lot quicker than filtering/searching straight from SQL.
Note: Collections only fetch 500 items at a time, so if you have more you'll have to look at splitting up your 'collection' and collecting seperately.
E.G. ClearCollect(Collection, Filter(*LOCATION*, *FilterForFirst500*)); *RepeatForNext500*
In Gallery:
Sort(
If(
CheckBox.Value = true && IsBlank(Search.Text),Filter(Collection,*FilterOptions*),
CheckBox2.Value = true && IsBlank(Search.Text),Filter(Collection,*FilterOptions*),
Not(IsBlank(SearchBox.Text)),
Search(
If(
CheckBox.Value = true, Filter(Collection,*FilterOptions*),
CheckBox2.Value = true, Filter(Collection,*FilterOptions*),
Collection),
Upper(SearchBox.Text),*SearchField*),
Filter(Collection, *RegularFilterOptions*),
*SortByField*, Ascending)
Probably a much nicer way of doing this but I'm not great with code, but it works so hey 🙂
Thanks for the help and ideas. I came up with a little different way to maybe help the next person. I am also not a pro so there is probably more ways to simplify this. I had a scenario where I had 2 checkboxes and a search box. The "active" and "inactive" records were what I was filtering with the checkboxes.
Search(If(Checkbox2.Value = true && Checkbox2_1.Value = true, Filter('DATABASE/TABLE', active = true || active = false),If(Checkbox2.Value=true,Filter('DATABASE/TABLE', active = true) , If(Checkbox2_1.Value = true, Filter('DATABASE/TABLE', active = false)))),searchbox.Text, "COLUMN_NAME")
Order is important here (because you'll want to make sure all records show up when both checkmarks are checked) so I put that logic in the first if statement.
User | Count |
---|---|
125 | |
87 | |
86 | |
75 | |
69 |
User | Count |
---|---|
216 | |
181 | |
140 | |
97 | |
83 |