In this blog we will see how to filter records on record status based on value selected in drop down. In Dynamics 365 we can configure views with different filter conditions. In PowerApps there is no view concept but based on value selected in drop down list, we can filter records in Gallery list or data table. This behavior can be extended further to replicate view concept in Dynamics 365. Let’s see how this works.
- I have entity named “Company” and Gallery control showing all Company records (Active and Inactive both) by default as shown below.
- This is because when you Canvas App using CDS for Apps template, Items property of Gallery is set with below formula with no filters into it. Below formula will enable search on company name and sort records in Gallery.
SortByColumns(Search(Companies, TextSearchBox1.Text, “cr2a5_companyname”), “cr2a5_companyname”, If(SortDescending1, Descending, Ascending))
In order to filter list based on drop down selection, lets add drop down control on top of Gallery control and add two values as: Active Companies & Inactive Companies
- Click on Drop down option in Controls menu from Insert tab which will add drop down control on form
- Align drop down control so that it appears right above Gallery control. Set Items property of drop down control to [“Active Companies”, “Inactive Companies”] to add these two values
Now we need to update Items property of Gallery control to include Filter function based on value selected in drop down
- Select Gallery control, update Items property as below which will filter active or inactive records using statuscode value based on value selected from drop down
SortByColumns(Search(Filter(Companies,If(Dropdown2.Selected.Value = “Active Companies”, _statuscode_label = “Active”, _statuscode_label = “Inactive”)) , TextSearchBox1.Text, “cr2a5_companyname”), “cr2a5_companyname”, If(SortDescending1, Descending, Ascending))
- As soon as you update Items property with above formula, Gallery list will be filtered based on drop down value. As shown above Gallery list only shows Active records as “Active Companies” is selected in drop down
- Let’s understand what above formula does. We added Filter function within Search function to filter data source “Companies”. If condition is used to check for drop down value, if “Active Companies then retrieve all records where statuscode value is “Active” else “Inactive”
Run the app, change drop down value and Gallery list will be filtered accordingly.