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.

 

40 REPLIES 40

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

 

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

 

Iamglt_0-1610162730456.png

 

 

Fixed IT!!! Horay.

 

I changed the DataCard DataField to blank:

 

Iamglt_2-1610164325390.png

 

 

Iamglt_1-1610164296670.png

 

 

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

Hi Warren,

 

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

 

Iamglt_1-1610228106679.png

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:

Iamglt_2-1610228253945.png

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. 

 

 

 

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.

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:

 

Iamglt_0-1610230044456.png

 But the Datacard is still pointing to Division:

 

Iamglt_1-1610230110240.png

 

Cheers,

Geoff.

 

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

Iamglt_0-1610230512412.png

 

 

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

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

Iamglt_0-1610231116357.png

 

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.

 

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 (2,630)