Filter optimization

[Majid2024]

The filter works perfectly, I was wondering if it can be improved?

Refresh(MTSA_TBL_Visitas_Cab);
ClearCollect(
    Col_Visitas_Cab,
    MTSA_TBL_Visitas_Cab
);
ClearCollect(
    Col_Visitas_Cab,
    Filter(
        Col_Visitas_Cab,
        If(
            IsBlank(TextInput_1.Text),
            true,
            TextInput_1.Text in Id
        ),
        If(
            IsBlank(ComboBox_1.Selected.Value),
            true,
            Estado = ComboBox_1.Selected.Value
        ),
        If(
            IsBlank(ComboBox_2.Selected.Denominacion),
            true,
            ComboBox_2.Selected.Denominacion in GruposVendedores
        ),
        If(
            IsBlank(DatePicker_1.SelectedDate),
            true,
            Fecha_Documento = DatePicker_1.SelectedDate
        ),
        If(
            IsBlank(DatePicker_2.SelectedDate),
            true,
            Fecha_Visita >= DatePicker_2.SelectedDate
        ),
        If(
            IsBlank(TextInput_2.Text),
            true,
            TextInput_2.Text in Usuario_de_creacion
        ),
        If(
            IsBlank(TextInput_3.Text),
            true,
            TextInput_3.Text in Usuario_de_modificacion
        ),
        If(
            IsBlank(DatePicker_3.SelectedDate),
            true,
            Fecha_Visita = DatePicker_3.SelectedDate
        ),
        If(
            IsBlank(TextInput_4.Text),
            true,
            TextInput_4.Text in Nombre_Visita
        ),
        If(
            IsBlank(DatePicker_4.SelectedDate),
            true,
            Fecha_Visita <= DatePicker_4.SelectedDate
        )
    )
);

[mmbr1606]

hey @Majid2024 

i would never change a running system xD why do you want to improve it? any reason for it?

[SpongYe]

Hi @Majid2024 

First point: Your code is already quite efficient, but there’s a small improvement that can be made. Instead of calling ClearCollect twice on the same collection Col_Visitas_Cab, you can directly apply the Filter function on MTSA_TBL_Visitas_Cab and store the result in Col_Visitas_Cab. Then you code would like something:

ClearCollect(
    Col_Visitas_Cab,
    Filter(
        MTSA_TBL_Visitas_Cab,
        If(
            IsBlank(TextInput_1.Text),
            true,
            TextInput_1.Text in Id
        )
......

Second point: You can use the With() function to store the values of the controls in variables. This can make your code cleaner and more efficient, especially if you’re using the same control values multiple times in your code. It would look more like this:

With(
    {
        varTextInput1: TextInput_1.Text,
        varComboBox1: ComboBox_1.Selected.Value,
        varComboBox2: ComboBox_2.Selected.Denominacion,
        varDatePicker1: DatePicker_1.SelectedDate,
        varDatePicker2: DatePicker_2.SelectedDate,
        varTextInput2: TextInput_2.Text,
        varTextInput3: TextInput_3.Text,
        varDatePicker3: DatePicker_3.SelectedDate,
        varTextInput4: TextInput_4.Text,
        varDatePicker4: DatePicker_4.SelectedDate
    },
    ClearCollect(
        Col_Visitas_Cab,
        Filter(
            MTSA_TBL_Visitas_Cab,
            If(IsBlank(varTextInput1), true, varTextInput1 in Id),
            If(IsBlank(varComboBox1), true, Estado = varComboBox1),
            If(IsBlank(varComboBox2), true, varComboBox2 in GruposVendedores),
            If(IsBlank(varDatePicker1), true, Fecha_Documento = varDatePicker1),
            If(IsBlank(varDatePicker2), true, Fecha_Visita >= varDatePicker2),
            If(IsBlank(varTextInput2), true, varTextInput2 in Usuario_de_creacion),
            If(IsBlank(varTextInput3), true, varTextInput3 in Usuario_de_modificacion),
            If(IsBlank(varDatePicker3), true, Fecha_Visita = varDatePicker3),
            If(IsBlank(varTextInput4), true, varTextInput4 in Nombre_Visita),
            If(IsBlank(varDatePicker4), true, Fecha_Visita <= varDatePicker4)
        )
    )
);

The With() function is used to create a temporary environment where the variables varTextInput1, varComboBox1, etc., are defined. Then, within this environment, the ClearCollect() function is called to clear and populate the Col_Visitas_Cab collection. The Filter() function uses the variables instead of directly referencing the controls.

This approach is more efficient because PowerApps doesn’t need to retrieve the value of the controls each time they’re referenced in the Filter() function. Instead, each control’s value is retrieved only once and stored in a variable.

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[Majid2024]

That was the first thing I tried but it gives me a delegation warning.

What is better to use two collections and avoid delegation or a collection with a delegation warning?

[Majid2024]

[Majid2024]

To improve

[SpongYe · Accepted solution]

Try this:

With(
    {
        _Col_Visitas_Cab: MTSA_TBL_Visitas_Cab,
        varTextInput1: TextInput_1.Text,
        varComboBox1: ComboBox_1.Selected.Value,
        varComboBox2: ComboBox_2.Selected.Denominacion,
        varDatePicker1: DatePicker_1.SelectedDate,
        varDatePicker2: DatePicker_2.SelectedDate,
        varTextInput2: TextInput_2.Text,
        varTextInput3: TextInput_3.Text,
        varDatePicker3: DatePicker_3.SelectedDate,
        varTextInput4: TextInput_4.Text,
        varDatePicker4: DatePicker_4.SelectedDate
    },
    ClearCollect(
        Col_Visitas_Cab,
        Filter(
            _Col_Visitas_Cab,
            If(!IsBlank(varTextInput1), varTextInput1 in Id),
            If(!IsBlank(varComboBox1), Estado = varComboBox1),
            If(!IsBlank(varComboBox2), varComboBox2 in GruposVendedores),
            If(!IsBlank(varDatePicker1), Fecha_Documento = varDatePicker1),
            If(!IsBlank(varDatePicker2), Fecha_Visita >= varDatePicker2),
            If(!IsBlank(varTextInput2), varTextInput2 in Usuario_de_creacion),
            If(!IsBlank(varTextInput3), varTextInput3 in Usuario_de_modificacion),
            If(!IsBlank(varDatePicker3), Fecha_Visita = varDatePicker3),
            If(!IsBlank(varTextInput4), varTextInput4 in Nombre_Visita),
            If(!IsBlank(varDatePicker4), Fecha_Visita <= varDatePicker4)
        )
    )
);

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[Majid2024]

I had to remove the ! and set it back the true, but it works fine!

Should I use more with? It's like you do things in isolation, both creating variables and calculations, from the outside it cannot be accessed, it is not global.
Thx!