Help on Delegation Warning

[MarkConley]

I am working on fixing my Delegation Warning, and have read the Understand delegation in a canvas app - Power Apps | Microsoft Docs, which hasn't helped me.  I have also read a bunch of articles and watched YouTube videos, and thought I had figured it out.  However, I am still getting delegation warnings.

To give some background, I have a Gallery that is linked to the VT Contracts SP List, where I'm searching for all VT Contracts (column named VT Contract) that are active contracts.  This list is small now, but will grow.

My original code was throwing up a delegation warning:

Filter('VT Contracts', searchBox_5.SearchValue in 'VT Contract' && 'Active Contract' = true)

The reason for the warning was because of the "in" and "&&"

I redid the code based on a YouTube video I had watched by Daniel Christian, which removed the delegation warning, but was now only showing when the Active Contract was equal to FALSE, when I clearly call out equal to TRUE

Filter('VT Contracts',StartsWith('VT Contract',searchBox_5.SearchValue) && 'Active Contract' = true)

I then redid the code a second time, which now is giving me the records when equal to TRUE, but now I am getting the delegation warning again.

Filter('VT Contracts',StartsWith('VT Contract',searchBox_5.SearchValue) && 'Active Contract' <> false)

What do I appear to be missing, or misunderstanding?

[RandyHayes · Accepted solution]

@MarkConley 

Your problem comes from the use of the Boolean Yes/No column in your filter.

There is a long going bug in PowerApps related to yes/no columns.  In general, you can ONLY (delegable) filter for a true value, and you cannot specify it.

So, your formula can be this:

Filter('VT Contracts',
    StartsWith('VT Contract', searchBox_5.SearchValue) && 
    'Active Contract'
)

You cannot filter for any other condition of that yes/no column without a delegation issue and also inaccurate results.

The better solution to this is to utilize a numeric column with a 0 default.  I like to consider 0 to be "not set", 1 to be false and 2 to be true.  But, the general result is, numeric columns can be delegated, and you will get accurate results.

I hope this is helpful for you.

[MarkConley]

How would I tie in the "Yes/No" with the numeric value, with a radio button/toggle with three possible numeric values?

[RandyHayes]

@MarkConley 

You could use either a Radio or Toggle or Checkbox or Dropdown or ListBox or pretty much any control with a selection capability.

I often use a Radio control for those yes/no scenarios.  Why I like that is because I can "blank" it (meaning neither yes or no has been chosen).

A Toggle or CheckBox does not allow me to indicate that "nothing" has ever been chosen.

Now, if I don't need that capability (i.e. no selection is either false or true) then any control will do.

Also, just to be clear, I often use Yes/No columns in lists.  This is not a bad thing UNLESS you plan to use the column as part of a filter.

Also, as I mentioned, this is a long running issue.  It is pretty much bashed about in this idea post.  

Sad part is that it is 3 years old and is still listed as "New".

[MarkConley]

I did figure it out, to where it's working.  One thing I forgot to anticipate is I have another filter looking at all this, and it's based off of a Calculated Column, where if there is a Date, then "Yes", otherwise "No".  Is the *bug* also affect Calculated Columns, or is that an entirely different issue?

[RandyHayes]

@MarkConley 

A SharePoint Calculated column is not impacted by this issue (as far as I am aware).

However, a Calculated column is not a delegable column...so, you're back to the delegation issue for that.

[amit1030]

@MarkConley 

To remove delegation warning, the best way is to don't use your SP List as your Datasource in Filter function. Better to create a collection with your SP List and use that collection as a data source in the Filter function.

Example - 

 ClearCollect(TestCollection,'VT Contracts');

Filter(TestCollection,StartsWith('VT Contract',searchBox_5.SearchValue) && 'Active Contract' <> false) 

I think this will resolve the issue with the delegation warning.

If this is useful, please click on the Thumbs up button 👍

[RandyHayes]

@amit1030 

Avoid collections when you don't need them.  They are significant overhead to your app for when you really only need a simple solution.  

@MarkConley   In rethinking what you last mentioned.  You might consider ignoring the calculated column anyway.  If your calculation was based on a blank date, you can delegate this to SharePoint.

Filter(yourSource, yourDateColumn = Blank())   is a delegable statement.