Delegation in filter gallery with dropdown and If for "all"

[pwagma]

Hello

I know that this is a topic that has already been discussed a lot, but I still have issues.

On my particular case, I have a gallery with items from CDS, 2 dropdown filters, and 1 search textbox.

The dropdowns get the list from sharepoint list plus the "all option", all collected on collections at the opening of the app.

The other problem is that the CDS doesn't order the items properly, so I need to add a value column to order the IDS properly.

The problem of course, are all the IFs inside the code, specially the dropdowns one. 

There are 4 combinations possible:

Dropdown 1 All; Dropdown 2 All

Dropdown 1 All; Dropdown 2 Selected

Dropdown 1 Selectedl; Dropdown 2 Selected

Dropdown 1 Selected; Dropdown 2 All

So I coded the 4 options to avoid the delegation problem.... But it is crazy, and I want to do this properly. Can someone help me to do this a bit more friendly?

Thanks

Pablo

SortByColumns(AddColumns(SortByColumns(If(!(DropdownEstadoClientes.SelectedText.Value = "Todos los estados"),If(!(DropdownVendedoresClientes.SelectedText.Value = "Todos los vendedores"),Filter(SortByColumns(Clientes,"createdon", Descending), NombreEstado = DropdownEstadoClientes.SelectedText.Value, NroDomicilio = 1, TextSearchBox1.Text in NombreCliente, NombreVendedor = DropdownVendedoresClientes.SelectedText.Value),Filter(SortByColumns(Clientes,"createdon", Descending), NombreEstado = DropdownEstadoClientes.SelectedText.Value, NroDomicilio = 1, TextSearchBox1.Text in NombreCliente)), If(!(DropdownVendedoresClientes.SelectedText.Value = "Todos los vendedores"),Filter(SortByColumns(Clientes,"createdon", Descending), NombreVendedor = DropdownVendedoresClientes.SelectedText.Value, NroDomicilio = 1, TextSearchBox1.Text in NombreCliente), Filter(SortByColumns(Clientes,"createdon",Descending),NroDomicilio = 1, TextSearchBox1.Text in NombreCliente))),"createdon",Descending),"nuevoid",Value(cr398_idcliente)),"nuevoid", If(SortDescending1, Ascending, Descending))

[RandyHayes · Accepted solution]

@pwagma 

The core issue is that you are using the IN operator in your Formula.  That is not a delegable operator.

You need not create so many of the same filter criteria and use Ifs in this.  It can be all one formula.

Please consider changing your Formula to the following:

With({_delegableData: 
        Filter(
            SortByColumns(Clientes,"createdon", Descending), 
            NroDomicilio = 1 &&
            ((DropdownVendedoresClientes.Selected.Value = "Todos los vendedores") || (NombreVendedor = DropdownVendedoresClientes.Selected.Value)) &&
            ((DropdownEstadoClientes.Selected.Value = "Todos los estados") || (NombreEstado = DropdownEstadoClientes.Selected.Value))
         )},


    SortByColumns(
        AddColumns(
            Filter(_delegableData,
                TextSearchBox1.Text in NombreCliente
            ),
            "nuevoid", Value(cr398_idcliente)
        ),
        "nuevoid", 
        If(SortDescending1, Ascending, Descending)
    )
)

(please note: this was all typed by hand...some syntax typos might exist).

In the above formula, we are first doing a filter (with all the dropdown conditions accounted for properly - without If's) and excluding the NombreClient search with the in operator.  This will all be delegable.

These results are assigned to the scoped With variable _delegableData.

We then use that to further filter with the In operator...at this point the data is in the app variable _delegableData, so delegation is not an issue.

Finally, add the column and the sort.

NOTE: I also replaced your .SelectedText property references in your formula.  You should avoid using that property as it has been deprecated.   Make sure that .Value is a column of your dropdown items property.  If not, then adjust that .Value to the appropriate column name.

I hope this is helpful for you.

[pwagma]

I will try your solution now

About the "in" part, I understand that the "in" is delegable on CDS datasource. 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Thanks
Pablo

[pwagma]

Thank you for your input.

Right now the code is MUCH better.The delegation problem I have it when I sort it out with the new column. Is not a big deal because it gives as a result the last 500 items that answer my filter.

The "in" was not a problem.

This is the code right now:

SortByColumns(AddColumns(SortByColumns(Filter(Clientes,(DropdownEstadoClientes.SelectedText.Value = "Todos los estados" || NombreEstado = DropdownEstadoClientes.SelectedText.Value), NroDomicilio = 1,(DropdownVendedoresClientes.SelectedText.Value = "Todos los vendedores" || NombreVendedor = DropdownVendedoresClientes.SelectedText.Value), TextSearchBox1.Text in NombreCliente),"createdon",Descending),"nuevoid",Value(cr398_idcliente)),"nuevoid", If(SortDescending1, Ascending, Descending))

[RandyHayes]

@pwagma 

I am not aware of that being delegable.  The sighted link does not specifically state that In is delegable to CDS/DataVerse, but yet that it is dependent on the datasource.  

However, the formula I provided should avoid that at any rate as it will be pre-filtering and then applying the operator the the pre-filtered results.