Unanswered

Filter gallery by comparing date time field with last 30 days criteria

(0) Share

Report

Posted on by Community Power Pla...

I want to Filter a gallery datasource for the last 30 days of created items.

Seems simple enough and working with sharepoint lists before i have done it as it has been text fields that i can convert to date values and compare to filter out.

This connection is SQL the createdon field shows as e.g. 6/5/2014 1:47 PM

Showing the last 30 days is fine with the DATEADD function - DateAdd(now(),-30,Days)

Both date values appear the same format 6/5/2014 1:47 PM compared to 3/25/2018 4:47 PM.

This doesn't show any results so all i can think is they are not really the same format.

Anyone ran into this issue? I thought about converting to just date values both fields like i would do in SQL however they are datetime in the SQL backend so unlike text fields seems difficult.

Any advice would be great, maybe i am missing something because this seems a very simple filter to want to see in a gallery.

Comparing 30 days ago to a datetime field value

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Community Power Pla... on at

Like (0)

Report

I also tried the trick of using that createdon field to a datepicker but this proved the same result.

Logic seems correct because i can do an IF statement to see which side the results are falling but to filter the gallery i just get no results

Was this reply helpful? Yes No
tianaranjo 497 on at

Like (0)

Report

@Anonymous I have done something similar with the following:
ClearCollect(colFilterCurrentYear, Filter(colAllEvents, Value(DateYear) = Value(CurrentYear)
|| Value(DateYear) >= Value(CurrentYear) - 3))

I created a collection for items submitted in this year. Then I filtered my 'master' collection based on CurrentYear - DateYear (DateYear is column in my SP list that pulls Year from the date the item was created.

Hope that helps get you started.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the response i guess i'm seeing if anyone has done date add criteria for gallery items.

I can bring both the comparing dates

1. SQL field createdon
2. DateAdd(Now(),-30,Days)

I can pull them both to a label and they show the same format but this seems to not be the case when i make the gallery items e.g.
where Createdon >=DateAdd(Now(),-30,Days)

get no items i can only think that it is comparing two different formats but shows them the same when brought out to a label.

Wondered if anyone has filtered gallery by DateAdd(Now()... function applied to an sql source

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @Anonymous,

Could you please share a screenshot of your PowerApps app's configuration?

Further, could you please show more details about your SQL table?

I have made a test on my side and don't have the issue that you mentioned. My PowerApps app's configuration as below:

The Items property of the Gallery control set to following formula:

Filter('[dbo].[TaskLists]',CreatedOn>=DateAdd(Now(),-30,Days))

Note: The CreatedOn column is a datetime type column in my SQL table.

Please check if there are some records existed in your SQL table. In addition, please refresh your SQL table datasource within your PowerApps app and then try your formula again to check if the issue is solved.

Please also take a try to re-create your PowerApps app to check if the issue is solved.

Best regards,

Kris

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the reply have tried this and it works less than equal to but not greater than / equal to.

This is a CRM sql table i aim using (e.g. Accounts created) in the last 30 days.

Can't think what can be causing this as it works less than today e.g.

Filter('[dbo].[Account]',CreatedOn<=DateAdd(Now(),30,Days))

There is data for these criterias through checking straight SQL, can't think it wouldn't work with an SQL table from crm .

not sure what else to try

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

just to add i am getting results for less than 30 days and get results but these results seem wrong e.g. Created on is a lot less than 30 days ago.

It must be now() or today() function is giving different data type or not understanding the sql from the CRM database, seems odd.

I don't care about the time would be good to convert both sides to just date format

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Having the same issue, where the formula works for Less than or equal to, but not greater than.
Did you ever resolve?

Was this reply helpful? Yes No