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

Re: Filter a list in PowerApps by date

Hi @v-xida-msft

The Startdate column is a date type and the date format in the app is "yyyy-mm-dd" which is the Swedish format. I guess that date format of the PowerApps app may depend on what language ths operating system is set to, right?

 

I have tried the code you provided for the Gallery Items and then, like you state, I end up with a delegation warning concerning large data sets. I tried ignoring it, published the app again and ran it and the list of items showed up empty (the list has six items).

 

Could this be related to the date format I'm using?

Community Support Team
Community Support Team

Re: Filter a list in PowerApps by date

Hi @Magnus,

Just an update -- Please modify your formula within the Items property of the Gallery to following:

SortByColumns(
     Filter(
            'Events VU'; 
             StartsWith(Header; TextSearchBox1.Text); 
             Value(Text(Startdate, "yyyymmdd")) > Value(Text(Today()-8,"yyyymmdd")) /* <-- Modify formula here */
     ); 
     "Title"; 
     If(SortDescending1; Descending; Ascending)
)

then check if the issue is solved.

In addition, if you want to get rid of the Delegation warning issue within your app, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, 'Events VU')

Set the Items property of the Gallery to following:

SortByColumns(
Filter(
RecordsCollection;
StartsWith(Header; TextSearchBox1.Text);
Value(Text(Startdate, "yyyymmdd")) > Value(Text(Today()-8,"yyyymmdd")) /* <-- Modify formula here */
);
"Title";
If(SortDescending1; Descending; Ascending)
)

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Magnus
Level: Powered On

Re: Filter a list in PowerApps by date

Hi @v-xida-msft!

I tried the first segment of code that you provided in your last post. I get the delegation warning but I also see error boxes. Seems like the data source cannot be found? Se screenshot below:

screenshot.PNG

It feels like I'm doing some basic mistake here...

iamlee
Level 8

Re: Filter a list in PowerApps by date

Here's how I pulled it off. https://powerusers.microsoft.com/t5/General-Discussion/Filter-Date-in-a-Gallery-by-DatePicker/m-p/19... in my case though, I only display items selected in the date picker. Once you use > or < you then get delegation warning and if you're going to get records > 2000 eventually based avoid delegation warning as it's a headache when you hit that point. your use case might be different, but in my case I managed my users expectation and it worked.
Magnus
Level: Powered On

Re: Filter a list in PowerApps by date

Hi @iamlee Thanks, but I'm looking to filter the list when the app loads, without a date picker in the gallery view. The app should requires minimal interaction from the user to get an overview of the active items.
iamlee
Level 8

Re: Filter a list in PowerApps by date

Hi - I came across this same problem now and I used the same approach I mentioned but I converted the current date to a number and added a number column in my SharePoint List. I managed to avoid delegation. You'll need to declare a Cut-Off Date on start of the application. 

 

The increment is in milliseconds, 86,400,000 per day. It's quite different to how Excel converts a date to a number.

 

 

Magnus
Level: Powered On

Re: Filter a list in PowerApps by date

Hi @iamlee

Could you please post the code you used to get this to work?

iamlee
Level 8

Re: Filter a list in PowerApps by date

On my main screen, I set this on the 'OnStart' properties. 

//I want to display only the last 7 days.

Set(CutOffDate,Value(Today() - 7))

 

On my SharePoint List, I added a Number column (I'll call it DateStamp. On the DateStamp data card, I put a Label control and on the Text property I put Value(Today()) (If you have a date control you can also use that, the formula would be Value(DateControl.SelectedDate).

 

My Gallery would have this formula then: 

Filter(
Table,
DateStamp >= CutOffDate
)

 

It filters based on the number column called DateStamp and because it's a number it doesn't give a delegation warning. 

 

 

PytByt
Level: Powered On

Re: Filter a list in PowerApps by date

Hi @Drrickryp 

 

i want to sum up the modified items in my list for more than a week, and i use this formula:
Sum('Candidate List', Modified > Created ()7)

 

can you help me with the right formula with my needs?

 

thanks,

pytbyt

Magnus
Level: Powered On

Re: Filter a list in PowerApps by date

I took on another approach to my filter problem, since I didn't get it to work otherwise.

 

I ended up making a list which shows all entries from today and forward. To accomplish this without a filter to hide older entries, I created a Flow which exports every new entry to an Excel file (to maintain history) and then removes items older than todays date from the Sharepoint list.

 

Not the best solution but it works for my current needs.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 36 members 4,084 guests
Please welcome our newest community members: