Filtering PowerApps Gallery By Dropdown & Multiple Dropdowns

While working on a Power Apps project, I got a challenge where I needed to filter the gallery based on a single and multiple dropdowns.

In this PowerApps case study I’ll demonstrate how to filter the PowerApps gallery using multiple dropdown controls and a dropdown menu.

  • I have a SharePoint List named “WorkTask” This list contains the fields below.
Column NameData Type
TaskThis is a Title column with a single line of text. I just renamed it to “Task”
DescriptionMultiple lines of text
StatusChoice
Start DateDate and time
End DateDate and time
  • In Power Apps, there is a Gallery control and a Dropdown control. The dropdown control has values like All, In Progress, Not Started, and Completed.
  • When a user selects any value from the dropdown, the gallery will display each record from the SharePoint list based on the selected dropdown choice field.

To achieve the above example, follow the steps below. Such as:

  • Open Power Apps -> Create Power Apps canvas app -> Connect the SharePoint list [WorkTask] to the app. Once you connect, it will look like the screenshot below.
  • Select a Power Apps Screen [WorkTask] -> Set its OnVisible property to the code below.

ClearCollect(

    colTask,

    {Value: “All”}

);

Collect(

    colTask,

    Distinct(

        WorkTask,

        Status.Value

    )

)

Where,

colTasks = PowerApps Collection

{Value: “All”} = Collection Item

Distinct() = This function can remove duplicate values from a data source

WorkTask = SharePoint Online List

Status.Value = SharePoint Choice Field Column Values

  • Next, insert a Dropdown control [drpStatus] -> Set its Items property as:
    Items = colTask
  • Where,
    colTasks- PowerApps collection
  • Insert a Gallery control and set its Items property as shown below:

If(

    drpStatus.Selected.Value = “All”,

    WorkTask,

    Filter(

        WorkTask,

        Status.Value = drpStatus.Selected.Value

    )

)

Where,

  • If() = This function helps us to evaluate multiple unrelated conditions
  • drpStatus.Selected.Value = “All”  PowerApps dropdown name and “All” is the collection item value
  • Save, Publish, and Preview the app. When the user selects a specific value from the dropdown control, the gallery will display the filtered records, i.e., [Completed], as in the screenshot below.
  • This is how to filter a PowerApps gallery by dropdown choice field.

Power Apps Filter Gallery By Dropdown Date

Next, we will discuss how to filter a PowerApps gallery by dropdown date.

Example: I have a SharePoint list named “Training Courses“. This list contains the below fields.

Column NameData Type
Course NameThis is a Title column with a single line of text. I just renamed it to “Course Name”
DescriptionMultiple lines of text
Course Start DateDate and time
Course End DateDate and time
  • In Power Apps, there is a Horizontal gallery control; this gallery displays each record from the SharePoint list based on the date selected in the dropdown menu.
  • To work around the above example, follow the below steps.
  • On the PowerApps -> Connect the SharePoint list, i.e., [Training Courses] to the app. Once you connect, it will look like the screenshot below.
  • Insert a Dropdown control and set its Items property as:

Items = Distinct(

    ‘Training Courses’,

    ‘Course Start Date’

)

Where,
‘Training Courses’ = SharePoint Online List
‘Course Start Date’ = SharePoint Date Field

  • Then, insert a Gallery control and set its Items property as:

Filter(

    ‘Training Courses’,

    ‘Course Start Date’ = drpStartDate.Selected.Value

)

Where,

‘Training Courses’ = SharePoint Online List

DrpStartDate- Power Apps Dropdown Name

  • Save, Publish, and Preview the app. Select any particular date from the dropdown control, and the gallery will filter and display the records based on that date.

This is how to filter a PowerApps gallery by dropdown date.

Power Apps Filter Gallery By Dropdown and Search

Let’s see how to filter a PowerApps gallery by dropdown control based on the search.

  • Example: I will take the same SharePoint list [Training Courses] for this example.
  • In Power Apps, there is Gallery control, Text input, and Dropdown control. This gallery displays each record from the SharePoint list based on the dropdown control and search results.
  • I faced a delegation warning [Yellow triangle]. The warning message appears as “Delegation warning. This formula’s “Search” part might not work correctly on large data sets.
  • I created a Power Apps collection [colCourses] using my SharePoint list to overcome this Power Apps delegation warning. Then, I filtered a Power Apps gallery by dropdown without delegation warning, 

On the Power Apps canvas app -> Select App object and set its OnStart property as:

ClearCollect(

    colCourses,

    ‘Training Courses’

)

Where,

colcourses = PowerApps Collection Name

‘Training Courses’ = SharePoint Online List

  • Insert a Text input, make its Default property blank [“”], and set its Hint text property as “Search Course Name” like below.
  • Then, insert a Dropdown control and set its Items property as:

