cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter Choices for Combo Box

Most of what I've already found online is related to the old LookUp, so I figured I'd start a new question.

 

I'm trying to filter a Combo Box in a form so that it only contains valid/relevant options.

 

As an example, let's say I have a DataSource that's a table filled with various Olympians and I want a drop down where you select someone by name.

By default, the options for this field are set as:

Choices('Olympians'.Name)

which would display as options for the drop down every name found in the Name column in the table.

 

But, I want to limit their options to only, say, entries in the table that have their country set as Canada in another column.

 

The user can only access this form after selecting their country, so I don't want them editing forms and making Team Canada contain Team USA members, you know?

 

I've tried filtering it like this, but it didn't seem to work. Is there an error here, or is my understanding of how filtering works wrong?

Choices(Filter('Olympians', country_column = wantedCountryVariable).Name)

(The form used to use LookUps and after updating, it used this Choices() function I've not seen before, so pardon the possibly dumb usage of it)

 

UPDATE WITH MORE DETAILS

 

Here's more detailed information about my problem, which I'll edit the original post with for anyone else who happens to pop up in here:

 

In the actual data source I'm working with, it's a bunch of products where each row represents a product from a client. Not all products are associated with a support contract, but the user should be able to look through a list of the support contracts they have for other products and possibly apply it to another product (the combo box in question is this list). 

 

Using Choices(), this is why it's getting 1. All support contracts listed in the table, and 2. Blank ones, for the products that don't have contracts listed in the table/applied to the product. I want to filter the support contracts shown in the drop down by not displaying the blank ones and also only displaying the ones that are associated with the right client. There's a column in the table for contracts, and there's a column for client, so theoretically I thought something like this

 

Filter('Products', !IsBlank(Contract), ClientName = CurrentCustomer)

Would work, but so far no luck. This doesn't filter it at all and it also says it doesn't support the not operator. 

I'm pretty stuck at the moment!

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Filter Choices for Combo Box

I finally got it! Thanks for all your ideas and help over the past few days. Also got help from this post.

 

This doesn't necesarily filter the options by country like in my example scenario, but I did manage to finally get it to stop showing a bunch of blank options, which is what I'm willing to settle for (and could probably easily do in the same way now that I know how). When using Choices('DataSource'.Column), it included blank values because of entries in the data source that left that column blank. In order to filter out the blanks, I did the following:

 

OnVisible:

ClearCollect(colDropDownOptions, Filter(Choices('DataSource'.Column), Not(IsBlank(Value))))

Then, in the Combo Box itself, I set Items to colDropDownOptions.

 

I tried just putting Choices('DataSource'.Column) in the collection at first, and noticed that the preview for the first five values all had a column called Id that contained numbers, and a column caled Values that was blank. At first I thought something went wrong, then I realized that those were the blank values I was getting! I finally got them to be filtered out after way too long.

 

Thanks again everyone! I feel dumb now, but happy, lol.

 

View solution in original post

21 REPLIES 21
Sienna
Level 10

Re: Filter Choices for Combo Box

I have never use and never seen choices before. And it is also not listed in function reference page. Try to get rid of it
Anonymous
Not applicable

Re: Filter Choices for Combo Box

So how do I go about populating the Combo Box?

 

I'm assuming Choices is a newer function and has no reference on the reference page yet, because it's used in every combo box I've got so far.

The tool tip says it takes the values found in a given column and uses them to populate the Combo Box. It seems like exactly what I'm looking for, but lacking in an ability to filter the results.

Sienna
Level 10

Re: Filter Choices for Combo Box

Ok Like I said I never heard of it so far and there is no reason to use it. Or at least I don’t see a reason here. Just put in your Items property

Filter('Olympians', country_column = wantedCountryVariable)

And set DisplayFields property of the combobox to whatever you want to display in your combobox
Anonymous
Not applicable

Re: Filter Choices for Combo Box

For some reason, I'm having a lot of difficulty getting this to work for the actual data source I'm using. I'll keep messing with it until I get somewhere though, because that looks like it should be right. The drop down appears exactly as it was before with Choices(), so it's as if it's not filtering anything at all. Another attempt I made by filtering by some other value left me with the right number of options, but all of them were blank. My theory is that it's displaying the wrong field of options, but it's limiting me to pick from a very specific list of fields which doesn't include the one I need.

 

Here's more detailed information about my problem, which I'll edit the original post with for anyone else who happens to pop up in here:

 

In the actual data source I'm working with, it's a bunch of products where each row represents a product from a client. Not all products are associated with a support contract, but the user should be able to look through a list of the support contracts they have for other products and possibly apply it to another product (the combo box in question is this list). 

 

Using Choices(), this is why it's getting 1. All support contracts listed in the table, and 2. Blank ones, for the products that don't have contracts listed in the table/applied to the product. I want to filter the support contracts shown in the drop down by not displaying the blank ones and also only displaying the ones that are associated with the right client. There's a column in the table for contracts, and there's a column for client, so theoretically I thought something like this

Filter('Products', !IsBlank(Contract), ClientName = CurrentCustomer)

Would work, but so far no luck. This doesn't filter it at all and it also says it doesn't support the not operator. 

I'm pretty stuck at the moment!

Sienna
Level 10

Re: Filter Choices for Combo Box

To filter product from blanc and client name use

 

Filter('Products', !IsBlanc(Contract) && ClientName=CurrentCustomer) 

 

Anonymous
Not applicable

Re: Filter Choices for Combo Box

No difference again, nothing is being filtered out. I just get more Suggestions saying ! and && can't be evaluated remotely.

 

Thanks for sticking with me, lol.

Sienna
Level 10

Re: Filter Choices for Combo Box

I think there is some limitation to isblank function. Im not at my computer so can’t test it but you can try to replace the isblank to !Contract=“”
Try it and see what you get
Anonymous
Not applicable

Re: Filter Choices for Combo Box

Same issue, not filtering anything at all.

Sienna
Level 10

Re: Filter Choices for Combo Box

Ok so try to display CurrentCustomer variable in one label and see what value you get. If the label will be empty then you know the problem is in variable

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 334 members 7,088 guests
Please welcome our newest community members: