Filter Data in Data table by Date or Year

[vanels]

Hi,

First of all, I want to say that I've seen another similar post but I don't want to "open" it again.

I have a drop down list with year text ("2015";"2016";"2017"; etc) and a data table with a StartDate Date column.

I'd like to filter this data by year only but I think that I have some error with format data...

My formula is 

SortByColumns(Filter('[dbo].[FORECASTSALES]'; DateValue(Text(STARTDATE; DateTimeFormat.ShortDate))>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

But a blue icon comes up on the left hand side corner with message: "Suggestion: Part of this Filter formula cannot be evaluated remotely due to service limitations..."

It shows some data (only 6 rows) but there are many in the database.

I only want filter by year but if I write something like this, the blue icon appear again...

SortByColumns(Filter('[dbo].[FORECASTSALES]'; Year(STARTDATE)=Value(ddYear.Selected.Value));"STARTDATE";Descending) 

Any help will be appreciate.

Thanks a lot!

[Sienna]

I assume you are using sql table. If so You can't do it. You have to collect the table into a collection and filter that collection

[vanels]

Oppss!!

Yes, it is a sql table.

And... it's posible to create a collection with all data table?

I supose that then, my data table will be connected with the collection but in what momment I create the collection?

In other test I've done, I've seen the format of STARTDATE field...

With this formula

SortByColumns(Filter('[dbo].[FORECASTSALES]'; STARTDATE>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

the message error is:

Invalid Mashup expression using splies values: operator < cannot be applied to DateTimeZone and DateTime...

Thanks

[Sienna]

To create a collection just type in your screen OnVisible property for example

ClearCollect(CollectionName, 'NameOfYourDatabase')

The collection will not be connected and you will have to update the collection everytime you make some changes with your SQL database. 

[Sienna · Accepted solution]

Yes... You can't filted SQL databse with dates.. There is a workaround where you will have to save all your dates as Value and save them as longinteger in SQL and if you would want to filter for example year 2015 then you will have to filter it like this

Filter(DatabaseName, DateColumn>=Value(Date(2015,1,1)) && DateColumn<Value(Date(2016,1,1)))

It is a bit tricky but it works and it is reliable and you don't have to collect the database into a collection

[vanels]

Yes!! This is the last formula I used but i put above, It shows a error message about the format (datetimezone and datetime).

First if all, I could try to fix this formats.

[Sienna]

What formula are you talking about now? Collecting to collection or filtering?

[vanels]

Hi,

I refer to this formula:

SortByColumns(Filter('[dbo].[FORECASTSALES]'; STARTDATE>=Date(Value(ddYear.Selected.Value);1;1));"STARTDATE";Descending)

[Sienna]

Hi @vanels

That formula will not work until you rework the database. If you insert a label into your app and change the Text property to 

Value(Date(ddYear.Selected.Value);1;1))

then you will get a long number. For example Value(Today()) is 1520899200000. That means if you want to do it this way, you have to rework the database. Mainly your column for date. You have to change the data format to longInteger. and re-save all dates to Value(Date) that means to that long number. There has been always some problems with dates in SQL so I prefer to do it this way. It is up to you what you gonna chose. There is also some good reading about it

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/

[vanels]

Ok, ok... I saw the same formula as mine and I was happy :), but I didn't realize that it was needed to make this changes in the database.

Well, I understand the options.

Thank you very much for your patience.