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.

PowerApps Staff CarlosFigueira
PowerApps 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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 155 members 3,681 guests
Please welcome our newest community members: