cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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
Highlighted
Resolver I
Resolver I

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

6 REPLIES 6
Highlighted
Resolver I
Resolver I

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

Highlighted

Wow. That worked marvelously. Thank you!

 

 

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

 

Highlighted

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

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

Highlighted

Hii, @CMee 

 

its working Fine for SQL, but what will be the formula for SP Lists, can you help please?? 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,975)