cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iamglt
Helper V
Helper V

Filtering One Drop down box based on the value of another drop down box

Hi,

I have two fields - one Called 'Division' and the other called 'Business Unit'.  There are many business units under each division.  For example:

SP Field: Division:

Corporate

SP Field: Business Unit:

HR

Finance

Legal

IT

SP Field: Division:

Public Engagement

SP Field: Business Unit:

Business Unit A

Business Unit B

Business Unit C

Business Unit D

SP Field: Division:

Directorate

SP Field: Business Unit:

Business Unit E

Business Unit F

Business Unit G

Business Unit H

 

What I would like to do is select the Division in the drop down box, and the Business Unit drop down field will only display the the records related to that particular Division.  For example, if I pick the division of 'Directorate', then only Business Unit E,F,G and H will be available from the in the Business Unit drop down box.  Both data cards (Division and Business Unit) write back to their respective SharePoint fields.  I have created a separate SharePoint list called Business Unit (which has been added as a data source) with the following info:

 

Division, Business Unit

Corporate, HR

Corporate, Finance

Corporate , Legal

Corporate, IT

Public Engagement, Business Unit A

Public Engagement, Business Unit B

Public Engagement, Business Unit C

Public Engagement, Business Unit D

Directorate, Business Unit E

Directorate, Business Unit F

Directorate, Business Unit G

Directorate, Business Unit H

 

Now my question is how do I get the Business Unit field to filter the values from the Business Unit List, then insert the selected value into the original Business Unit field?

 

Any assistance is always greatly appreciated,

Geoff.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

@Iamglt ,

That is probably because you forgot to rename the field (from my assumed name) in the second drop-down

