Creating a cascading dropdown option, from one table, using two choice columns

Like (0) Share

Report

Posted on 26 Dec 2021 16:46:18 by wilfordm81

Hi there,

Happy Holidays to you all!

I'm having difficulty creating a cascading dropdown from a single, custom Dataverse table. There are two columns wherein I want one to be dependant upon the other, both are "Choice" data-type columns. These drop-downs are currently displayed in a form, which when a new form is updated/submitted, it submits the data directly to the Dataverse table.

The first column, is titled Energy Group, and has three choices: Gas, Electricity & Water.

The second column, is titled Energy Type, and has ten choices: gas one, gas two, water one, water two, water three, water four, water five, electricity one, electricity two & electricity three.

Naturally, I only want the respective energy types showing, when the energy group has been selected.

So far, I've used Youtube tutorials, (which have been based on Sharepoint lists, although i know the premise is the same).

The first solution, is to create a formula on the energy-types dropdown datacard, using the right hand side "depends-on" toggle, and the generated solution created, is:

Filter('Jab Readings', 'Energy Types' = DataCardValue14.Selected.Value) - this creates two errors. One against the datacard, and one against the '=' value in the formula.

If I use DISTINCT, against the energy groups datacard, to filter the choices, the "Update" parameter errors out, and leaves the dropdown inert.

Thank you in advance if anybody could shine a light on this, as I'd be very grateful.

Sincerely

Categories:

Using Apps Using Formulas

All responses (2)

Answers (1)

wilfordm81 72 on 28 Dec 2021 at 10:49:43

Like (0)

Report

Re: Creating a cascading dropdown option, from one table, using two choice columns

Hi @v-JessicaG-msft,

Happy holidays, and thanks for taking the time to work through this scenario!

The first option worked a treat, however one alteration I made was to remove the "dependants" from the Choices element of the formula.

Thanks again, and have a Happy New Year!

W
Verified answer

v-JessicaG-msft on 28 Dec 2021 at 10:23:40

Like (1)

Report
Re: Creating a cascading dropdown option, from one table, using two choice columns

Hi @wilfordm81 ,

According to your description, I thought of two solutions.

1. If there is no cascading relationship between the Energy Group and Energy Type columns, you can use the "filter" and "startwith" functions in the dropdown to filter the Energy Group column values to the Energy Types column values. I have test for your reference, please try the following workaround:

Formula:

Filter(Choices(dependants.'Energy Type'),StartsWith(Value,DataCardValue1.Selected.Value))

2. If you want to create a cascade dropdown to present the data, you could create another table to relate the two columns of data.

The specific solution can be found at this link:

https://www.matthewdevaney.com/create-cascading-dependent-dropdowns-in-power-apps/

Best Regards,

Jessica Gu