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

[Iamglt]

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.

[WarrenBelz]

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.

[Iamglt]

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.  

[WarrenBelz]

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?

[Iamglt]

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]

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.

---

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

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

Cheers,

Geoff.

[WarrenBelz · Accepted solution]

@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)'  

[Iamglt]

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

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:

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.

[WarrenBelz · Accepted solution]

@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.

[Iamglt]

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...

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.