cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filter Data in Data table by Date or Year

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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

View solution in original post

10 REPLIES 10
Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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

Highlighted
Helper I
Helper I

Re: Filter Data in Data table by Date or Year

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

Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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. 

Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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

View solution in original post

Highlighted
Helper I
Helper I

Re: Filter Data in Data table by Date or Year

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.

Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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

Highlighted
Helper I
Helper I

Re: Filter Data in Data table by Date or Year

Hi,

 

I refer to this formula:

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

 

Highlighted
Memorable Member
Memorable Member

Re: Filter Data in Data table by Date or Year

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/

Highlighted
Helper I
Helper I

Re: Filter Data in Data table by Date or Year

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.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,642)