Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Error when delegating Date filter to SQL Server

(0) ShareShare
ReportReport
Posted on by 284

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

Categories:
  • AnthonyPhan Profile Picture
    AnthonyPhan 284 on at
    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.

  • timl Profile Picture
    timl 34,222 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard