Filter by DatePicker

[jlarsen81]

Hello all, I am wondering if anyone could help me out here. I would like to have the ability to filter my gallery using a DatePicker, but also retain the ability to show all dates in the gallery as well? Is this even possible?

Any help at all would be appreciated.

Thanks

[mr-dang]

If you want to filter a gallery sometimes, then you will need a condition in the filter.

First, this is what you would normally have, a simple Filter:

Filter(datasource,
	Date=DatePicker1.SelectedDate
)

If you only want the date filtered sometimes, then stick an If statement in the condition. Return "true" when you want everything. For this example, I tie the condition to a Toggle control, but you could use other controls or even a ContextVariable:

Filter(datasource,
	If(Toggle1.Value,Date=DatePicker1.SelectedDate,true)
)

This means, "When Toggle1 is flicked on, then the datasource will be filtered to show all records whose date equals the date set in DatePicker1. When Toggle1 is flicked off, then the datasource will show all records."

[jlarsen81]

Here is what I have come up with:

If(Toggle2.Value=true&&Dropdown1_5.Selected.Value="All Items",(SortByColumns(Search(Filter('[dbo].[LCS_CTOPS]',OrderStage="FAB"||OrderStage="Digitizing"||OrderStage="Template"||OrderStage="Installing"&&Temp_Date=DatePicker1.SelectedDate),SearchTextInput1.Text,"Area","CG_Number","CSRep","CustomerName","LOT","Material_Color","TrackName","OrderStage","OrderStatus","OrderType","CustomerName"),"Temp_Date",Ascending)),Toggle2.Value=false&&Dropdown1_5.Selected.Value="All Items",(SortByColumns(Search(Filter('[dbo].[LCS_CTOPS]',OrderStage="Fab"||OrderStage="Template"||OrderStage="Digitizing"||OrderStage="Installing")

The syntax is accepted, but both sides of the toggle result without filtering by the selected date, despite the addition filter on the true statement.

Any thoughts?

[mr-dang · Accepted solution]

@jlarsen81,

It looks like you inverted the location of the condition. I place my condition inside the Filter; you placed your condition outside the filter. Below is what you have, but I added some missing parentheses:

If(Toggle2.Value=true && Dropdown1_5.Selected.Value="All Items",
	(SortByColumns(
		Search(
			Filter('[dbo].[LCS_CTOPS]',
				OrderStage="FAB" || 
				OrderStage="Digitizing" ||
				OrderStage="Template" ||
				OrderStage="Installing" &&
				Temp_Date=DatePicker1.SelectedDate
			),
		SearchTextInput1.Text,
			"Area",
			"CG_Number",
			"CSRep",
			"CustomerName",
			"LOT",
			"Material_Color",
			"TrackName",
			"OrderStage",
			"OrderStatus",
			"OrderType",
			"CustomerName"
		),
	"Temp_Date",Ascending)
	),
	
	Toggle2.Value=false && Dropdown1_5.Selected.Value="All Items",
	(SortByColumns(
		Search(
			Filter('[dbo].[LCS_CTOPS]',
				OrderStage="Fab" || 
				OrderStage="Template" ||
				OrderStage="Digitizing" ||
				OrderStage="Installing"
			),
			[a part of the formula seems to be missing here]
		)
	)
	)
)

Below is what your formula would look like if the condition is inside the Filter instead (blue).

SortByColumns(
	Search(
		Filter('[dbo].[LCS_CTOPS]',
			If(Toggle2.Value=true && Dropdown1_5.Selected.Value="All Items",
				Temp_Date=DatePicker1.SelectedDate,
				true
				) &&
				(OrderStage="FAB" || 
				OrderStage="Digitizing" ||
				OrderStage="Template" ||
				OrderStage="Installing"
				)
		),
	SearchTextInput1.Text,
		"Area",
		"CG_Number",
		"CSRep",
		"CustomerName",
		"LOT",
		"Material_Color",
		"TrackName",
		"OrderStage",
		"OrderStatus",
		"OrderType",
		"CustomerName"
	),
"Temp_Date",Ascending
)

Important Note: notice the parentheses in red. They are needed so that the condition for the date is true with any of those options for OrderStage.

[jlarsen81]

Okay, I also have multiple conditions based off of the dropdown. I'm guessing that those would go between the red and black parentheses after OrderStage="Installing"? So as to still fall under the if statement?

[mr-dang]

The red area is the section where you will place any conditions:

Filter('[dbo].[LCS_CTOPS]',
	If(Toggle2.Value=true && Dropdown1_5.Selected.Value="All Items",
		Temp_Date=DatePicker1.SelectedDate,
		true
	) &&
	(OrderStage="FAB" || 
		OrderStage="Digitizing" ||
		OrderStage="Template" ||
		OrderStage="Installing"
	)
)

It is hard to tell you exactly where to finish your conditions for your Dropdown without knowing exactly what it would go with.

If the Dropdown will change what you want OrderStage to look for, then you will need to wrap the condition around the parentheses for OrderStage:

Filter('[dbo].[LCS_CTOPS]',
	If(Toggle2.Value=true && Dropdown1_5.Selected.Value="All Items",
		Temp_Date=DatePicker1.SelectedDate,
		true
	) &&
	If(Dropdown1_5.Selected.Value="[put your value here]",
		(OrderStage="FAB" || 
			OrderStage="Digitizing" ||
			OrderStage="Template" ||
			OrderStage="Installing"
		),
		[other result here]
	)
)

If the Dropdown will add more requirements to your conditions in addition to the things you already have, then you would append it to the end with And()/&&:

Filter('[dbo].[LCS_CTOPS]',
	If(Toggle2.Value=true && Dropdown1_5.Selected.Value="All Items",
		Temp_Date=DatePicker1.SelectedDate,
		true
	) &&
	(OrderStage="FAB" || 
		OrderStage="Digitizing" ||
		OrderStage="Template" ||
		OrderStage="Installing"
	) &&
	If(Dropdown1_5.Selected.Value="", [more conditions here])
)

[jlarsen81]

I do greatly appreciate your help on this, unfortunately nesting the if inside the filter creates a delegation problem. So I think I might have to wait until if/when if statements in a filter are delegable.

Again thank you!

[Nikhil2]

Hello,

Greetings for the day !

I have a small lag in my powerapps, where i am using datepicker as start and end date as "datePickerStart and datePickerEnd". What i need in my app is when an user selectes the start and end date i want to filter gallery based on the selection of the date as my scenario is submitted date where is used that column text code as "Thisitem.created" and uses the label name as "Body3". 

As below picture holds the button as apply, i have tried applying code to the button Onselect with all possibilties but ended up with null result. Can someone suggest me what kind of code will suit with this option?. 

[mr-dang · Accepted solution]

Hi @Nikhil2,

If I understand correctly:

• You have 2 date pickers, one for the start and one for the end.
• Those date pickers affect BrowseGallery1
• Your dropdown menu filters the items in BrowseGallery1 based on their Status.Value column

Below I've formatted your formula with spacing:

Filter(BrowseGallery1.AllItems,
    Status.Value="Approved",
    Status.Value="Open",
    Status.Value="Pending"
)

The problem with the formula in the dropdown menu is that each condition for Status.Value is required. The commas between each condition mean And. Since Status.Value can only be one of those options, I assume you might want to use Or instead:

Filter(BrowseGallery1.AllItems,
    Or(
        Status.Value="Approved",
        Status.Value="Open",
        Status.Value="Pending"
    )
)

The Or operator will check that any one or more of those conditions may be true, rather than all of them--which is not possible.

Please let me know if this helps.

Mr. Dang

______________ 

[Nikhil2]

Hello Mr.Dang,

Greetings for the day!

Foremost thank you for the reply, perhaps i found an workaround for filtering the status.value but the current problem which i have right now is quite enchanting. Below Pictures briefs that, i need to set the defaultdate as when user submits the first claim to the "Start Date" and "End Date" as today. But in the picture for the start date it doesn't work when there is a date of submitted yesturday and also for the "End Date" i have to force fully push the today's date to next day. Because if i set the "End Date" as today the filter to the gallery is not working.