Filtering a gallery with datepicker doesn't work with other conditions

[OrcPeon]

Trom'ka Powerappers,

i'm facing a really strange issue in my app. 

The learn materials were taken by @RezaDorrani :

https://www.youtube.com/watch?v=6KlI1iZ_KD0

https://www.youtube.com/watch?v=eCMuXPI1Qok

Whenever I try to filter my gallery with a following code, it doesn't work at all. If I use only datepicker (Value(Text(DatePicker1.SelectedDate; "yyyymmdd")) = 'Erstellt am Number') condition, it works fine and I dont' know why!

SortByColumns(
    Filter(
        LeadTabelle;
        StartsWith(LeadName; TextInput1_1.Text)
        && 
        (Status_Kunde = Dropdown2.Selected.Value || Dropdown2.Selected.Value = Blank()) 
        && 
        (DatePicker1.SelectedDate = Blank() || Value(Text(DatePicker1.SelectedDate; "yyyymmdd")) = 'Erstellt am Number')
        );
        "createdon";Ascending)

To avoid delegation warning, I created in my database extra int row ('Erstellt am Number') and populeted it with data in "yyyymmdd" format, and so I could format datepicker into value and compare it with my extra row, that I created. Somehow if i use Value('Erstellt am Number), it kind of works fine, but still with a delegation warning. 

In my opinion, the proper way would be:

DateValue(DatePicker1.SelectedDate) = DateValue('Erstellt am')

However, it causes delegation warning. Again.

Dataverse is my datasource.

Any help would be really appreciated!

[WarrenBelz]

Hi @OrcPeon ,

Firstly, what is your Data Source ? If SharePoint, you simply store the field as a date with no Delegation issues. Please confirm this before we go further.

[OrcPeon]

Dataverse is my datasource.

[WarrenBelz]

@OrcPeon ,

Then you should be able to simply store it as a Date

SortByColumns(
   Filter(
      LeadTabelle;
        StartsWith(
         LeadName; 
         TextInput1_1.Text
      ) && 
      (
         Status_Kunde = Dropdown2.Selected.Value || 
         Dropdown2.Selected.Value = Blank()
      ) && 
        (
         DatePicker1.SelectedDate = Blank() || 
         DatePicker1.SelectedDate = 'Erstellt am Number'
      )
   );
   "createdon";
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[OrcPeon]

I alredy tried it before. I think, I need to explain the field 'Erstellt am' better. This is a standard row from dataverse, whenever dataset would be created:

The problem is, datepicker1 gets the timestamp in shortdatetime format. And 'Erstellt am' (CreatedOn) has the hours and minutes within it.

If I convert manually with Text() function the 'Erstellt am' into the ShortDate format, i will face delegation warning. But IT works!

I can expand my app up to 2000 datasets, I know the drill, but it is not a solution at all.

[WarrenBelz]

Hi @OrcPeon ,

I am a SharePoint user, but Dataverse has equal or greater Delegation capabilities. If you have a date/time field and some hour and minute drop-downs, you should be able to update it with

DatePicker1.SelectedDate + 
Time(
   Value(HourDDName.Selected.Value), 
   Value(MinuteDDName.Selected.Value), 
   0
)

You are always going to get that Delegation warning when you try to convert a data source field to Text as the function is not Delegable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[OrcPeon]

Thank you very much for your efforts, I appreciate it! 

Unfortunately, there is no chanche to track exact time (hours and minutes) in my scenario. 

I used a trick from @RezaDorrani and created an extra row in Dataverse, which is int row. It is 'Erstellt am Number'.

In the background PowerAutomate populate this field with the data from 'Erstellt am' in a "yyyymmdd" format.

And it works! BUT, as I said in at the beginning: if I use ONLY this condition in my filter, it works, as soon as I add extra condition, like Datepicker1.SelecetedDate = Blank(), it shows me empty screen. 

Do you have any clue, why is it so?

Here some examples:

And the result of this filter is a match with my datasource:

Another try with extra condition:

Here is the result:

I'm so confused... 😞

[WarrenBelz]

@OrcPeon ,

Only one thing here - I have found Date Pickers are not always blank - I use for a Blank test

Value(DatePicker1.SelectedDate) < 1

although I am not sure that is your issue.

[OrcPeon]

Yeah, i had the same issue and found this:

I use Reset(Datepicker1) and it works well for me. I also changed Default to ""

I can try another condition, like StartsWith in my filter, the result will be same.

[WarrenBelz]

@OrcPeon ,

The InputTextPlaceholder is irrelevant to any code - it just displays the "hint text" in the date picker if no data is present, although I tend to change mine to "Enter xxxx date" or similar.

 I also tend to leave the Default blank.