cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Community Support
Community Support

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.
Highlighted
Frequent Visitor

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,908)