Combo Box to Show 'All' as a Selection

[coolbeans23]

Hi,

I created a blank canvas app and in that I have a combo box that has a source from an Excel table called Table1 (the file resides on SharePoint).  The Excel table has a column called STATION (which has duplicates).  I wanted the combo box to list the unique stations after filtering another column called Action_Required.  I put the following formula for the Item property of the combo box and it returned the correct results:

Distinct(Filter(Table1, Action_Required = "Correction needed"),STATION)

However,  I would like to add an 'All' selection on the top of the drop down list that a user can select.  It won't necessarily highlight all the stations in the combo box but I just want that value so I can use it in a condition in my Power Automate flow.  Can this be done? Any other formula suggestions? I've tried a few suggestion provided by ChatGPT but the formula always returned an error like invalid argurments etc

[zmorek]

Collect it first (e.g. on screen visible, or with a button press) then insert "All":

ClearCollect(colWhatever,
Distinct(Filter(Table1, Action_Required = "Correction needed"),STATION)),
{Result: "All"}
)

Then use that collection as the Items() to your combo box.

[coolbeans23]

I'm sort of new to Power Apps, so if you could make it a little more clear that would help.  So do I create a button and put that formula on the On Select property? How would I use the collection in my combo box?

Is 'colWhatever' a variable? I tried putting the code to a button and the formula error said the 'colWhatever' was not recognized. Thanks!

[zmorek]

Hello there!

My apologies. Collections are a way to temporarily store data within your app. Think of collections like your shopping cart on your favorite website - you can put things in and take things out, but nothing's really "committed" until you press the "buy" button. That's what we're doing here.

ClearCollect() first takes a collection, removes everything from it, and then adds to it whatever you tell it to add. Collections need a name, and the first argument of the ClearCollect() statement is the name of the collection. You can call it whatever you want, so I picked "colWhatever," but you would probably call this "colStation". 

We then use the ClearCollect() function to take the table of data you already have created (the distinct result of your filter function) and then add another result ("All") to it.

ClearCollect() can be used on multiple properties, for example, the OnVisible() property of the screen that combo box is on. When a user navigates to that screen, PowerApps will execute that formula. This is a nice way to have things processing/loading in the background while the user continues to interact with the app.

It can also be used OnSelect() of a button, you're correct.

In this case I'd probably use the screen's OnVisible() property. Put this there:

ClearCollect(
colWhatever,
{Result: "All"},
Distinct(Filter(Table1, Action_Required = "Correction needed"),STATION))
)

Then navigate away from that screen, and back to it. Yay! You have a collection.

Now select your combo box, and select its Items() property. Set it to whatever you named the collection (in this example, colWhatever). 

Make a little more sense?

[coolbeans23]

Yeah that def makes more sense now. However, when I put the formula on the OnVisible property of the screen it gives an error when I hover over the formula:

The function 'ClearCollect' has some invalid arguments

colWhatever isn't recognized

I believe I have to correct this formula before adding the collection to the combo box.  Please advise.

[zmorek · Accepted solution]

I might have had too many parentheses in there.. Please try:

ClearCollect(
colWhatever,
{Result: "All"},
Distinct(Filter(Table1, Action_Required = "Correction needed"),STATION)
)

[coolbeans23]

Awesome! That worked. Is there a way that I can make it when 'All' is selected from the combo box the user cannot select other stations, but not the other way?

What does the {Result: part of the formula below mean? Why use curly brackets? Just trying to understand.

{Result: "All"}

[zmorek]

Woohoo!! I'm excited for you. Yes:

• Select your combo box, then select its SelectMultiple() property. Enter this:

Self.Selected.Result <> "All"

This allows the user to select multiple things so long as one of those things isn't "All"

• Then select its OnChange() property and enter this:

If(
    CountRows(Self.SelectedItems) > 1 && "All" in Self.SelectedItems,
    Reset(Self)
)

This will reset the combo box if the user selects multiple things, and then later selects "All". 

Curly brackets {} indicate a record. A record can be thought of like a row in a spreadsheet, and each part of the curly brackets is a column.

When you use Distinct() on a table, it returns a one-column table, whose "column header" is named Result. When we add in {Result: "All"} to the ClearCollect() statement, it's like us adding in another row to that one-column table, whose value is "All".

I'm not the best explainer, but I hope that helps! Please let me know if there's anything further I can do.

[coolbeans23]

Beautiful! And yes, your explanation was very helpful. I displayed the results of the combo to a label (especially when many stations are selected this comes in handy). 

Depending if All, multi-station (more than one), or a single station is selected I can retrieve the value from the label in Power Automate, correct? The end result is different emails tailored to the stations based on the three selections will be sent.  This will work right?

Just wanted to get your input.  Thank you so much again.

[zmorek]

Of course!

And yes, since you're using a combo box (a table of records), you'll have to Concat() its items to turn it into a string to show in a label. To see how this works, put this in your label's Text() property:

Concat(ComboboxName.SelectedItems, Result & ", ")

That will concatenate the values selected in the combo box with a comma!