Delegation warning error on Filter

[NewNW]

Hi All,

I m getting the following warning

I have the following function on my app.

If(dpdSites_1.Selected.Value =" Hectoville",(Filter('CarDetailsH',
Not(
Title in Filter('BookingDetailsH',( ((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
<= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) >=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0))) ||
((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
>= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0))) ||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
>= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0)))||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
<= (DateValue(Text(dtpDropOffDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) >=
(DateValue(Text(dtpPickupDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)))
)).CarRegistrationNumber
)
)),(Filter('CarDetailsM',
Not(
Title in Filter('BookingDetailsM',( ((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
<= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) >=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0))) ||
((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
>= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0))) ||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
>= (DateValue(Text(dtpPickupDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0)))||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
<= (DateValue(Text(dtpDropOffDate_1.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime_1.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) >=
(DateValue(Text(dtpPickupDate_1.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime_1.Selected.Value), 0, 0)))
)).Title
)
)))

How can I remove this delegation message?

FYI - My BookingDetailsH and BookingDetailsM SharePoint list I have used CarRegistrationNumber, PickupDate and DropoffDate as index column.

My CarDetailsH and CarDetailsM SharePoint list I have used CarRegistrationNumber as index column.

[RandyHayes]

@NewNW 

Indexing columns does not resolve delegation issues. 

Your problem in your formula is that you are using many operators that are not delegable. Specifically the In operator will not delegate to SharePoint.

You will want to take a look at the Connector Documentation for SharePoint to get a better idea on what can and cannot delegate to your datasource.

Also, review the documentation on delegation to get a better idea on delegation overall.

So, to answer your question - you cannot get rid of the delegation warning on your formula because of the above.  You will need to re-look at your formula and data to determine a better way to delegate a filter.  Very common is the use of the StartsWith() function for text columns in Sharepoint, as it will delegate.

I hope this is helpful for you.,

[NewNW]

Hi Randy,

Thank you very much for your reply, yes I m aware of these operations, but my question is how can I do this query in powerapps without delegation warning. 

I m pretty sure I m not the only one try  to do this type of query and successed without delegation issue.

Thanks. 

[RandyHayes]

@NewNW 

There is no way you can do that filter without a delegation warning.  This is because the In operator will not delegate. As well, the Not operator will not delegate either. As mentioned, you would need to change your filter.  You would need some way to utilize the operators that are delegable. If the Title column has information that is what you want to filter on, then you will need to use an operator that can delegate, such as StartsWith.

Keep in mind that the In operator, the Not operator and any operations on Date columns will not delegate (among others).  

To get past delegation issues in many cases involves creating a way to either utilize delegable functions, or create a way to delegate at the source.  Since you are using SharePoint as your datasource, there is nothing you can do to create a deleagble source, so you will need to work with the data.

In this case, I would look at how you can shape your data in SharePoint to allow you to use fully delegable functions. Some ways to do that are to convert dates to numbers or strings (again depending on how you will need to filter).  

Also, you might need to consider collecting larger lists in a local collection and to use your filters against that.

There are many options to solve delegation issues, but all involve some design consideration in the data and, because every situation is different, there is no one solution that fits all.

[NewNW]

Hi Randy,

Thanks for your reply. What I m building is car reservation system using SharePoint and PowerApps. Basically, what I m trying to do is, on my query I want to get the unreserved cars within the date range the user is select from pickup and drop off calendar including pickup and drop off time. 

So I have to filter all the cars registration numbers ( title) from CarDetails list based on the BookingDetails list that is not match with my query value(car registrations that are booked already- this is the Title filed on my SP list ).

So I m seeking expert advice and guidance to resolve this issue.

Thanks.

[RandyHayes]

@NewNW 

What are some of the sizes of these lists?  

What I would look at is restrictive string values... Things like, if you need to do a date range, then store the date as a string in the following format YYYYMMDD, then you can use filters with StartsWith and narrow the results.  For example, if you wanted all the records from May 2019, then you can Filter with StartsWith(yourStringDateColumn, "201905") 

Although the above might not help with ranges (like your original formula), you can use this in a local collection easier.  So, for example  ClearCollect(colMyLocalCollection, Filter(yourData, StartsWith(yourStringColumn, "201904"), Filter(yourData, StartsWith(yourStringColumn, "201905"))

This would give you a local collection with all the records from April and May.  THEN, you can use any other reducing filters or operations against the local collection and will have no delegation issues.

There are a lot of ways to deal with Delegation, this is just one. The choice is really going to depend on your design and overall data needs within that design.

[NewNW]

Thanks Randy, I ll chcek this and get back to you if I need further assitance.

Thanks.