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

Date comparison inside Filter of SQL table fails but works outside of Filter?

I have a table in SQL that has two fields 'Date Start' and 'Date End' that are stored as date types. I have this code that seems to work:

First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID')).'Date Start' < AttendanceDate
First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID')).'Date End' > AttendanceDate

AttendanceDate is another SQL date field from a different table that has been put into a collection then used in a gallery. I want to refine my filter statement to include that check. I tried:

First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID', 'Date Start' < AttendanceDate, 'Date End' > AttendanceDate))

When I do this I get the following error message:

Filter Error.png

I know there are special requirements with PowerApps vs SQL and date fields. In other cases I have used Text versions and that works great for direct comparisons but I want to do ranges. Can anyone recommend a work arround?

3 REPLIES 3
BrianHFASPS
Level 8

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

I have found a workaround not sure if it is best so would welcome any comments. I started with collecting a narrow list:

Collect(StudentRoster,Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID'))

Then I am able to do the LookUp in the way I wanted:

LookUp(StudentRoster,'Date Start' < AttendanceDate && 'Date End' > AttendanceDate, ActivitySignupID)

This may be optimum as the Collect filters it down to very few records and then I can locally in the app narrow to specific one in easiest way.

Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

Date comparisons with SQL tables currently have an issue in which the delegation doesn't work properly (see https://docs.microsoft.com/en-us/connectors/sql/ for information). To work around this, you can add a pair of calculated columns in your SQL table:

ALTER TABLE [Activity Rosters]
ADD [Date Start Int] AS YEAR([Date Start]) * 10000 + MONTH([Date Start]) * 100 + DAY([Date Start])

ALTER TABLE [Activity Rosters]
ADD [Date End Int] AS YEAR([Date End]) * 10000 + MONTH([Date End]) * 100 + DAY([Date End])

And you can compare the dates with the numeric value of the AttendanceData from your app:

First(
    Filter(
        '[dbo].[Activity Rosters]',
        DailyActUID = 'Bill Student List'.Selected.'User ID',
        'Date Start Int' < (10000 * Year(AttendanceDate) + 100 * Month(AttendanceDate) + Day(AttendanceDate)),
        'Date End Int' > (10000 * Year(AttendanceDate) + 100 * Month(AttendanceDate) + Day(AttendanceDate))))

Hope this helps!

BrianHFASPS
Level 8

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

Thanks for the idea, I will keep in that in my pocket in case I need it in the future. It is similar to the date to string in SQL for compare someone else showed me before. Since my Collection workaround requires no changes to SQL or any extra date formatting I will stick with it until the SQL connector is hopefully updated to support dates properly. I am filtering with my initial query from 1000 to 1-2 so that already reduces my data time / costs, then locally I can shrink to the specific one I want.

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (7,528)