Hi all , banging my head against the wall ....
I have a drop down (DropSailDate) that contains dates :
in my gallery Items :
DropSailDate.Selected.Value <> Blank() , Filter('[dbo].[PAX_LIST]', SAIL_DATE = DropSailDate.Selected.Value,
the SAIL_DATE column in MSSQL table is date
when dropdown selected , I get no values, when blank , I have the full list populated .
I created 2 variables , X and Y , X contains the distinct values of the database table and Y is the dropdown selected value. ( please see the attached file ) , they both are the same date format .
what am I doing wrong ?
Solved! Go to Solution.
Thank you very much for you help , I will try that today 🙂
It just blows my mind that Microsoft cannot recognise a date within 2 Microsoft products !!
Hi @cafeolai ,
This a known limitation for SQL Server connector, direct date filters do not work for SQL Server. (Reference: https://docs.microsoft.com/en-us/connectors/sql/ )
The following data types cannot be used as query option predicates:
So, the solution for this issue is to create a calculated column for the date column in SQL table with Integer datatype. The formula for this calculated column is “YEAR([your_date_field]) * 10000 + MONTH([ your_date_field ]) * 100 + DAY([ your_date_field ])“. The data in your table will look like below with new field
Then in PowerApps just filter on that new Integer column:
Filter('[dbo].[PAX_LIST]', SAIL_DATEAsInt =Value(Text(DropSailDate.Selected.Value,”[$-en-US]yyyymmdd”)))
Hope this helps.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.