cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jlarsen81
Level: Powered On

Filter by DatePicker

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
mr-dang
Level 10

Re: Filter by DatePicker

@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.

Microsoft Employee
@8bitclassroom

View solution in original post

mr-dang
Level 10

Re: Filter by DatePicker

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

 

 

______________ 

Microsoft Employee
@8bitclassroom

View solution in original post

mr-dang
Level 10

Re: Filter by DatePicker


@Nikhil2 wrote:

 

Looks like below properties doesn't seem to work, because for the datepicker control do not have option "OnSelect" and for "OnVisible" doesn't seem to be working. If find alternative option would be great to implement.

 

1. The initial date actually fetching from edit form where i have setted the defaultdate as "today()".

2. Later i am calling the date in the gallery using the code to Text property Thisitem.created.

3. Now the datepicker play its role, Startdate sets it DefaultDate property to "ExpensestartDate" and for End Date"Today()"

4. For the button "Apply" i have defined the value to "OnSelect" property Set(ExpenseStartDate, datePickerStart.SelectedDate); Set(ExpenseEndDate, datePickerEnd.SelectedDate); Back()


 

If you de-select all the items on the screen, you will be able to access the OnVisible property of the screen. The OnVisible property will perform the actions when the user navigates to that screen. So you can use the OnVisible property to declare your ExpenseStartDate to the date of your choice:

 

Screen.OnVisible:

Set(ExpenseStartDate,date_that_you_want)

Let me know if this helps.

 

Mr. Dang

_____________

Microsoft Employee
@8bitclassroom

View solution in original post

15 REPLIES 15
mr-dang
Level 10

Re: Filter by DatePicker

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."

Microsoft Employee
@8bitclassroom
jlarsen81
Level: Powered On

Re: Filter by DatePicker

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
Level 10

Re: Filter by DatePicker

@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.

Microsoft Employee
@8bitclassroom

View solution in original post

jlarsen81
Level: Powered On

Re: Filter by DatePicker

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
Level 10

Re: Filter by DatePicker

 

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

 

Microsoft Employee
@8bitclassroom
jlarsen81
Level: Powered On

Re: Filter by DatePicker

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
Level 8

Re: Filter by DatePicker

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?. 

 

Capture1.PNGCapture2.PNG

mr-dang
Level 10

Re: Filter by DatePicker

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

 

 

______________ 

Microsoft Employee
@8bitclassroom

View solution in original post

Nikhil2
Level 8

Re: Filter by DatePicker

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.

 

New Cap.PNGNew Cap1.PNGNew Cap 2.PNGNew Cap 3.PNG

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,112)