cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Filtering a Gallery With Two DatePickers

Good afternoon. I'm trying to sort and filter a gallery using two date pickers. Here's how it would work:

 

1. User selects a date on  DatePicker1.

2. User selects a date on DatePicker2.

3. Gallery displays all items that fall between those two selected dates. .

 

SortByColumns(Filter(InspectionsCache, NextInspect = 
DateStart.SelectedDate || DateEnd.SelectedDate),
"NextInspect",Descending)

Now, it seems that I'm going to have do some sort of If statement. Basically, "If DatePicker1 is selected, filter return date" Then another If statement that selects for  the other one. 

 

I know I can create a SQL view that does this, but I want to minimize the amount of connections that I already have to my app.  Thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
CMee
Level: Powered On

Re: Filtering a Gallery With Two DatePickers

See if the following works.

 

 

Sort(
	If(IsBlank(DateEnd.SelectedDate),
		If(IsBlank(DateStart),
			InspectionsCache,
			Filter(InspectionsCache,NextInspect >= DateStart.SelectedDate)
		),
		If(IsBlank(DateStart),
			Filter(InspectionsCache,NextInspect <= DateEnd.SelectedDate),
			Filter(InspectionsCache,NextInspect <= DateEnd.SelectedDate,NextInspect >= DateStart.SelectedDate)
		)
	),
"NextInspect",Descending)

 

Should be noted that >= and <= aren't delegable for SharePoint lists, but it seems you are using an SQL datasource so you should be all good.

 

View solution in original post

5 REPLIES 5
CMee
Level: Powered On

Re: Filtering a Gallery With Two DatePickers

See if the following works.

 

 

Sort(
	If(IsBlank(DateEnd.SelectedDate),
		If(IsBlank(DateStart),
			InspectionsCache,
			Filter(InspectionsCache,NextInspect >= DateStart.SelectedDate)
		),
		If(IsBlank(DateStart),
			Filter(InspectionsCache,NextInspect <= DateEnd.SelectedDate),
			Filter(InspectionsCache,NextInspect <= DateEnd.SelectedDate,NextInspect >= DateStart.SelectedDate)
		)
	),
"NextInspect",Descending)

 

Should be noted that >= and <= aren't delegable for SharePoint lists, but it seems you are using an SQL datasource so you should be all good.

 

View solution in original post

Re: Filtering a Gallery With Two DatePickers

Wow. That worked marvelously. Thank you!

 

 

Re: Filtering a Gallery With Two DatePickers

What if I just wanted to do between 0 and 14 days before Today()? This way I can identify anything that is past due on inspection. I've tried this formual using the one that you sent me earlier:

 

 

Sort(
If(IsBlank(OverdueGal.AllItems),
If(IsBlank(OverdueGal.AllItems),
(Filter(InspectionsCache,NextInspect <=Today())
),
If(IsBlank(OverdueGal.AllItems),
Filter(InspectionsCache,NextInspect <= Today()),
Filter(InspectionsCache, NextInspect <=Today()), NextInspect >= DateAdd(Today(), -10,Days)
))),
"NextInspect", Descending)

I then receive a "Circular reference between properties error". What's the difference between the two?

 

Brendon

 

CMee
Level: Powered On

Re: Filtering a Gallery With Two DatePickers

A circular reference occurs when an item is referencing itself to try and get a value. For instance:

LabelA.Text = LabelB.Text

LabelB.Text = LabelA.Text

The circle is fairly clear in this example, there is no external data source so the two labels just try to reference eachother ad infinitum.

 

In your example, I'm guessing that the formulas shown is in OverdueGal.Items. However, the formula references OverdueGal.Allitems - this is circular as the formula is referencing the result of the formula.

 

The forumla can be simplified quite significantly to remove all the if statements so that you are just left with a gallery of your overdue items. You were definitely on the right track with your alterations.This should work in OverdueGal.items:

 

Sort(
	Filter(InspectionsCache, NextInspect <=Today(), NextInspect >= DateAdd(Today(), -14,Days)),
 "NextInspect", Descending)
Highlighted

Re: Filtering a Gallery With Two DatePickers

I've been playing around with this formula a few more times. So far, I've been able to set a range that goes between Today() and 14 days from now. However, it'd like to include days that are before Today().  But, when I try this one, it only does just the next 14 days. It would be nice if PowerApps allowed a BETWEEN forumula that returns a table or record when a user enters two different dates. 

 

Note: this is way easier to do in SQL, but my SQL View isn't being updated after a patch has occurred. Hence, I have to do this work-around.

 

Sort(Filter(InspectionsCache, Location = InspectDropDown.Selected.Value, 
NextInspect >= Today(),
NextInspect <= DateAdd(Today(),15,Days),
NextInspect >DateAdd(Today(), -14, Days)),
NextInspect,Ascending)

Brendon

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,911)