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

Proper way to collect against a SP date column?

Hello smart people,

 

I need some help. I'm having trouble with my formula in that I'm getting the delegation warning. I'm pulling data from a SharePoint list and using "in" in my Filter function, so it's not happy. 

 

I've read on here that the best way to circumvent this issue is to use ClearCollect and then leverage a date column, thereby pulling only the records > a certain date.

 

Since I'm new to this, I'm having trouble understanding how to implement this functionality. I have a few questions and since everybody has been so awesome in here before, hopefully I can get some help.

 

1) Should I create a date column in my SharePoint list, or use one of the default columns, like "Created"?

2) If I do create a date column, should that be formatted as a date column, or a text column?

3) How can I leverage this column in my filter formula? I'm having trouble understanding how to write something like, "Only pull records that are 1 year older than the current date." It's this dynamic date that's really confusing the heck out of me.

 

Hopefully this is simple. I'm still quite green at this and have never learned Excel, so most of these formulas are still a bit of a struggle.

 

Thanks! 🙂

 

Daniel

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Proper way to collect against a SP date column?

Hi @adaptivecruise,

 

1. Created column is invisible field in SharePoint list. If you want to see the created dates value in SharePoint list, then you should create a date time column. If you only want to use it in PowerApps, then you could use Created column directly.

2. If you only store date value in this column then it would be better to be a date time column.

3. To pull records that are 1 year older than the current date in Gallery control, you should use below formula on the Items property of Gallery:

Filter('List Name',Created>DateAdd(Now(),1,Years))

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Proper way to collect against a SP date column?

Hi @adaptivecruise,

 

1. Created column is invisible field in SharePoint list. If you want to see the created dates value in SharePoint list, then you should create a date time column. If you only want to use it in PowerApps, then you could use Created column directly.

2. If you only store date value in this column then it would be better to be a date time column.

3. To pull records that are 1 year older than the current date in Gallery control, you should use below formula on the Items property of Gallery:

Filter('List Name',Created>DateAdd(Now(),1,Years))

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

adaptivecruise
Level: Powered On

Re: Proper way to collect against a SP date column?

Mona, thanks a ton for the assistance.

I will give this a shot today and then either mark this as solved or provide some more feedback. 

Can't thank you enough for your help.

Daniel

adaptivecruise
Level: Powered On

Re: Proper way to collect against a SP date column?

Hello again @v-monli-msft I was wondering if you could help me with a slight re-working of the formula you provided.

 

I'd actually only like to pull the records for the last year...anything older than a year would not be in scope of the filter. So basically I'd be able to only pull the records that are within that 1 year range...anything older would fall off.

 

Does that make sense?

adaptivecruise
Level: Powered On

Re: Proper way to collect against a SP date column?

Never mind, @v-monli-msft! I figured it out with your help.

 

I changed the formula to read WorkflowStartDate>Today()-365 with WorkflowStartDate being the date column I created in SharePoint. So as long as the date is greater than today minus 365 days ago (so basically greater than 1 year ago) it works.

 

To test it I went into my test database, modified the date to be one year and one day older than today, and it disappeared.

 

So cool. Thank you so much for your guidance.

 

Daniel

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 (5,013)