Filter by Dates in Julian Format

[mmattar]

Hello all,

I am trying to filter my SQL Table on gallery based on what 2 dates a user chooses. Ex: DateFrom: 6/9/2020 and DateTo: 6/16/2020 can only be 1 week in range, I am formatting these dates to be julian like Value(Text(DatePickerTo.SelectedDate,"[$-en-US]yyyymmdd")) which will make the dates "20200609". I have a column in my SQL table that is also a date in the julian format. When comparing these i am still getting delegation issues which doesn't make sense since i am evaluation numbers. Appreciate the help

Thanks

[eka24]

Can you show the full formula giving the delegation warning

Meanwhile try;

Text(DatePickerTo.SelectedDate,"yyyymmdd")

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mmattar]

Hello, yes i am already doing that like this: Value(Text(DatePickerTo.SelectedDate,"[$-en-US]yyyymmdd")), i convert the picked date to julian so i need to check for greater than or less than on another julian formatted date that is a column in my sql table

[eka24]

Please is Value(Text(DatePickerTo.SelectedDate,"[$-en-US]yyyymmdd"))

The full formula. What are you comparing? Where is the delegation warning in this formula

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mmattar]

Filter(
   '[Table_Name]',
        Column1 = Dropdown.SelectedText.Value And Column2= Dropdown1.SelectedText.Value And

        Column3 = Dropdown2.Selected.Result And
        Value(JulianFormattedColumn) >= Value(Text(DatePickerFrom.SelectedDate,"[$-en-US]yyyymmdd")) And Value(JulianFormattedColumn) <= Value(Text(DatePickerTo.SelectedDate,"[$-en-US]yyyymmdd"))
)

Everything in blue is where the delegation warning is popping up on

An example of a JulianFormattedColumn data piece can be 20200609 and converting the datepickers to 20200611 for example. They are all just plain numbers at the end of the day, why the delegation issues ?

[eka24]

Based on the documentation you need to have a separate Column for the formatted date. Don't format the SQL Date in Powerapps but do that in a column.

After that move on with your formula. You can however format the DatePicker.

https://docs.microsoft.com/en-us/connectors/sql/

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mmattar · Accepted solution]

To anyone who will be approaching the same method as i did, make sure the julianFormatted column is of type int and not varChar like i had. I was wrapping the column around in a Value() and powerapps did not like that. So change it to int and it will filter with no delegable warnings