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.
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.
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!
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.
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
)
)
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.
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"]
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.