cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWF_2019
Level: Powered On

Error using datepicker control which refers to invalid connector

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

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Error using datepicker control which refers to invalid connector

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JWF_2019
Level: Powered On

Re: Error using datepicker control which refers to invalid connector

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

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,080)