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

Find all records 60 days old

How would i find all records in SQL server whos date is 60 or more days old.

 

I use:

Filter("MY TABLE", DateDiff(Now(), IN_SYSTEM_DATE) >= 60)

 

But i keep getting service limitation error. I thought Filter supported delegation with SQL server... How would i acheive this?

8 REPLIES 8
mr-dang
Level 10

Re: Find all records 60 days old

Hi,

I have a solution for you. Since 60 days ago is a static date, you can have it pre-calculated so that your filter does not need it calculated for each row.

 

Create a DatePicker control to store the static calculation. Set its DefaultDate property to:

DateAdd(Now(),-60)

This means, "Add negative 60 days to the date and time now." You could use Today() in place of Now().

You can hide the DatePicker control if you don't want it seen. Set its Visible property to false. At this point, you could also get creative by adding a slider to adjust 60 days to whatever other amount you want. In that case, you would adjust DatePicker.DefaultDate to:

DateAdd(Now(),-Slider1.Value)

 

Then Filter your table comparing dates against the DatePicker you just created:

 

Filter("MY TABLE",IN_SYSTEM_DATE<=DatePicker1.SelectedDate)

This means, "Filter my table to show all dates that are older than or equal to 60 days ago, the date calculated in DatePicker.

 

Note: Delegation is supported with Controls this way; a context variable would not work for this.

 

Let me know how this works for you.

 

Mr. Dang

Microsoft Employee
@8bitclassroom
Bri
Level: Powered On

Re: Find all records 60 days old

I get an error, "We cannot apply operator < to types DateTimeZone and DateTime.

 

Any suggestions?

mr-dang
Level 10

Re: Find all records 60 days old

My guess is that there is a mismatch between data types. The dates in your datasource may be formatted differently than the DatePicker, but I really don't know.

 

This is probably a question for the PA team.

 

@v-micsh-msft

Microsoft Employee
@8bitclassroom
Bri
Level: Powered On

Re: Find all records 60 days old

Yes, it is a mismatch of data types. However, I have altered every datetime value i can on the SQL server side, but still no luck.

Highlighted
Meneghino
Level 10

Re: Find all records 60 days old

Hi @Bri

There SQL Server / Azure SQL DB connector currently has many bugs, including the following:

1) After a field contains a value, you cannot set the value back to null from PowerApps
2) There are intermittent issues writing numbers with more than 7 significant figures to the database
3) Date type columns cannot be filtered using > or < operators
4) Writing to date type columns is problematic

 

As a work-around for 3) and 4) I now store all date information as an integer of value yyyymmdd.  This allows me to filter using > and < operators as well as avoid all pitfalls with time zones.

 

I know that the PowerApps team is working to resolve all the above, and there should be a new connector out in July sometime.

 

Hope this helps.

 

PS I have many posts on all the above and more if you want to search the community

Bri
Level: Powered On

Re: Find all records 60 days old

Where do you get this information about what they are working on, and when it will be completed. I know they have a blog, but that does not give any time frames for completion.

That would be a great resource to have.
Meneghino
Level 10

Re: Find all records 60 days old

Hi @Bri

Unfortunately communication from the PowerApps group is generally pretty poor and unstructured.  We have complained about it, but no real progress.  They have now put status on the ideas section but no dates and no details.

I know about the significant figures issue since I wrote to the support team and received a reply to say that they are working on a fix and should be out in July.

@mr-dangthen posted at the bottom of this thread with a link to a video talking about delegation of SQL server connector being updated soon.

Putting two and two together, I just imagine that they are reviewing the whole SQL connector as it is just too buggy at the moment.

Sorry to disappoint.

Bri
Level: Powered On

Re: Find all records 60 days old

Not a problem at all, I appreciate all the help. And the SQL connector seems fine for my purposes other than dates/time.

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
Users Online
Currently online: 184 members 6,207 guests
Please welcome our newest community members: