cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonykiefer
Advocate IV
Advocate IV

Cascading Dropdowns using a reference table of lookup values

I'm building a form that has a section of Dropdown lists that should be Cascading Dropdowns.  The data in each dropdown is populated from a SQL table.  I don't think this is a usual scenario and I'm having some trouble.  

 

First let me say the db design might not be the best but it's what I have to work with and it gets updated regularly so I have to keep it.  The table that should drive the cascading dropdowns is...

Care Instruction Table.jpg

 I made some highlights for reference as I move on.

 

The form looks like this...

FormView Cascading Dropdowns.jpg

 

The first dropdown("Care Instruction Code") drives the population of the cascading lists and is a list of the USR_EPI_CL_CODEs(as highlighted above).  Each code has any combination of supporting codes/columns (USR_AX_PREP_CODE, USR_AX_DRYCLEAN_CODE, USR_AX_BLEACH_CODE...).

 

The supporting code column names are references to the associated table name that will contain entries for the next dropdown.  So depending on which USR_EPI_CL_CODE is chosen...the following dropdown lists should be populated by the reference table and the Code is the reference for the highlighted suggested entry.  

 

Also...in this example of the BackPack...if a supporting column does not have a reference code...the dropdown list should still be populated but without a highlighted suggested entry.

 

In this example the Care Instruction Code dropdown is set to "Backpack".  The USR_AX_PREP_CODE for backpack is 14

 

The Care Preparation table (named USR_WASH_PREP) is...

WashPrep.jpg

As a result...The second dropdown "Care Preparation" should be populated with the USR_Description entries from USR_WASH_PREP with focus set to USR_CODE 14 "AVOID SATURATION WITH LIQUIDS".

 

NOTE:  For the NewForm functionality the Cascading Dropdowns should be prepopulated with the "Suggested Values"...I say "Suggested Values" because they are not hard and fast rules, they are guides.  The users may changed the values.  The EditForm functionality will show the user entered values by setting the "Default" value.  Ex: LookUp(USR_WASH_PREP,USR_CODE=Parent.Default,USR_DESCRIPTION)

 

I would appreciate any help in this matter.  I tried using the "Depends on" feature in the properties but it seems to be geared for a one-to-one looked.  Since I need to use this "middle" supporting table, things got a little difficult for me.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tonykiefer
Advocate IV
Advocate IV

If figured out my mistake.

 

When I was working on the "Depends on" feature in the DropDown properties I was setting the Parent Control code to the USR_EPI_CL_CODE.  The correct solution is for each dropdown to set the Parent Control code value to the associate column reference as such.  

 

Solution.jpg

 

View solution in original post

1 REPLY 1
tonykiefer
Advocate IV
Advocate IV

If figured out my mistake.

 

When I was working on the "Depends on" feature in the DropDown properties I was setting the Parent Control code to the USR_EPI_CL_CODE.  The correct solution is for each dropdown to set the Parent Control code value to the associate column reference as such.  

 

Solution.jpg

 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,688)