Dropdown from a Dataverse choice column to show values from a different Dataverse choice column

(0) Share

Report

Posted on by bblatnjak

I am creating an Expense Tracker app, and I'm using a Dataverse table as the data source.

I have two choice columns:

Category - contains five main categories:
- Housing and Utilities
- Transportation
- Food and Groceries
- Personal and Health
- Financial and Miscellaneous
Subcategory - contains 25 options to choose (5 for each main category).

My goal is to create logic where each of the five subcategories corresponds to one of the main five categories. This way, when I select an option in the "Category" dropdown, I want to see the corresponding options in a different dropdown called "Subcategory." For instance, if I choose "Housing and Utilities" from the "Category" dropdown, I should only see the options related to housing and utilities in the "Subcategory" dropdown.

Please refer to the images below for visual reference:

Thank you in advance for your assistance.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @bblatnjak,

Unfortunately there is not really al link we can use between the categories and subcategories due to the use of choice columns. When using 2 choices instead of a relationship between Master Data tables, a workaround would be adding some additional text to the subcategories to define the category link. (e.g. [H & U] Rent/Mortgage, [T] Gasoline/Fuel...). This way you can simply filter on the subcategories that contain [H & U] should the first category be selected and so on...

We can however, leverage the fact that both categories and subcategories are in the same order. I may have overengineerd the code but the following code in the Items property of the subcategories dropdown did the trick for me:

//Only display the last 5 subcategories LastN( FirstN( Choices(TableName.SubCategoryColumn), //Display x amount of rows depending on the selection index (e.g. first selection, display 5, second display 10...) LookUp( //Add an index column to your Category choice list ForAll( Sequence(CountRows(Choices(TableName.CategoryColumn))), Patch( Index( Choices(TableName.CategoryColumn), Value ), {IndexVal: Value} ) ), Dropdown1.Selected.Value = ThisRecord.Value ).IndexVal * 5 ), 5 )

This code is also scalable, as long as you respect the current category-subcategory order. Meaning each category has to have 5 subcategories in that exact order.

Note: Change all references to TableName.CategoryColumn and TableName.SubCategoryColumn to the correct names. Dropdown1 should be replaced with the name of the category dropdown.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
bblatnjak 65 on at

Like
a
(0)

Report
Copy link

Link copied!

@LaurensM Thank you very much for your swift reply. I'll keep the code you made. You mentioned relationship between Master data tables. Would a better option be to have subcategories listed in a different table, is that what you meant? Thanks, BB

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @bblatnjak,

It really depends on the business case. In cases where your data is static, you could opt for a choice field.

In your case, the choice options will work as long as you don't stray away from your current set-up - being for each category you have 5 subcategories in the exact same sequence.

Issues start occurring once your data needs to change or the structure (1 - 5 ratio) suddenly changes and so on...

In cases where your data needs to be dynamic and/or there is a clear child-parent relationship between 2 optionsets, I tend to use separate master data tables instead of choice options. In my opinion, this gives the user more flexibility to add, modify or delete the dropdown options. It also allows you to provide more metadata (e.g. category description).

I have had cases where both category and subcategory were separate tables with a relationship between them. The main table would have a LookUp to both the Category and Subcategory tables - the relationship between the category tables allowed for easy filtering/hiding.

I hope this helps!

Was this reply helpful? Yes No
bblatnjak 65 on at

Like
a
(0)

Report
Copy link

Link copied!

@LaurensM Understood. Thank you for your kind help!

Was this reply helpful? Yes No