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

Only show custom date range on load

So, I'm very very new to PowerApps and thought that you all might be able to push me in the right direction.  I have created an app to access a sharepoint calendar.  I have gotten it pretty close to complete, but found that I don't want to show dates way off in the future or the past.  So, I would need to filter it when the app loads to only show from the current date until 2 weeks from today.  Currently, my function is as shown here:

 

SortByColumns(Filter('State Calendar', StartsWith(Title, TextSearchBox1.Text)), "EventDate", If(SortDescending1, Ascending, Descending))

 

So, how can I adjust that to only show the current day +14 days?  seems like it would be fairly easy to do, but i'm having a lot of trouble finding the solution.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Only show custom date range on load

Hi @JiX


The problem here is that the query isn't delegable. What this means is that PowerApps retrieves 500 rows and filters only those records that match your date criteria. That's why you only see 2 results, rather than 10.

On checking the documentation, the only delegable operator with SharePoint is the = operator (which is unfortunate).

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

There are a couple of things that might help.

First, you can increase the non-delegable row limit from the default value of 500, up to 2000.

Second, if EventDate is a date column (rather than a datetime column), you can try to create a filter using just the = operator.

Filter('State Calendar',
        EventDate = Today()
        Or EventDate = DateAdd(Today(), -1, Days)
        Or EventDate = DateAdd(Today(), -2, Days)
        Or EventDate = DateAdd(Today(), -3, Days)
        Or EventDate = DateAdd(Today(), -4, Days)
        Or EventDate = DateAdd(Today(), -5, Days)
        Or EventDate = DateAdd(Today(), -6, Days)
        Or EventDate = DateAdd(Today(), -7, Days)
        Or EventDate = DateAdd(Today(), -8, Days)
        Or EventDate = DateAdd(Today(), -9, Days)
        Or EventDate = DateAdd(Today(), -10, Days)
        Or EventDate = DateAdd(Today(), -11, Days)
        Or EventDate = DateAdd(Today(), -12, Days)
        Or EventDate = DateAdd(Today(), -13, Days)
        Or EventDate = DateAdd(Today(), -14, Days)
)


Hope that's of some help.


View solution in original post

6 REPLIES 6
JiX
Level: Powered On

Re: Only show custom date range on load

ok, so i was able to 'sort of' make it work with this:

 

Filter('State Calendar', DateValue(Text(EventDate),"en-US") >= DateValue(Text(Today()),"en-US") And DateValue(Text(EndDate),"en-US") <= DateValue(Text(Today() + 14),"en-US"))

 

but for some reason, it isn't showing all of the calendar events between those days.  it only pulled up 2 of the events when there should have been about 10.  little lost here.

 

 

 

**Update**

So, i see that there's an error showing:  "part of this filter can not be evaluated remotely due to service limitations" and that the local evaluation may not produce all results.  and it has the 'EventDate' underlined.  So, I have to assume that the filter can't connect to that field or something??  If i use that field in the form, it works fine though.  It's only when i'm using the reference in the filter.  weird.

Super User
Super User

Re: Only show custom date range on load

Hi @JiX

 

What if you try the following:

 

Filter('State Calendar', 
        EventDate >= Today() And EventDate <= DateAdd(Today(), -14, Days)
       )

Does that show more accurate results?

JiX
Level: Powered On

Re: Only show custom date range on load

didn't work.  did the same thing that my second entry was doing (only showing 2 items).  After a bunch of trial and error, it looks like the 'EventDate' column can't be referenced correctly unless it is inside of a function that is located within the gallery itself.  anything outside of the gallery doesn't load it properly.  Problem is that I can't figure out why.  Thank you for the filter information though.  that's a much cleaner code then what I had.  Still doesn't work right, but much easier to manage.

Super User
Super User

Re: Only show custom date range on load

Hi @JiX


The problem here is that the query isn't delegable. What this means is that PowerApps retrieves 500 rows and filters only those records that match your date criteria. That's why you only see 2 results, rather than 10.

On checking the documentation, the only delegable operator with SharePoint is the = operator (which is unfortunate).

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

There are a couple of things that might help.

First, you can increase the non-delegable row limit from the default value of 500, up to 2000.

Second, if EventDate is a date column (rather than a datetime column), you can try to create a filter using just the = operator.

Filter('State Calendar',
        EventDate = Today()
        Or EventDate = DateAdd(Today(), -1, Days)
        Or EventDate = DateAdd(Today(), -2, Days)
        Or EventDate = DateAdd(Today(), -3, Days)
        Or EventDate = DateAdd(Today(), -4, Days)
        Or EventDate = DateAdd(Today(), -5, Days)
        Or EventDate = DateAdd(Today(), -6, Days)
        Or EventDate = DateAdd(Today(), -7, Days)
        Or EventDate = DateAdd(Today(), -8, Days)
        Or EventDate = DateAdd(Today(), -9, Days)
        Or EventDate = DateAdd(Today(), -10, Days)
        Or EventDate = DateAdd(Today(), -11, Days)
        Or EventDate = DateAdd(Today(), -12, Days)
        Or EventDate = DateAdd(Today(), -13, Days)
        Or EventDate = DateAdd(Today(), -14, Days)
)


Hope that's of some help.


View solution in original post

JiX
Level: Powered On

Re: Only show custom date range on load

THAT WORKED!!!!  changed the limit to 2000 and it suddenly worked!  THANK YOU!!!

JiX
Level: Powered On

Re: Only show custom date range on load

OK, which brings me to my next problem.  lol  how can i filter by two things, separately?  example:  my galery has a search field at the top and my current items function is below. but, when i search, it doesn't find anything.  any ideas on how to get the search field to function while still filtering the dates?

 

SortByColumns(Filter('State Calendar', StartsWith(Title, TextSearchBox1.Text) && EventDate >= Today() And EventDate <= DateAdd(Today(), 14, Days) ), "EventDate", If(SortDescending1, Ascending, Descending))

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

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
Users online (5,192)