Unanswered

Another Date Filter Delegation Issue

(0) Share

Report

Posted on by Ajh

183

I have a search box and a date selection field but for the life of me I can't get it to work right. I took the automatically generated formula from the template and basically wrapped it in a filter on the date picker box using coalesce so it would work as is normally when nothing is selected. I understand what delegation is but I can't find a pattern to work around / with it.

How do I delegate with an 'optional' date field using = (on two columns), while maintaining the search capability should there be multiple rows on that day?

Data source is sql server on prem through a gateway.

Filter(
 SortByColumns(
 Search([@'[UDM].[StormData]']
 , TextSearchBox1.Text, "Class","Remarks","Designation","ParentType","ParentID")
 , "StormDate", If(SortDescending1, Ascending, Descending)
 )
 ,RecordVersion=0 && (Coalesce(DatePicker1.SelectedDate,StormDate) = StormDate || Coalesce(DatePicker1.SelectedDate,StartDate) = StartDate)
 )

Thanks very much in advance.

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

Ajh 183 on at

Like
a
(0)

Report
Copy link

Link copied!
This is my latest attempt. I believe all the statements are delegable and I don't get any warnings, but when I set the date field correctly it doesn't bring back any results. I've tried the date picker in both local and utc time zones.

Filter( SortByColumns( Search([@'[UDM].[StormData]'] , TextSearchBox1.Text, "Class","Remarks","Designation","ParentType","ParentID") , "StormDate", If(SortDescending1, Ascending, Descending) ) ,RecordVersion=0 && (DatePicker1.SelectedDate = StormDate || DatePicker1.SelectedDate = StartDate || IsBlank(DatePicker1.SelectedDate) ) )
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Ajh ,

The reason why you still have delegation warning is because of comparing with datetime field.

Please notice that:

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.

So you could try this:

Filter( SortByColumns( Search([@'[UDM].[StormData]'] , TextSearchBox1.Text, "Class","Remarks","Designation","ParentType","ParentID") , "StormDate", If(SortDescending1, Ascending, Descending) ) ,RecordVersion=0 && (Year(DatePicker1.SelectedDate) * 10000 + Month(DatePicker1.SelectedDate) * 100 + Day(DatePicker1.SelectedDate) = Year(StormDate ) * 10000 + Month(StormDate) * 100 +Day(StormDate) || (Year(DatePicker1.SelectedDate) * 10000 + Month(DatePicker1.SelectedDate) * 100 + Day(DatePicker1.SelectedDate) = Year(StartDate ) * 10000 + Month(StartDate) * 100 +Day(StartDate) || IsBlank(DatePicker1.SelectedDate) ) )

However, Year(),Month() and Day() function is not delegate for sql server.

So comparing with datetime field is a key problem for sql server.

If the formula above does not work for you, I suggest you save data to collection to avoid delegation problem.

In collection, you do not have to only use delegate functions.

The steps:
set the app's OnStart:

ClearCollect( test,[@'[UDM].[StormData]' )

The filter function(filter collection):

Filter( SortByColumns( Search(test , TextSearchBox1.Text, "Class","Remarks","Designation","ParentType","ParentID") , "StormDate", If(SortDescending1, Ascending, Descending) ) ,RecordVersion=0 && (DatePicker1.SelectedDate = StormDate || DatePicker1.SelectedDate = StartDate || IsBlank(DatePicker1.SelectedDate) ) )

I suggest you know more about limitations of sql server in powerapps:
https://docs.microsoft.com/en-us/connectors/sql/

Best regards,
Ajh 183 on at

Like
a
(0)

Report
Copy link

Link copied!

Saving as collection gets rid of the warning but not the problem if I remember correctly. I can do the int thing but man I hate that. That is not cool. This seems like such an important thing I'm really surprised there's no real way to do it.