cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyPhan
Level 8

Error when delegating Date filter to SQL Server

Hi,

 

I am trying to populate a collection with data from an SQL Database. I am delegating a Date filter to the SQL server to minimise the amount of data handled by the PowerApp. When the code is actioned (from a button) I recieve the error "An error occured on the server."

The code attached to the button select property is:

 

 

Set(CurrentDate, DatePicker1.SelectedDate);
Clear(WorkOrderTable);
Collect(WorkOrderTable,Filter('[db_owner].[ResourceEstimationTimeSheet]', Date = CurrentDate))

 

Strangly the filter works fine if i use:

 

Date = DatePicker1.SelectedDate

Instead of:

 

Date = CurrentDate

 

What is the reason for this behaviour?? I have attached an image below for your reference


image.png

2 REPLIES 2
Super User
Super User

Re: Error when delegating Date filter to SQL Server

This is an interesting question and I'm curious as to what the answer is.

 

The thing I notice is that the name of your column in ResourceEstimationTimeSheet is Date. Perhaps Date might be a keyword somewhere in PowerApps, and this might be the cause of the problem? (Date is the name of a function in PowerApps, but I don't think that's the cause of the problem.)

 

Interestingly, if you fully qualify the field name, the filter expression becomes non delegable and that's not the behaviour I would expect.

 

DateBug.png

 

If you were to filter a date column other than the column called 'date', do you get the same behaviour? Incidently, I was able to make this syntax work with my column called 'StartDate' without seeing any error.

AnthonyPhan
Level 8

Re: Error when delegating Date filter to SQL Server

Hi @tim

Thanks for the response. I set up a clone of the ResourceEstimationTimeSheet and renamed the Date column to DateCol. The issue still persists. 

 

image.png

 

Its also interesting to note that I am also now able to produce this issue when using DatePicker1.SelectedDate.


image.png

 

My DateCol column is of date type Date not DateTime. Could this be the issue?

EDIT: I also forgot to mention that i have no issue when i filtered on a string type column.

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 206 members 5,335 guests
Please welcome our newest community members: