cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwagma
Helper I
Helper I

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

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))

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

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
Super User
Super User

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,504)