How can I use a toggle to filter a gallery with delegation

[Medoomi]

I have a yes/no toggle & would like to use it to filter the content of a gallery. 

However, If I use an If() expression (to incorporate the true/false output of the toggle), I encounter a delegation warning on the filter.

Can anyone suggest a different way to incorporate the toggle without involving a delegation warning?

At present, my code (with the delegation warning) looks like this & my toggle is labelled TogProjHomeFilter (the entire if() expression is marked with the delegation warning):

Sort(
    Filter(
        Projects,
        ProjectSearch.Text in 'Project Name' || ProjectSearch.Text in Customer,
        If(
            TogProjHomeFilter.Value = true,
            'Opportunity Status'.'Projects Home Filtered' <> "no",
            'Opportunity Status'.'Projects Home Filtered' <> "yes"
        ),
        IsBlank(ProjectStatusSearch.Selected.Forecasting) || 'Opportunity Status'.Forecasting = ProjectStatusSearch.Selected.Forecasting,
        IsBlank(ProjectStateSearch.Selected.Abbreviation) || State.Abbreviation = ProjectStateSearch.Selected.Abbreviation
    ),
    'Approx Close',
    Ascending
)

[WarrenBelz]

Hi @Medoomi ,

Firstly, the in Filter is not Delegable - so you would have to use

StartsWith('Project Name',ProjectSearch.Text) || StartsWith(Customer,ProjectSearch.Text),

Also is the toggle "attached" to a field? Boolean (yes/no) fields are not Delegable, but I don't think that is the problem here. You are referring to 'Opportunity Status'.'Projects Home Filtered', and the Filter is on Projects. What is this reference to (another list or a field in Projects)?

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.

[Medoomi]

Thx @WarrenBelz 

Opportunity Status'.'Projects Home Filtered',  is simply a text list of yes/no values. It is related to Projects as a lookup field within Projects.

Edited to add:

I could easily be wrong, but so far, I haven't encountered a delegation warning when using:

ProjectSearch.Text in 'Project Name' || ProjectSearch.Text in Customer

It is the If() portion of the formula which brings the delegation warning.

Regarding the toggle, I am not sure what you mean by attached. It is largely unaltered from defaults, so it outputs true/false... 

[WarrenBelz]

Hi @Medoomi ,

That is your second Delegation issue - LookUp fields are not Delegable. I have a blog on Delegation that may have some suggestions on collections. You unfortunately cannot "pre-filter" this as all the other filters have a possibility of containing all records.

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.

[Medoomi]

Thx again @WarrenBelz 

Hmmm, maybe to change the question then somewhat, the following code currently filters the gallery with delegation:

Sort(
    Filter(
        Projects,
        ProjectSearch.Text in 'Project Name' || ProjectSearch.Text in Customer,
        'Opportunity Status'.'Projects Home Filtered' <> "yes",
        IsBlank(ProjectStatusSearch.Selected.Forecasting) || 'Opportunity Status'.Forecasting = ProjectStatusSearch.Selected.Forecasting,
        IsBlank(ProjectStateSearch.Selected.Abbreviation) || State.Abbreviation = ProjectStateSearch.Selected.Abbreviation
    ),
    'Approx Close',
    Ascending
)

The line I would like to have a user change/toggle/alter, is this one:

'Opportunity Status'.'Projects Home Filtered' <> "yes"

I.e. I would be delighted if I could use a control to toggle the "yes" in this line above to a "no."

Is there a way I could do this creatively, or even have the toggle output text so it could be incorporated into the formula?

[WarrenBelz]

Hi @Medoomi ,

What is your data source type (I am assuming SharePoint)? Also why to you have <> "yes" when you could use = "no" ?

I can assure you the in Filter is not Delegable in SharePoint, neither are Lookup fields (they are a bit worse actually). Can I correctly assume here that 'Opportunity Status' is a Lookup field in Projects pointing at another list (the name would be helpful) and that 'Projects Home Filtered' is a field in that other list? If so, what is the likely maximum size of that list?

[Medoomi]

Thanks again @WarrenBelz 

My data source is DataVerse. I don't know if this explains why I do not have a delegation warning on the code I posted. In any case, the table Projects has a likely maximum size of 5000 records, so it would be nice to avoid delegation warnings.

I used a <> "yes" mainly because the code/table was in progress & I wished to include records that had blank values.