Items = Distinct(

     colcourses,

    ‘Course Start Date’

)

.

Where,
colcourses = PowerApps Collection

‘Course Start Date’ = SharePoint Date Field

  • Next, insert a Gallery control and set its Items property to the code below:

Items= Search(

    Filter(

         colCourses,

         ‘Course Start Date’ = srpStartDate_1.Selected.Value

     ),

     TextInput1.Text,

     ‘Course Name’

)

Where,

Search()= This function allows users to search for and filter items in a PowerApps gallery.

SrpStartDate= PowerApps Dropdown

TextInput1= PowerApps Text input

‘Course Name’= SharePoint Text column

  • Save, Publish, and preview the app. This gallery will display the filtered records based on the search results and selected dropdown value as in the screenshot below.

This is all about how to filter a Power Apps gallery by dropdown and search.

How to Filter a PowerApps Gallery With Multiple Dropdowns

Now, we will see how to filter a PowerApps Gallery With Multiple Dropdowns step by step.

Set up a SharePoint List

First, we will create a SharePoint List named IT Help Desk with the various columns:

Column NameData Type
Request IDNumber
SubjectTitle (Single line of text)
Request DateDate and time
CategoryChoice (Servers, User Workstation, Basic Software, Data Center)
PriorityChoice (Very High, Medium, Normal)
ProgressNumber
Request UserPerson
  • Look at the SharePoint Online list below:

Create a Power Apps Blank Canvas app

Next, we will create a new blank canvas app in Power Apps and add some input controls. Follow the instructions below:

  • Sign in to Power Apps with valid Microsoft credentials.
  • Go to Apps -> Expand + New app -> Select Canvas.
  • Provide the App name and choose the Table Format -> Click on Create.
  • Connect the SharePoint List Datasource connector (IT Help Desk) to the app.
  • Next, insert three Dropdown controls and rename them to ddCategory, ddPriority, and ddReqUser.
  • Now, I want to retrieve all the dropdown values from the specific SharePoint list columns, namely Category, Priority, and Request User.
  • I also want to give each dropdown control a blank value. Every value from the SharePoint list, including a blank value, will be available in the dropdown.
  • To do so, we will create a collection with a blank value on the screen’s OnVisible property as:

OnVisible = ClearCollect(

    CategoryWithBlank,

    {Category: Char(160)}

);

Collect(

    CategoryWithBlank,

    Distinct(

        ‘IT Help Desk’,

        Catagory.Value

    )

);

ClearCollect(

    PriorityWithBlank,

    {Priority: Char(160)}

);

Collect(

    PriorityWithBlank,

    Distinct(

        ‘IT Help Desk’,

        Priority.Value

    )

);

ClearCollect(

    ReqUserWithBlank,

    {ReqUser: Char(160)}

);

Collect(

    ReqUserWithBlank,

    Distinct(

        ‘IT Help Desk’,

        ‘Request User’.DisplayName

    )

);

  • Where,
    CategoryWithBlank, PriorityWithBlank, ReqUserWithBlank = Provide the Collection Names,
    Category, Priority, ReqUser = Specify the Collection Headers,
    ‘IT Help Desk’ = SharePoint List Name,
    Category.Value, Priority.Value = SharePoint Choice Columns,
    ‘Request User’.DisplayName = SharePoint Person Column,
  • Then, set these collections to each and every Dropdown’s Items property:
    Category: Items = CategoryWithBlank
    Priority: Items = PriorityWithBlank
    RequestUser: Items = ReqUserWithBlank
  • Save and Close the app. Open and Preview the app once again; you can see the outputs below:
  • Next, add a Vertical Gallery Control and set its Layout to Title, subtitle, and body. Apply all the code on each gallery label’s Text property as:
  • Then, Select the Gallery Control and set its Items property to the code below:

Items = Filter(

    ‘IT Help Desk’,

     ddCatagory.Selected.Value = Blank() Or Catagory.Value = ddCatagory.Selected.Value,

     ddPriority.Selected.Value = Blank() Or Priority.Value = ddPriority.Selected.Value,

     ddReqUser.Selected.Value = Blank() Or ‘Request User’.DisplayName = ddReqUser.Selected.Value

)

  • Where,
    ddCatagory, ddPriority, ddReqUser = Dropdown Control Names

Catagory, Priority = SharePoint Choice Columns
‘Request User’ = SharePoint Person Columns

  • Save the app and then run a preview. The gallery will filter and show all the SharePoint list records when all the dropdown controls have empty values.
  • When you choose options from the various drop-down controls, the gallery filters and shows those specific values by your drop-down selections.


Before Applying Filter

After Applying Filter

  • This is how to filter a PowerApps Gallery With Multiple Dropdowns.
Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *