Selective list for dropdown

[Msel]

I have a list in excel I have used as a data source for a dropdown list in a form which is working well. However I now only want to display options from this list in the dropdown that are not listed in another table. Any suggestions on how I might do this?

[Carsten_Growth]

However I now only want to display options from this list in the dropdown that are not listed in another table. Any suggestions on how I might do this?

---

What is meant by this? Another table in Excel?

[Msel]

Sorry yes another list in excel

[Carsten_Growth]

Two ways:
Either filter the list you´ve used as your datasource first inside Excel or
bring both lists as datascources into your application and then filter on the results inside your app and use the result as your dropdownlist items, instead of your Excel list

[timl]

Hi @Msel 

Let's suppose you have an Excel table of Customers. The unique identifier of this table is a field called CustomerID

You now want to show all customers that do not appear in an Excel table called Orders.

The formula you would add to the Items property of your drop down would look like this:

Filter(Customer,
        Not(CustomerID in ShowColumns(Orders, "CustomerID"))
)

[v-xida-msft]

Hi @Msel ,

Could you please share a bit more about the formula you used within the Dropdown Items property?

Do you want to restrict the available options within the Dropdown box based on the existing options in your another table?

Based on the needs that you mentioned, I think the Filter function could achieve your needs. Please consider set the Items property of the Dropdown box to following formula:

Filter(
       Distinct(Table1, Table1_ColumnName),
       Not(Result in Table2.Table2_ColumnName)
)

Note: The Table1 represents your current Table data source, the Table2 represents your another Table. The Table1_ColumnName (which is used to populate the Dropdown list) represents the column in your Table1, the Table2_ColumnName (which is used to store the option within the Dropdown box) represents the column in your Table2, on your side, please replace it with actual column name from your Table data source

In addition, you also need to bind the selected option from the Dropdown box to the field in your Edit form, please set the Update property of the field data card in your Edit form to following:

Dropdown1.Selected.Result

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Msel]

Thank you for the responses.

Sorry I should have provided further information to start with.

I have two tables. I want to display the items in the dropdown that appear in table 1 but not table 2.

For example;

Table 1                                             

Colours

Red

Blue

Green

Yellow

Orange

Purple

Table 2

Used Colours

Red

Blue

Yellow

So in this example I want Yellow, Orange and Purple to appear in the dropdown.  Hope this makes more sense. I currently have both excel tables available as separate data sources

[timl]

Hi @Msel 

>>So in this example I want Yellow, Orange and Purple to appear in the dropdown. Hope this makes more sense. I currently have both excel tables available as separate data sources

Did you mean to say Green, Orange, and Purple, or did you actually mean Yellow, Orange and Purple?

If it's the former, then this will give you the result that you need.

Filter(Table1,
        Not(Colours in ShowColumns(Table2, "UsedColours"))
)

[v-xida-msft · Accepted solution]

Hi @Msel ,

Do you want to display only Green,  Orange and Purple within the Dropdown box?

Please consider take a try with the following workaround. Set the Items property of the Dropdown box to following:

Filter(
       Distinct(Table1, Colours),
       Not(Result in Table2.'Used Colours')
)

when you want to reference the selected option from above Dropdown box, please use the following formula:

Dropdown1.Selected.Result

If this Dropdown box is within a Edit form, please bind the Update property of the field data card to the selected option within above Dropdown box. Set the Update property of the Colours field data card in your Edit form to following:

Dropdown1.Selected.Result

Note: Please make sure you set the "Result" as Display column value within the Dropdown box.

Please consider take a try with above solution, check if the issue is solved.

Best regards,