You are also correct that 'Opportunity Status'  is a lookup field in Projects. It points to the table 'Opportunity Status'. And yes, 'Projects Home Filtered' is a field in that table.

I am open to creative solutions to the delegation warning... but like I mentioned, I only see a delegation warning when I introduce an If() clause to the filter. With the following code, it *seems* to work, I am just unsure how to introduce a toggle/button/something to turn the clause on and off (use a variable?)

'Opportunity Status'.'Projects Home Filtered' <> "yes"

[WarrenBelz · Accepted solution]

Hi @Medoomi ,

I am a Sharepoint user, but normally the documentation I refer to for DataVerse is correct, however if you are not getting Delegation warnings on the in Filter, that is good. If you want creative, try the below.

With(
   {
      wToggle:
      If(
         TogProjHomeFilter.Value,
         "no",
         "yes"
      )
   },
   Sort(
      Filter(
         Projects,
         (
            ProjectSearch.Text in 'Project Name' || 
            ProjectSearch.Text in Customer
         ) &&
         'Opportunity Status'.'Projects Home Filtered' <> wToggle &&
         (
            IsBlank(ProjectStatusSearch.Selected.Forecasting) || 
            'Opportunity Status'.Forecasting = ProjectStatusSearch.Selected.Forecasting
         ) &&
         (
            IsBlank(ProjectStateSearch.Selected.Abbreviation) || 
            State.Abbreviation = ProjectStateSearch.Selected.Abbreviation
         ),
        'Approx Close'
      )
   )
)

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.

[HEATFreight]

I'm not really seeing a solution to my problem here.

With() statements are amazing tools to abstract logic into a delegable format, but my issue is that the With() statement doesn't solve the problem when the logic which needs to be toggled is a decision between matching, for instance, one explicit value vs. any value.

Let's say I want a toggle that filters a SharePoint list by a true/false (Yes/No) column. Sometimes I want to see all the records. Sometimes I want to see records matching a specific value, true but not false, or false but not true. Well ok, that's easy enough to fix. Just have a Switch() or If() statement to step through each of the possible toggle conditions and just run the Filter('SharePoint List', conditionAorB) again for each condition.

Well that would be no problem if you had just one toggle, but what if you have several? Now you have a scenario where each non-delegable toggle must be split up into as many filter conditions as necessary and permutated against the filter conditions of all the other toggles....

Two toggles with two conditions each, that's four separate Filter() statements. Three toggles with two conditions each requires six separate versions of the Filter(), four toggles with two conditions needs eight, and so on.

If the toggle conditions are as simple as xyz=true and xyz=false, then you can implement that toggle without extra versions of the same Filter(). But if you need the toggle to switch between xyz=true and xyz=Or(true,false,null), for example, well I'm not really sure there is a way to delegate that.

Another example, say you have a text column that needs to either match a certain string or match any string, well you can't do IsMatch(columnName,Any) because that's not delegable. I wonder if you could do StartsWith(columnName,"")??? I don't think I've ever tested that, but it seems logical... All strings start with an empty string, right? If filtering on <StartsWith(columnName,"")> is the same as filtering on <true>, then the toggle would simply set some variable to either the empty string or some other string of interest, and then you'd be all set. I might test that later but it doesn't solve my use case. Actually, Randy Hayes confirms that here. So, that should eliminate a few of my toggles which require a sort of "wildcard" logic, because you don't need a wildcard operator to filter a blank text query.

Anyway, is there a wildcard operator for SharePoint requests in PowerApps?

Another thing I found that doesn't work is filtering on <(varBool || columnBool)>, whereas <(varBoolA || varBoolB)> and <columnBool> are both perfectly delegable! That makes no sense to me.

I think what I have to do is use a text column which is either an empty string or has a particular string value, and then my toggle will set a var either to "" or that string value. Thus it will filter by either any value or the one particular value, and that eliminates the need to write multiple versions of the Filter().

[WarrenBelz]

@HEATFreight ,

I tend to focus on what I can do with the tools I have rather than wish for new tools that may not exist, or cost more and are not really necessary most of the time. SharePoint as a data source for Power Apps is the "base model" in Office365 (albeit in most cases a very competent and reliable one). You can get more Delegation capability if you want to pay for it with Dataverse/Azure SQL etc)

Suggestion improvements are welcome on the Ideas Forum, where someone from Microsoft will look at them and a number of your previous posts (framed with a constructive suggested solution) would be very welcome there.