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

Filtering a filter / select date range from SharePoint list

Hi all,

I have an app that displays a month-view calendar, with a gallery for each day. The items in each day's gallery are a filter of a SharePoint calendar list / events list based on the Event Date. For less than 500 (before upping the limit to 2000) items in the SharePoint calendar, this works fine with a filter like:

 

 

 

Text('Start Time',"[$-en-GB]dd/mm/yyyy") = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" )

 

 

 

...but of course, because it uses the date type column from the SharePoint list ('Start Time'), this isn't delegable.

 

I added a single line of text column (EventDateAsText) to the SharePoint calendar list, and populate this from the PowerApp when a new item is created, and changed the gallery.items filter to:

 

 

 

EventDateAsText = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" )

 

 

 

... and that works fine, more than 500 items are returned across 6 days in the calendar. As a test, I created 100 items for each of 6 days in the calendar. So, I see 6 day's galleries populated with 100 items in each.

 

What I now need to do is add more conditions to the filter; for example, only return items that have a 'Vertical' name value that is in the collection colSelectedVerts:

 

 

 

Vertical.Value in colSelectedVerts.Value

 

 

 

... but now, using the "in" operator sets the gallery.items call non-delegable. And I only see 500 results returned across 5 days (instead of 600 over 6 days)

 

I've tried, and seem to be successfully getting all results (i.e. more than 500) by using a filter inside a filter like this:

 

 

 

Filter(

    Filter(
        SPCalendarListTEST
        ,
        EventDateAsText = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" )
    )

,
Vertical.Value in colSelectedVerts.Value
)

 

 

 

What I was wondering, is if I first filter on the delegable "EventDateAsText = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" )" to return those matches to each particular day's gallery FIRST (so, ALL items that match the date per day, never more than 500), THEN, is the 2nd part of the filter "Vertical.Value in colSelectedVerts.Value" only filtering upon the results from the first part of the filter? If so then the non-delegable blue squiggle on the "in" operation in the second filter is all ok for this application.

 

So ... this filter for each day's gallery.items property results in a max of 500 items returned:

 

 

 

Filter(
    SPCalendarListTEST
,
    EventDateAsText = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" ) &&
    Vertical.Value in colSelectedVerts.Value
)

 

 

 

And this way (filter in a filter) seems to return ALL items, 600:

 

 

 

Filter(
    Filter(
        SPCalendarListTEST
    ,
        EventDateAsText = Text(Date( Value(Text(DatePicker2.SelectedDate, "[$-en-GB]yyyy")), Value(Text(DatePicker2.SelectedDate, "[$-en-GB]mm")), Value(MonthDayNumber.Text)),"[$-en-GB]dd/mm/yyyy" )
    )
,
    Vertical.Value in colSelectedVerts.Value
)

 

 

 

Is this a feasible solution? Or have I just made up some nonsense? It is very slow to respond with 600+ items. Perhaps there's a much smarter way to date filter via SharePoint (with delegation)?

 

Cheers,

Steve.

 

2 REPLIES 2
PowerAddict
Level 10

Re: Filtering a filter

The 2 step filtering as explained by you makes sense. That being said, if you don't want to deal with delegation at all, you could get all of the data in a collection and then work off of that.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)
stevegeall
Level: Powered On

Re: Filtering a filter

Thanks, @PowerAddict , I'm trying that now, bringing in all the data to a collection first.

 

I've used a bit of this post https://officepoweruser.com/how-to-collect-over-2000-records-in-powerapps/ and added a multi-collect on my app start and refresh button (multiple users, content updating often, need to refresh the data source to see additional items / changed items).

 

The SPCalendarListTEST data source has a 'Number' column in it named "EventDateAsInt" which is written to when a new item is added via the Power App, using the Patch function; it's in the format yyyymmdd, e.g. 20191119 (or 20,191,119 as shown in SharePoint). This is what allows the use of less-than and/or greater-than operators < > to only get dates that are older than or newer than another date. In my case, I want to keep the collection as small as possible for performance so as below I'm only returning items that have an EventDate that is newer than 1 month ago from today's date (no need to see any items that are older than this, so no need to bring them into the collection).

 

 

 

 

 

Set(todayDate,Today());
Set(oneMonthAgoDateValueAsInt,
    Value(
        Text(Year(DateAdd(todayDate, -1, Months)),"[$-en-GB]0000") &
        Text(Month(DateAdd(todayDate, -1, Months)),"[$-en-GB]00") &
        Text(Day(DateAdd(todayDate, -1, Months)),"[$-en-GB]00")
    )
);

// don't want items with an event date older than a month ago - try keep collection as small as possible
ClearCollect(colCalendarTESTdateFiltered,
    Filter(
        Sort(
            SPCalendarListTEST
        ,
            EventDateAsInt
        )
    ,
        EventDateAsInt>=oneMonthAgoDateValueAsInt
    )

);
If(CountRows(colCalendarTESTdateFiltered)>=500,
    Set(varEventDateAsIntForNextRun,Max(colCalendarTESTdateFiltered,EventDateAsInt));
    Notify("500 check varEventDateAsIntForNextRun="&varEventDateAsIntForNextRun);
    Collect(colCalendarTESTdateFiltered,
        Filter(
            Sort(
                SPCalendarListTEST
            ,
                EventDateAsInt
            )
        ,
            EventDateAsInt>varEventDateAsIntForNextRun
        )
    )
);
If(CountRows(colCalendarTESTdateFiltered)>=1000,
    Set(varEventDateAsIntForNextRun,Max(colCalendarTESTdateFiltered,EventDateAsInt));
    Notify("1000 check varEventDateAsIntForNextRun="&varEventDateAsIntForNextRun);
    Collect(colCalendarTESTdateFiltered,
        Filter(
            Sort(
                SPCalendarListTEST
            ,
                EventDateAsInt
            )
        ,
            EventDateAsInt>varEventDateAsIntForNextRun
        )
    )
)
// and so on until count greater than total future items in SharePoint calendar... (will change the limit from 500 to 2000 for less multi-collects required)

 

 

 

 

Found an issue with single digit days and months, easy fix to use the 'Text' function to add leading 0's for single-digit months and days when setting the old date and also when writing new values when creating the new items...

 

 

Value(
    Text(Year(DateAdd(todayDate, -1, Months)),"[$-en-GB]0000") &
    Text(Month(DateAdd(todayDate, -1, Months)),"[$-en-GB]00") &
    Text(Day(DateAdd(todayDate, -1, Months)),"[$-en-GB]00")
)

 

 

Now the EventDateAsInt will be 20191205 for 5th Dec 2019, rather than 2019125 which would break the date filtering/comparing/greater-than

 

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,945)