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

Conditionally Filter a Table Based on a Drop Down

I have a Data Table with Items being pulled from a Table (not a collection). I am trying to use a drop down with the following values ["","Sales","Human Resource"] to conditionally filter the table. Code works correctly if Sales or Human Resources is selected but shows nothing with the blank "" value is selected. My filter code:

Filter(
    Master,
    txt_EmployeeColumnFilter.Text in 'Employee Name',
    If(dd_DeptColumnFilter.SelectedText.Value <> Blank(),
        dd_DeptColumnFilter.SelectedText.Value in Dept
    )
)

What I want is if the drop down value selected is empty/NULL/blank then the Dept filter is not applied. Tried many combinations of this and each time I select "" no records are displayed in the table.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@fmc1 

This is because you changed the column name.

When you use: ["","Sales","Human Resource"] This syntax denotes a table of records.  Since the column name is not provided, PowerApps will assume Value for the column name.

A collection is overkill for what you are doing as it is an in-memory database object, I would simply set a variable instead.

If you want to keep the Filter formula the same, then use the following:

Set(deptValues,
    Table(
      {Value: ""},
      {Value: "Sales"},
      {Value: "Human Resources"}
    )
)

Then set your Items property to : deptValues

 

If you want to specify the column differently:

Set(deptValues,
    Table(
      {deptVal: ""},
      {deptVal: "Sales"},
      {deptVal: "Human Resources"}
    )
)

Then you need to change the column in your formula to:

Filter(Master,
    txt_EmployeeColumnFilter.Text in 'Employee Name' &&
    (IsBlank(dd_DeptColumnFilter.Selected.deptVal) ||
     dd_DeptColumnFilter.Selected.deptVal in Dept
    )
)
_____________________________________________________________________________________
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

8 REPLIES 8
RandyHayes
Super User
Super User

@fmc1 

Please consider changing your Formula to the following:

 

Filter(Master,
    txt_EmployeeColumnFilter.Text in 'Employee Name' &&
    (IsBlank(dd_DeptColumnFilter.Selected.Value) ||
     dd_DeptColumnFilter.Selected.Value in Dept
    )
)

 

 

I hope this is helpful for you.

 

EDIT: The above formula has been changed as I noticed you were using SelectedText in it.  That is a deprecated property and should be avoided.  It is corrected above.

_____________________________________________________________________________________
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!
zmansuri
Super User
Super User

Filter( Master, txt_EmployeeColumnFilter.Text in 'Employee Name',
Dept = dd_DeptColumnFilter.SelectedText.Value || dd_DeptColumnFilter.SelectedText.Value = "All")

 

Add All in your dropdown:

 

["All","Sales","Human Resource"]

---------------------------------------------------------------------------------------------------------------------------


Was I helpful? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. It will help other users to find it faster.

follow me on Twitter @zmansuri123,
connect with me on LinkedIn from Here

That fixed the problem and now I understand the logic on filters. But, if I remove my hard coded drop down dept values ["","Sales","Human Resources"] and replace it with a collection that includes the same values, the data table again shows no results. Collection is the following:

ClearCollect(
    deptValues,
    {deptVal: ""},
    {deptVal: "Sales"},
    {deptVal: "Human Resources"}
)

I updated my drop down Items = deptValues. They show correctly but break the data table filtering. 

RandyHayes
Super User
Super User

@fmc1 

This is because you changed the column name.

When you use: ["","Sales","Human Resource"] This syntax denotes a table of records.  Since the column name is not provided, PowerApps will assume Value for the column name.

A collection is overkill for what you are doing as it is an in-memory database object, I would simply set a variable instead.

If you want to keep the Filter formula the same, then use the following:

Set(deptValues,
    Table(
      {Value: ""},
      {Value: "Sales"},
      {Value: "Human Resources"}
    )
)

Then set your Items property to : deptValues

 

If you want to specify the column differently:

Set(deptValues,
    Table(
      {deptVal: ""},
      {deptVal: "Sales"},
      {deptVal: "Human Resources"}
    )
)

Then you need to change the column in your formula to:

Filter(Master,
    txt_EmployeeColumnFilter.Text in 'Employee Name' &&
    (IsBlank(dd_DeptColumnFilter.Selected.deptVal) ||
     dd_DeptColumnFilter.Selected.deptVal in Dept
    )
)
_____________________________________________________________________________________
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!

Both solutions worked. But then I thought I would make the dept values dynamic based on the underlying data:

ClearCollect(deptValues,{value:""},Sort(Distinct(Master,Dept),Result,Ascending));

I run the following on startup and it works perfectly. At some point the list of departments will come from its own table but for now this will work.

RandyHayes
Super User
Super User

@fmc1 

Your formula has a mismatch of columns.  You are specifying a value column in the first record and then adding records that have a Result column from the Distinct function.  These will not compare.

 

I would skip the collection for this...It is overkill and adds to performance drags in your app!

If you want a more dynamic way, then set your Items property to:

Ungroup(
    Table(
        {Items: Table({Result:""}) },
        {Items: Sort(Distinct(Master,Dept),Result)}
    ),
    "Items"
)

This will provide an accurate Items property to your control and will add the blank to the Distinct list of the Dept records.

 

Why this over the collects?  

1) It does not drag your app with collections

2) It will be completely dynamic

3) If you even need to maintain the Items property, it will be in one place - no need to see that it is based on a collection and then try to figure out where the collection is set (you cannot search for collections and see where they are defined in PowerApps...so you're left with trying to figure it out on your own)

 

IF you want to make it more based on the OnStart of the app, then use a Variable NOT a collection.  For the main point of number 3 above.  You CAN search on Variables, you cannot search on collections.

So, in your OnStart:

Set(glbDepartments,
  Ungroup(
    Table(
        {Items: Table({Result:""}) },
        {Items: Sort(Distinct(Master,Dept),Result)}
    ),
    "Items"
  )
)

Then change your Items property of the Dropdown to : glbDepartments

 

At least with that, if you ever need to maintain something about the Items property, you will see it is a variable and can then just look that up to see where it is defined...again, can't do that with a collection!

_____________________________________________________________________________________
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!
fmc1
Helper I
Helper I

I wanted to use Table but did not think I could include a filtered table. I guess I need to find some good documentation on the differences in performance and overhead using collections, tables and data source tables/lists. Most of what I read talks about using collections and at first, they appeared more flexible to work with.
 
Thanks for the information.
RandyHayes
Super User
Super User

@fmc1 

Yes, collections are WAY over used!!  They are needed only when you need an in-memory database/table that allows you to add, remove and change records in memory.  There are other situations where they are needed, but they are rare and yet everyone seems to want to use them.

A collection is just a table and it, as mentioned above, allows you the add, remove, and change ability.  

The WORST part of collections is that you cannot find where they are defined or altered.

A variable on the other hand, can also be a table.  A variable is static and is only changed when you change it in a formula.  However, especially in your case, a department list is probably not something you need to add, remove or change records in memory for.  So the variable is preferred (if a variable at all is needed) because it will not have the overhead of the add, remove, and change logic in the app.  It will just be the table.  PLUS, you CAN search on variables and find where they are defined and used.

Ultimately, it is better to just get the data from the datasource as it is already a "sort of" collection in the app.  But that choice depends a lot on how dynamic things need to be.

 

So, just keep in mind - collections are tables that are editable.  Variables can be anything, including tables and records.  Datasources are the source of the data and are always tables.

 

I hope this is clear and helpful.

 

_____________________________________________________________________________________
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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

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