web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Error using datepicker...

Power Apps

Unanswered

Error using datepicker control which refers to invalid connector

(0) Share

Report

Report

Posted on by JWF_2019

4

Hello,

I am using a gallery to display tasks and when I use the following, powerapps says there is an error

Filter('[dbo].[View_Tasks]', ProjID = drpProject.Selected.ProjID And StartDate >= dteFilterStartDate.SelectedDate )

The error is

An error occurred on the server. Server response: Not a valid connector. Error Response.

When I use Filter('[dbo].[View_Tasks]', ProjID = drpProject.Selected.ProjID ) as the items for the gallery it works.

However the datepicker control portion ( And StartDate >= dteFilterStartDate.SelectedDate ) causes the error.

In sql server, the data is a date field.

Any ideas why it should not like this statement?

Regards,

John

Categories:

Building Power Apps

All responses Img

All responses (2)

Answers Img

Answers (0)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JWF_2019 ,

Do you mean that you can not filter data based on date type of SQL?

Please note that:

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

//modify your date field data to value and compare this value

Here's a doc about it for your reference:

https://docs.microsoft.com/en-us/connectors/sql/

I've made a similar test for your reference:

test5 is my date type field

set the gallery's Items:

Filter( AddColumns('[dbo].[testtime]',"newdate", Year(test5) * 10000 + Month(test5) * 100 + Day(test5)), newdate=Value(Text(DatePicker1.SelectedDate,"[$-en-US]yyyymmdd")) )

Then the comparison will work.

Best regards,
JWF_2019 4 on at

Like
a
(0)

Report
Copy link

Link copied!

I changed the formula as per your instructions and while there is no error, it did not return any records. I even removed the projID criteria and still nothing.

I then used AddColumns on a DataTable control to look at the data. It shows the column but only some data at the beginning of the table (perhaps 500 records) which represents data for the year 2010.

Now I see in the documentation there are "throttling limits" of 500. So now I how do I filter properly and see records. My objective was to create a filter form so that the user would select a project and a date range to see the tasks for that period of time.

I thought this would be a really easy and straight forward application but it seems to me this is not simple.

Is the sql date filter being looked into? The current method cannot be efficient if your always converting the date format.

Regards,

John

Join the conversation