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.