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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

4 REPLIES 4
RandyHayes
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (21,337)