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]

@Iamglt ,

Not the Default - the DefaultSelectedItems.

Business Unit Combo should be 

{Result:ThisItem.BusinessUnit}

and Division Combo should be

{Value:ThisItem.Division}

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]

Its not a Combo box, they are both Drop Down boxes - so there is only a default option:

[Iamglt]

Fixed IT!!! Horay.

I changed the DataCard DataField to blank:

[Iamglt]

Sorry please ignore my previous post - its still happening....

[Iamglt]

Hi Warren,

I have reverted back to the Combo boxes, and the business units combo box works fine, with the following code:

With(
   {wList: NewEmployeeDivisionBusUnitList},
   Sort(
      Distinct(
         wList,
         DropDownBusinessUnitList
      ),
      Result
   )
)

However when i put my code in for the Division:

Sort(
    Filter(
        NewEmployeeDivisionBusUnitList,
        DropDownBusinessUnitList = ComboBox2.Selected.Result
        ),
        ThisItem.Division, ThisItem.Division
)

I Can see the number of records, but they are appearing as blank lines:

I changed the 'Business Unit' List to NewEmployeeDivisionBusUnitList: (Two fields: DropDownBusinessUnitList, DropDownDivisionList) That contains the Business Unit and Division values.

The Master SP List I changed to NewEmployeeOnboarding (when I recreated all of the fields), Business Unit field is now called BusinessUnit and Division is stall the same (i.e. I removed the spaces from all field names).  Do you know what I'm doing wrong thats returning blank lines?

Cheers,

Geoff. 

[WarrenBelz]

Hi @Iamglt ,

Where did you get this code?

Sort(
    Filter(
        NewEmployeeDivisionBusUnitList,
        DropDownBusinessUnitList = ComboBox2.Selected.Result
        ),
        ThisItem.Division, ThisItem.Division
)

it needs to be

Sort(
   Filter(
      NewEmployeeDivisionBusUnitList,
      DropDownBusinessUnitList = ComboBox2.Selected.Result
   ), 
   Division
).Division

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]

I had to change Business Units to Division and vice versa (I had the wrong data in the wrong fields when I first set this up).  So I have changed it, but when I try to enter 'Divison' as in your code, the only options that it will accept is 'ThisItem.Divison' or 'DropDownDivionList' - see screenshot:

 But the Datacard is still pointing to Division:

Cheers,

Geoff.

[Iamglt]

Interesting, when I checked the Combox3 data card, it had the following values in the Display Fields and Search Fields:

[WarrenBelz]

@Iamglt ,

Now I see you are putting that in the DataField of the Card - my code was for the Items of the Combo Box. The DataField of the card is simply "Division" (in the quotes as shown)

[Iamglt]

No I had your code in the Items - sections (I should have taken a larger screen shot):

And the other shots I was just showing you (in case they were causing the issues).  For some reason it doesn't like the Division field unless its ThisItem.Division

Cheers,

Geoff.