cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jlarsen81
Helper I
Helper I

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

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
Community Champion
Community Champion


@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
Community Champion
Community Champion

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

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
Community Champion
Community Champion

@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
Helper I
Helper I

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
Community Champion
Community Champion

 

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

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
Resolver II
Resolver II

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
Community Champion
Community Champion

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (49,982)