cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Community Champion
Community Champion

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @rhiheu ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Resolver I
Resolver I

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @WarrenBelz, apologies for the slow reply. Unfortunately the Now() function is apparently only available in US Pacific Standard Time (https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/37231588-calculat...) which even I did an offset for my time zone I would need to change it each time we had Daylight Savings which isn't really feasible.

I'm not sure if there are any other delegable ways? Otherwise I would be happy with just filtering to only show results from the same day, if that can be delegable.

Highlighted
Community Champion
Community Champion

Re: Filter SharePoint list by within 12 hours of Item Creation time

HI @rhiheu ,

The only time you will have issues with UTC issues is if your SharePoint server is on a different time zone to your Power Apps facility.

You can use the TimeZoneOffset function (which returns the difference between the User's time and UTC) and use it to adjust the value of Now() when writing or the date/time when reading (depending on which way you want to manage it). I am not familiar with the daylight savings issue, but this is only 1 hour.

To answer your question on Delegation, now matter how you try to "parcel" data-based queries, they are not Delegable. There is a workaround with a "shadow" numeric field to reflect the date in yyyymmdd format

Value(Text(YourDate,"yyyymmdd"))

which can be set on the OnChange of your date field and is able to be Filtered with no delegation issues, by turning your search date into the same format

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Resolver I
Resolver I

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @WarrenBelz, sorry I'm getting quite confused. It's a Gallery (initially it was a Combo box but I changed it) that I'm trying to filter, not sure if you're thinking it's a date field? And I'm not trying to write back to that SharePoint list data source with this Power App, only read a filtered view of it. That SharePoint list is populated by a Graph API integration. So I can't get the Power App to write any functions back to the list to tell it the current time or date, as it would be too late by then for the filtered view. I think my only hope is for the app to filter based on what the SharePoint list already knows - its automatic 'Created' column, or a calculated column I add which populates by SharePoint upon item creation (from the Graph API integration). Before I was thinking you were suggesting SharePoint calculated column formulas to me, now I realise you are suggesting Power App formulas which I don't think I can use.

 

Sorry for the confusion, thanks for all your help though!

Highlighted
Community Champion
Community Champion

Re: Filter SharePoint list by within 12 hours of Item Creation time

No problem @rhiheu ,

You mentioned Now(), and I assumed you were dealing with a date field. Filtering or writing still has the same date/time conversion issues (you mentioned US time). I will go back to my original suggestion (using the "shadow" field on creation) of numeric yyyymmdd as this filters in a Delegable manner (20200602 is today).

Highlighted
Community Champion
Community Champion

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @rhiheu ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Resolver I
Resolver I

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @WarrenBelz, apologies, I haven't had time to try this yet. Once I do I'll make sure I reply here. Thanks 🙂

Highlighted
Resolver I
Resolver I

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @WarrenBelz, I've tried this out now but sorry I'm still not quite sure exactly what to do. This is what I've done so far:

1. I've created the calculated column in SharePoint (called 'CalcDay') with data type: number and this formula:

 

=TEXT(Created,"yyyymmdd")

 

It's correctly generating the numbers e.g. 20200613.

2. In my Power App's gallery Items property, I've tried:

 

Filter(SPListname, CalcDay=(Text(Today(),"yyyymmdd")))

 

Which it auto-corrects to:

 

Filter(SPListname, CalcDay=(Text(Today(),"[$-en-GB]yyyymmdd")))

 

But it's still coming up with a delegation warning.

CalcDay warning.jpg

So it doesn't like the calculated column, and I've learnt that I can't index calculated columns either. I'm not sure what to try next?

Highlighted
Community Champion
Community Champion

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @rhiheu ,

Different issue, same problem - SharePoint calculated fields are not delegable.

You need an actual numeric column in SharePoint populated each time you change the date then create a Variable for today in that format and test against that.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Filter SharePoint list by within 12 hours of Item Creation time

Hi @WarrenBelz, thanks, I've done that now and it's working well. To summarise:

I created a SharePoint column called 'CreatedDate' which is just a 'Single line of text' column, which has a default (calculated) value of:

=TEXT(TODAY(),"yyyymmdd")

It doesn't fill for old entries on the list, but entries added since adding the formula correctly add today's date. I didn't realise there was such a big difference from Power App's perspective between standard columns that have a default calculated value, and calculated columns.

I then used the following formula in the Power App gallery to filter on this, to only show items from today:

Filter(SPListName, CreatedDate=Text(Today(),"[$-en-GB]yyyymmdd")

Thanks again for all your help and perseverance with this.

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (5,598)