Dynamic Power Apps Dropdown Box

In the last post I set myself the challenge of creating an dynamic Power Apps Dropdown box that shows the number of records in a table that match the Dropdown selection criteria. The requirement was to not have hardcoding so any changes to the records in the table are reflected in the Dropdown. This includes both new ‘categories’ for the dropdown and also the number of records against each category

I couldn’t resist the temptation to crack on and give this a go – and it turns out it really isn’t that complicated!

Take a look at the demo below and checkout the bracketed number in the Dropdown box that matches the number of records in the Gallery

Dynamically Counting the Number of Records

Using ForAll, Distinct, Filter and CountRows to Create a Collection

First create a Collection which I’ve called CountryCount. The Collection is created using the ‘Load’ Button. The Collection has two columns. The Continent column holds the name of the Continent and the Number column contain the number of Countries in the source table for that particular Continent

The code for the OnSelect property of the Button is shown below

The first line Clear(colCountryCount) clears the existing colCountryCount Collection. This is because the Collect function can be used inside the subsequent ForAll function but ClearCollect cannot. Imagine if ClearConnect was used inside a ForAll loop. Every time the ForAll loop runs a cycle then the data created in the previous cycle would be removed!

Now on to the main business. It is always worth thinking through the approach before starting to code. To get the output we are looking for, it’s pretty clear that we need to use the ForAll, Filter, CountRows and Distinct functions, but in what order? I like to think it through logically to understand exactly what I want to happen. It works as follows:

ForAll the Distinct values of Continent within the Country table, add the following two Columns to the Collection called colCountryCount. First add the name of the Continent specified in the current iteration of the ForAll loop (referenced by the Result parameter). Then add the number of rows remaining when the Country table is filtered using the current Continent in the ForAll loop (again specified by the Result paramenter)

The resulting code below is assigned to the OnSelect property of the ‘Load’ Button

Clear(colCountryCount);

ForAll(
   Distinct(
      Country,
      Continent
   ),
   Collect(
      colCountryCount,
      {
         Continent: Result,
         Number: CountRows(
            Filter(
               Country,
               Continent = Result
            )
         )
      }
   )
)

Once the Load Button is clicked and the code has run, the existing records in the colCountryCount Collection are cleared and the records opposite are created

You can see in the demo that there is a very small delay in creating the Collection, though this is only a small table. For a working App we would run the code when the App loads or perhaps when the screen is made visible to both minimise or remove the delay altogether and to avoid the need for a user to take any action to load the data

If the table is large, particularly if the number of records is greater than 2000, then delegation limits will be hit. This is because the CountRows function is not delegable. Under those circumstances, create a seperate table to calculate the totals directly within the Data Source and access via a Lookup. To see how to do this in SharePoint, checkout Matthew Devaney’s excellent blog post: Sum Rows in a Sharepoint List with no Delegation Warnings. Alternatively, if the data is static, or changes very infrequently it could be hard coded directly into the App

Now the Collection has been created, let’s look at how we use the data in the Dropdown box

Using the Collection Records in a Power Apps Dropdown Box

SortByColumns(
   AddColumns(
      colCountryCount,
      "CountPerContinent",
      Continent & " (" & Number & ")"
   ),
   "CountPerContinent"
)

The code opposite is assigned to the Items property of the Dropdown box

Using AddColumns, a new ‘virtual’ column is created within the colCountryCount Collection called CountPerContinent. We use the ‘&‘ operator to join the existing columns in colCountryCount to create the fields of the new column. Remember, the new column only exists within the Dropdown box itself, it isn’t actually added to the Collection – so don’t expect to physically see it

Inserting the Gallery

All we need to do now is create a Gallery and assign its Items property as below and we are up and running!

Although we are directly selecting the column CountPerContinent from the Dropdown box, we can reference other columns in the Collection using the structure: Dropdown.Selected.COLUMN

Filter(Country,Continent=Dropdown.Selected.Continent)

Summary

Now the Dropdown is up and running we could embelish further by using the technque discussed in the previous post to create an ‘All’ box (also with a record count) to be the first option for the Dropdown

I hope you get to use this technique or something similar in your Apps. Just be aware of the potential user experience impact of the time required for the ForAll function to loop through every record, and use where appropriate

More Info

Hiredgun.tech: 3 Really Cool Power Apps Dropdowns
Hiredgun.tech: Power Apps Dropdown Boxes
Hiredgun.tech: Multiple Search Options
Power Apps Reference: Filter
Power Apps Reference: SortByColumns
Power Apps Reference: AddColumns & ShowColumns
Power Apps Reference: ForAll
Power Apps Reference: Distinct
Power Apps Reference: CountRows