Sort(
   Filter(
      'Business Units',
      'Division (Title)'=DataCardValue137.Selected.Result
   ).'Business Unit',
   'Business Unit'
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

PS You might recall that ddDivision might be a better name for DataCardValue137 (and easier to refer to) - it is also very good practice not to put spaces and special characters in file names like 'Division (Title)'  

View solution in original post

@Iamglt <

First question - up to you, but be consistent. I use the control type (dd for dropdown, cb for Combo Box, tx for Text Box, dp for DatePicker, lb for Label etc ), then  the field name and then if necessary for multiple controls in the app on the same field, an abbreviation of the screen name. Yo do not have to rename everything, but the controls containing data that you are going to refer to are the most important.

For the second, it will of course retain the current value (that is what it is designed to do), but you could Reset the second dropdown on the OnChange of the first. This is a bit tricky if you want to show the value in other circumstances, so you would have to decide what you wanted to show - (Blank() as an example. You would first set a Variable at Screen OnVisible

UpdateContext({varResetcb:false})

then on the OnChange of the first Combo Box

UpdateContext({varResetcb:true})

then the DefaultSelectedItems would be something like

If(
   varResetcb,
   {'Business Unit':Blank()},
   {'Business Unit':ThisItem.'Business Unit'}
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

40 REPLIES 40
WarrenBelz
Super User III
Super User III

Hi @Iamglt ,

Firstly your two drop downs Items for First

With(
   {wList:YourSPListName},
   Sort(
      Distinct(
         wList,
         Division
      ),
      Result
   )
)

then the Items for the second

Sort(
   Filter(
      YourSPListName,
      Division=YourFirstDropdownName.Selected.Result
   ).'Business Unit',
   'Business Unit'
)

 I do not understand your last question fully - what are you trying to insert and where?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi Warren,

 

Thanks for you reply - in your code above, am I meant to use for YourSPListName

the original SP list (New Employee Workflow), or the new list called Business Units?

 

For the second part of my question I just wanted to ensure that if the drop down boxes get their values from a separate list (i.e. Business Units), then they write those values back to their original fields in the 'New Employee Workflow' list.  

Hi @Iamglt ,

Sorry for being confused here, but what is the difference between the lists other than you presenting the information differently in your post?

 My code was designed for the first one, but would also work on the second (I assumed the commas were just showing the contents of the two fields).

What data are you trying to write back on the selection and to what field in what list?

Ok the second list (Called Business Units) was to define what was related to each of the divisions, so you could sort corporate and get HR,  Finance, Legal etc. 

 

Division, Business Unit

Corporate, HR

Corporate, Finance

Corporate , Legal

Corporate, IT

Public Engagement, Business Unit A

Public Engagement, Business Unit B

Public Engagement, Business Unit C

Public Engagement, Business Unit D

Directorate, Business Unit E

Directorate, Business Unit F

Directorate, Business Unit G

Directorate, Business Unit H

 

The original list (New Employee Workflow) is where I want the values selected written back to that list.  In other words, the Data Cards get their information from the Second List (Business Units), but when the selection is made, it writes it into the Fields Division and Business unit in the New Employee Workflow list.

 

Cheers,

Geoff.

Ok this code works for the Division drop down box:

 


@Iamglt wrote:

Ok the second list (Called Business Units) was to define what was related to each of the divisions, so you could sort corporate and get HR,  Finance, Legal etc. 

 

Division, Business Unit

Corporate, HR

Corporate, Finance

Corporate , Legal

Corporate, IT

Public Engagement, Business Unit A

Public Engagement, Business Unit B

Public Engagement, Business Unit C

Public Engagement, Business Unit D

Directorate, Business Unit E

Directorate, Business Unit F

Directorate, Business Unit G

Directorate, Business Unit H

 

The original list (New Employee Workflow) is where I want the values selected written back to that list.  In other words, the Data Cards get their information from the Second List (Business Units), but when the selection is made, it writes it into the Fields Division and Business unit in the New Employee Workflow list.

 

Cheers,

Geoff.


Iamglt_0-1609667710841.png

But with the Business Units data card, I cant get it working: 

Iamglt_1-1609667768576.png

How do I refer to the value in the Division drop down box, By DataCardValue137?

 

Cheers,

Geoff.

 


 

@Iamglt ,

That is probably because you forgot to rename the field (from my assumed name) in the second drop-down

Sort(
   Filter(
      'Business Units',
      'Division (Title)'=DataCardValue137.Selected.Result
   ).'Business Unit',
   'Business Unit'
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

PS You might recall that ddDivision might be a better name for DataCardValue137 (and easier to refer to) - it is also very good practice not to put spaces and special characters in file names like 'Division (Title)'  

View solution in original post

Yea I still have to go through and rename everything - is there some standard we use for data cards?  eg:

 

Iamglt_0-1609669243836.png

 

Also, your code works perfectly, but when I make a change in the Division field, the Business Unit field has the updated values, but still remembers the old value selected:

 

Iamglt_1-1609669399869.png

 

Finance should not be in that list.  Is there any way to set the Business Unit field to Null when the Division field changes?

 

Cheers,

Geoff.

 

 

@Iamglt <

First question - up to you, but be consistent. I use the control type (dd for dropdown, cb for Combo Box, tx for Text Box, dp for DatePicker, lb for Label etc ), then  the field name and then if necessary for multiple controls in the app on the same field, an abbreviation of the screen name. Yo do not have to rename everything, but the controls containing data that you are going to refer to are the most important.

For the second, it will of course retain the current value (that is what it is designed to do), but you could Reset the second dropdown on the OnChange of the first. This is a bit tricky if you want to show the value in other circumstances, so you would have to decide what you wanted to show - (Blank() as an example. You would first set a Variable at Screen OnVisible

UpdateContext({varResetcb:false})

then on the OnChange of the first Combo Box

UpdateContext({varResetcb:true})

then the DefaultSelectedItems would be something like

If(
   varResetcb,
   {'Business Unit':Blank()},
   {'Business Unit':ThisItem.'Business Unit'}
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Hi Warren,

Just realized that after I got the populating fields (Division + Business Unit) populated, they do not save back to the 'New Employee Workflow', even after I patch all fields for the form when I click the save button...

 

Iamglt_0-1609954814226.png

Is there any particular thing I need to do to force the selected items (from the Business Units list) in the drop down boxes to update into the same fields in the New Employee Workflow list?  I have restored some older versions of my Powerapp and it looks like its never updated, even thought the functionality with the drop down boxes works.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,072)