Filter Gallery with dropdown

[Matt383]

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?

[ganeshsanap]

@Matt383 Considering you have a SharePoint column with name 'SharePoint List Column name' for which you want apply filter.

If this is a Single line of text column, try this: 

SortByColumns(Filter([@JobRegister], StartsWith(Company, TextSearchBox1.Text) && Dropdown1.Selected.Value= 'SharePoint List Column name'), "Date" , If(SortDescending1, Descending, Ascending))

If this is a Choice column, try this: 

SortByColumns(Filter([@JobRegister], StartsWith(Company, TextSearchBox1.Text) && Dropdown1.Selected.Value= 'SharePoint List Column name'.Value), "Date" , If(SortDescending1, Descending, Ascending))

---

Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍

Best Regards,

Ganesh Sanap

Blog site 

[Matt383]

Thanks Ganesh,

My sharepoint column is a choice value, so i used the suggestion below. Only problem i have now is when the dropdown value is "All" (Which is the default) Nothing in the gallery is shown. Only when you select 'Collected' or 'Completed' will the gallery appear. Just need the entire gallery shown unless the user selects "Collected or Completed' from the dropdown list.

SortByColumns(Filter([@JobRegister], StartsWith(Company, TextSearchBox1.Text) && Dropdown1.Selected.Value= Status.Value), "Date" , If(SortDescending1, Descending, Ascending))

[Anonymous]

Hi Matt

           Your existing code deals with everything except for 'All', so you now just need to add in some separate logic to deal with the 'All' case. Something like what is below would work I think.

SortByColumns(
        Filter([@JobRegister], 
            (StartsWith(Company, TextSearchBox1.Text) && Dropdown1.Selected.Value= 'SharePoint List Column name'.Value)) 
            || 
            (Dropdown1.Selected.Value= "All")), 
        "Date" , 
        If(SortDescending1, Descending, Ascending)
)

There is a guide for beginners on this topic here. You might  want to have a look at the end of this where it talks about filtering for all. 

[Matt383]

Hi David, I tried the suggestion below but showing invalid arguments.

SortByColumns( Filter([@JobRegister], (StartsWith(Company, TextSearchBox1.Text) && Dropdown1.Selected.Value= Status.Value)) || (Dropdown1.Selected.Value= "All")), "Date" , If(SortDescending1, Descending, Ascending) )

[Anonymous]

Can you please hit the "Format Text" button, and repost? It's a bit hard to dig through it when the code is in a big line ...

The image below shows where this is. 

[Anonymous]

I think that you might need to check where the brackets are in the code .. but it's easier to do with it in a nice format 🙂

[Matt383]

Hi Dave, It wont allow me to format the text as the option is greyed out. Only appears when there are no errors 

[Anonymous · Accepted solution]

OK, you should try playing with the code until you can reformat it. If the editor can't reformat it then the code has some structural issues with it.

To help you out I've put the code you had into a text editor and what I can see is that you do indeed have some structural issues. 

Your text, reformatted to show what I think you are trying to do is below.

The reason I have Highlighted the two sections is that in position 1 I think you are missing a bracket, and in position 2 I think you have an extra bracket. 

What I think you are after is this. 

SortByColumns
	(
		Filter([@JobRegister], 
		(
			( // enclose the statements used in the filter in brackets to be sure it's all OK

				// These two statmements go together
				(StartWith(Company, TextSearchBox1,Text) && DropDown1.Selected.Value = Status.Value)) 
				|| 

				// This statement sits by itself
				(Dropdown1.Selected.Value = "All")
			)
		), 
		"Date", 
		If(SortDescending, Descending, Ascending)
	)

Let me know how you go with this

[Matt383]

Thanks David, this is what worked for me. All good now

SortByColumns(
Filter(
[@JobRegister],
((StartsWith(
Company,
TextSearchBox1.Text
) && Dropdown1.Selected.Value = Status.Value)) || (Dropdown1.Selected.Value = "All")
),
"Date",
If(
SortDescending1,
Descending,
Ascending
)
)