Hi, i have an app built directly from sharepoint. need to be able to filter gallery by "Completed", "Collected" or "All"
I have a dropdown input named dropdown1, the onselect of the gallery is currently,
SortByColumns(Filter([@JobRegister], StartsWith(Company, TextSearchBox1.Text)),"Date" , If(SortDescending1, Descending, Ascending))
I thought i would be able something like this to the existing formula above but getting a few errors
&& ( Dropdown1.Selected.Value = "all" || If( Dropdown1.Selected.Value="Completed" || If(Dropdown1.Selected.Value="Collected")))
Can you please assist?
Hi David, still no luck
My bad. Missing a bracket for the SortByColumns function.
Here is what it should be.
SortByColumns (
Filter([@JobRegister],
StartWith(Company, TextSearchBox1.Text),
((DropDown1.Selected.Value = Status.Value) || (Dropdown1.Selected.Value = "All")),
"Date",
If(SortDescending, Descending, Ascending)
)
)
My bad - missing the bracket for the SortByColumns. Here it is:
SortByColumns (
Filter([@JobRegister],
StartWith(Company, TextSearchBox1.Text),
((DropDown1.Selected.Value = Status.Value) || (Dropdown1.Selected.Value = "All")),
"Date",
If(SortDescending, Descending, Ascending)
)
)
Yes, you had a bracket in the wrong place. Try this.
SortByColumns ( Filter([@JobRegister],
StartWith(Company, TextSearchBox1.Text),
((DropDown1.Selected.Value = Status.Value) || (Dropdown1.Selected.Value = "All")),
"Date",
If(SortDescending, Descending, Ascending)
)
not sure what you mean with formula1, 2 etc write formula 1 in there? i keep getting invalid argument with each attempt
SortByColumns ( Filter([@JobRegister], (StartWith(Company, TextSearchBox1.Text),( (DropDown1.Selected.Value = Status.Value) || (Dropdown1.Selected.Value = "All") ) ), "Date", If(SortDescending, Descending, Ascending) )
Ok, so it looks like you need to break the StartsWith code into a separate parameter of the Filter function
e.g.The syntax of Filter is Filter(DataSource, Formula1 [, Formula2, ... ] )
And we've been jamming everything into Formula1.
I tried this and it works on a setup I have which is similar to what you describe, let me know how it goes for you
SortByColumns
(
Filter([@JobRegister],
(
//Formula 1
StartWith(Company, TextSearchBox1.Text),
//Formula 2
(
(DropDown1.Selected.Value = Status.Value) ||
(Dropdown1.Selected.Value = "All")
)
),
"Date",
If(SortDescending, Descending, Ascending)
)
Hi David, Well this works
SortByColumns(Filter([@JobRegister], StartsWith(Company, TextSearchBox1.Text)),"Date" , If(SortDescending1, Descending, Ascending))
Once i add this section in, the search box is non responsive
&& Dropdown1.Selected.Value = Status.Value)) || (Dropdown1.Selected.Value = "All")
)
That's weird. Try taking things out and see where it start working again. I'd recommend you take out the second statement initially like this
SortByColumns
(
Filter([@JobRegister],
(
( // enclose the statements used in the filter in brackets to be sure it's all OK
(StartWith(Company, TextSearchBox1,Text) && DropDown1.Selected.Value = Status.Value))
)
),
"Date",
If(SortDescending, Descending, Ascending)
)
If that doesn't result in the search box working again then simplify down the first statement as follows
SortByColumns
(
Filter([@JobRegister],
(
( // enclose the statements used in the filter in brackets to be sure it's all OK
(StartWith(Company, TextSearchBox1,Text)
)
),
"Date",
If(SortDescending, Descending, Ascending)
)
See what you need to do to get it working again, and then you'll know where the problem kicks in
Hi David, since ive made these changes, now the search box is not responsive. Before you could start typing into TextSearchBox1 and it would filter the gallery by company name. Now nothing happens when you type in. The dropdown list works as it should.
SortByColumns(
Filter(
[@JobRegister],
((StartsWith(
Company,
TextSearchBox1.Text
) && Dropdown1.Selected.Value = Status.Value)) || (Dropdown1.Selected.Value = "All")
),
"Date",
If(
SortDescending1,
Descending,
Ascending
)
)