cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nicky_VDB
Level: Powered On

Using multiple Combo Boxes to filter a Data Table

I'm struggling with something that should seem simple enough: I have a data table that therapists at work can see what kid they're responsible for, it includes information like: 'department', 'team', 'name',...

 

I want to add a few combo boxes so that the therapist can filter on these different columns.

 

This is the formula I'm currently using: 

 

 

 

 

 

 

If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Naam in TextInput_Name.Text Or Voornaam in TextInput_Name.Text;
        Afdeling exactin ComboBox_Afdeling.SelectedItems;
        Cluster exactin ComboBox_Cluster.SelectedItems;
        Team exactin ComboBox_Team.SelectedItems;
        Coördinator exactin ComboBox_CV.SelectedItems;
        KV exactin ComboBox_KV.SelectedItems;
        Stadium exactin ComboBox_Stadium.SelectedItems
    )
)

 

 

 

 

Which works well enough, but it adds the different filters together instead of subtracting them because of the 'Or' statements.

 

I have tried using And statements, but that doesn't work as it seems to think the combo boxes aren't empty when they infact are.

 

What I want is, if say you select a coördinator, and also a stage (stadium), you only see the children that are assigned to that coördinator in that stage, not all the kids assigned to that coördinator AND in that stage.

 

Any pointers would be awesome!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Using multiple Combo Boxes to filter a Data Table

Hi @Nicky_VDB ,

Could you please share a bit more about your scenario?

Do you want to subtract the filtered results based on the multiple conditions?

 

Please consider modify your formula as below:

If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
           '[dbo].[rAnalyseAfdelingen]';
            Naam in TextInput_Name.Text || Voornaam in TextInput_Name.Text;
           If(
              !IsBlank(ComboBox_Afdeling.Selected.Value);
              Afdeling in ComboBox_Afdeling.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Cluster.Selected.Value);
              Cluster in ComboBox_Cluster.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Team.Selected.Value);
               Team in ComboBox_Team.SelectedItems;
               true
           );
           If(
               !IsBlank(ComboBox_CV.Selected.Value);
               Coördinator in ComboBox_CV.SelectedItems;
               true
           );
           If(
              !IsBlank(ComboBox_KV.Selected.Value);
              KV in ComboBox_KV.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Stadium.Selected.Value);
              Stadium in ComboBox_Stadium.SelectedItems;
              true
           )        
    )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Using multiple Combo Boxes to filter a Data Table

@Nicky_VDB 

There are many conditions in your FILTER statement but you only mentioned co-ordinator and stage so that's what i'll focus on here.  This is your code "as-is".

Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Coördinator exactin ComboBox_CV.SelectedItems;
        Stadium exactin ComboBox_Stadium.SelectedItems
)

 

To me this says "get only the results where the Co-ordinator and the Stage match"  This is exactly your requirement!  Give it a try without all of the other filters and see if it works.

 

If it does not work, and the gallery behaves more like an OR condition the must be a problem with the selection in your combobox.  The SelectedItems property allows for the selection of more than one option.  Perhaps you will want to change the property AllowMutlipleSelections to

False

 

And then change your code to this:

Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Coördinator = ComboBox_CV.Selected;
        Stadium = ComboBox_Stadium.Selected
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Nicky_VDB
Level: Powered On

Re: Using multiple Combo Boxes to filter a Data Table

That works sort of but not quite! 

 

I changed the code to your example, but when I only select one of the comboboxes, it shows nothing instead of only filtering on the one condition.

 

When I fill all the comboboxes it filters correctly, it makes me think there's a problem with how powerapps sees the empty values.

Highlighted
Community Support Team
Community Support Team

Re: Using multiple Combo Boxes to filter a Data Table

Hi @Nicky_VDB ,

Could you please share a bit more about your scenario?

Do you want to subtract the filtered results based on the multiple conditions?

 

Please consider modify your formula as below:

If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
           '[dbo].[rAnalyseAfdelingen]';
            Naam in TextInput_Name.Text || Voornaam in TextInput_Name.Text;
           If(
              !IsBlank(ComboBox_Afdeling.Selected.Value);
              Afdeling in ComboBox_Afdeling.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Cluster.Selected.Value);
              Cluster in ComboBox_Cluster.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Team.Selected.Value);
               Team in ComboBox_Team.SelectedItems;
               true
           );
           If(
               !IsBlank(ComboBox_CV.Selected.Value);
               Coördinator in ComboBox_CV.SelectedItems;
               true
           );
           If(
              !IsBlank(ComboBox_KV.Selected.Value);
              KV in ComboBox_KV.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Stadium.Selected.Value);
              Stadium in ComboBox_Stadium.SelectedItems;
              true
           )        
    )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Nicky_VDB
Level: Powered On

Re: Using multiple Combo Boxes to filter a Data Table

I had to change the formulas a bit, but it works now! This is what I ended up using:

Thanks!

    If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        If(
            !IsBlank(TextInput_Name.Text);
            Naam in TextInput_Name.Text || Voornaam in TextInput_Name.Text;
            true
        );
        If(
            !IsBlank(ComboBox_Stadium.Selected.Result);
            Stadium in ComboBox_Stadium.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_Afdeling.Selected.Result);
            Afdeling in ComboBox_Afdeling.Selected.Result;
            true
        );
                If(
            !IsBlank(ComboBox_Cluster.Selected.Result);
            Cluster in ComboBox_Cluster.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_Team.Selected.Result);
            Team in ComboBox_Team.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_CV.Selected.Result);
            Coördinator in ComboBox_CV.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_KV.Selected.Result);
            KV exactin ComboBox_KV.Selected.Result;
            true
        )
    )
)

Helpful resources

Announcements
thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,579)