cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matt383
Helper V
Helper V

Filter Gallery with dropdown

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?

1 ACCEPTED SOLUTION

Accepted Solutions
DavidBraendler
Resolver II
Resolver II

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. 

 

DavidBraendler_1-1624589851245.png

 

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

 

 

View solution in original post

19 REPLIES 19
ganeshsanap
Super User
Super User

@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 

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

DavidBraendler
Resolver II
Resolver II

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.

DavidBraendler_0-1624585133935.png

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. 



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

 

Matt383_1-1624588199349.png

 

 

DavidBraendler
Resolver II
Resolver II

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. 

DavidBraendler_0-1624588826712.png

 

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 🙂

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

DavidBraendler
Resolver II
Resolver II

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. 

 

DavidBraendler_1-1624589851245.png

 

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

 

 

View solution in original post

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

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,180)