Filtering by date and SQL in PowerApps

(0) Share

Report

Posted on by janineb

Good day

I want to filter all the "closed" calls over the last 14 days from a SQL database in my PowerApps.

I have converted the date to int in SQL and have created a variable in the app as follows:

App / OnStart / Set(varDate, DateDiff(Today(),Days)-14)

(I have also used the following: Set(varDate, DateDiff(ClsDateClosedTxt,Today(),Days)-14) without success)

The gallery's filter is as follows:

SortByColumns(Filter(vw_ITChangeRequests,Status="Closed" && varDate),"StatusDateInt", Descending).

The filter produces all the results of all the closed calls and not just the last 14 days - can you please assist me

Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
Hi @janineb ,

Two things - you need DateAdd to get the comparison date and you need to compare it with the Date Closed (I have assumed the field name below)

With( { wDate: DateAdd( Today(), Days, -14 ) }, SortByColumns( Filter( vw_ITChangeRequests, Status = "Closed" && DateClosed < wDate ), "StatusDateInt", Descending ) )

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Mink21 158 on at

Like (1)

Report

Hi @janineb

First of all, you should get the date by using DateAdd instead of DateDiff. Like this:
Set(varDate,DateAdd(Today(),-14,Days))
Once you have the date you can convert it to the same int format you have in SQL.

Then, in your gallery I'd try the filter like this:
SortByColumns(Filter(vw_ITChangeRequests, Status="Closed", StatusDateInt >= varDate),"StatusDateInt", Descending)

I hope this helps!

Was this reply helpful? Yes No
janineb on at

Like (0)

Report

Hi Warren
I have commented out the "varDate" variable and copied your coding in the gallery. It does not show an error in the app, but I still don't get any results. There should be at least 2 results?

Was this reply helpful? Yes No
janineb on at

Like (0)

Report

Thank you for your feedback. I have tried your suggestion above, but unfortunately do not get any results (there should be at least 2 results). The app does not show that there is an error.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
Hi @janineb ,

I have commented the code below - I cannot see your data, but this should help

With( { wDate: //set wDate Variable to 14 days before today DateAdd( //done here for Delegation managment Today(), Days, -14 ) }, SortByColumns( Filter( vw_ITChangeRequests, //Filter Table where Status = "Closed" && //Status is Closed AND DateClosed < wDate //your closed date is more than 14 days ago (less then wDate) ), "StatusDateInt", //Sort by this field descending Descending ) )

If this is what you want, it should work.

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
janineb on at

Like (0)

Report

You are an absolute genius ... thank you Warren

Was this reply helpful? Yes No