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

Hi Warren,

Its the Comma between the Division: and BusinessUnit: Statements.  As soon as I remove the comma the red lines errors go away.  I've tried using a semi colon between statements and that does not work either.  If I remove the comma and BuisnessUnit: statement altogether then it accepts it fine.  Tried an '&&' between the statements and that did not work either.

 

I tried this on the onSelect of the drop down box:

 

If(
   varMode = "Edit",
   Patch(
      'New Employee Workflow',
      {ID:galDashboard.Selected.ID},
      {
          BusinessUnit: {Value:dcBusinessUnit.Selected.BusinessUnit}
      }
   ),
Patch(
      'New Employee Workflow',
      Defaults('New Employee Workflow'),
      frmEmpDetails.Updates,
      {
          BusinessUnit: {Value:dcBusinessUnit.Selected.BusinessUnit}
      }
   );
   Set(
      varMode,
      "Edit"
   )
)

 

And it shows the following errors:

 

Capture10.JPG

I tried deleting the BusinessUnit Field and recreating it as a text field and it still did not work.  I recreated it as a choice field, deleted the combo box and re-added it and renamed the data card to dcBusinessUnit and still the same errors above.

 

Cheers,

Geoff.

 

@Iamglt ,

What type of field is BusinessUnit (single/multichoice) in SharePoint and what are the Items of that drop-down?

 

 

 

 

Hi Warren,

 

I have tried Division by itself in the above code and it works.  I have also tried deleting The BusinessUnit field from SharePoint and then re-creating it as a choice field, then deleting and re-adding the power apps data card (done a data refresh) - So the BusinessUnit field is now directly connected to the SharePoint original field and it still wont save to its own field.  

Hi Warren,

 

I missed this message.  The SP Field is single Choice:

 

Iamglt_0-1610015537331.png

The business units i have sent you via private msg.  I think what I will do tomorrow is re-design this using text fields with drop down boxes via power app.  I tried as a test to put the following code into the onChange drop down box for division:

 

If(
   varMode = "Edit",
   Patch(
      'New Employee Workflow',
      {ID:galDashboard.Selected.ID},
      frmEmpDetails.Updates,
      {
         Division: {Value:DataCardValue137.Selected.Result}
      }
   ),
Patch(
      'New Employee Workflow',
      Defaults('New Employee Workflow'),
      frmEmpDetails.Updates,
      {
      Division: {Value:DataCardValue137.Selected.Result}
      }
   );
Set(
      varMode,
      "Edit"
   )
)

 

I ran a few tests, and sometimes it saved and sometimes it didn't.  I see what you mean about choice fields now and I think eliminating them on the SharePoint side will fix this.

 

Cheers,

Geoff.

Hi @Iamglt ,

I was fearing you were going to tell me it was a Lookup - I totally agree with your path - get rid of both the Choice and Lookup columns and make them both a Single Line of text. All will then become sooo much easier.

Ok cool I will work on that now.  So that means I will have one List that contains the list of divisions / business units that populates the drop down boxes (or should I just hard code that into collections?), One Text field for business units and one for divisions, populated by the drop down boxes.  Will this format still be the same for text fields?

 

Division: {Value:DataCardValue137.Selected.Result}

 

Cheers,

Geoff.

 

Hi @Iamglt ,

You will simply have Single Lines of Text as your data and do the Filter in the Items of the dropdown. Your Update will then simply be ComboBoxName.Selected.xxxx (Value/Result/FIeldName).

 

Iamglt
Helper V
Helper V

Hi Warren,

I clanged all the fields to text and reprogrammed the DropDown Boxes and also renamed my Lists / Fields without spaces and then have re-added everything back to the from (a lot of work - lesson learnt).

 

I got the Drop Down boxes working with the following code (I had to insert a label which was a copy of the Business Unit Drop Down box) to Debug, but here it is:

 

If(
    varMode = "Edit",
       Patch(
      NewEmployeeOnboarding,
      {ID:galDashboard.Selected.ID},
      frmEmpDetails.Updates,
      {BusinessUnit: Dropdown2.Selected.Result},
      {Division:     Dropdown1.SelectedText.Value}
   ),
    Patch(
        NewEmployeeOnboarding,
        Defaults(NewEmployeeOnboarding),
        frmEmpDetails.Updates,
        {BusinessUnit: Dropdown2.Selected.Result},
        {Division:     Dropdown1.SelectedText.Value}
    ));
Set(
    varMode,
    "Edit"
)

 

Now the issue I have is a screen refresh one.  Lets say I have selected these values and hit the save button:

 

Iamglt_0-1610157246983.png

 

You can see that the SP list has been updated (I created a view just to show these fields):

 

Iamglt_1-1610157440498.png

If I change the Edit Screen again and Save (I selected : Business Unit - Directorate, and Division: Communications, Marketing and Public Affairs) - but as soon I hit save the screen went back to this below:

 

Iamglt_2-1610157511508.png

However, when I check the SP List, the Patch / Save actually worked:

 

Iamglt_3-1610157663244.png

 

Any ideas why the values in the fields revert back to what they were after the patch / save has occurred?

 

Cheers,

Geoff.

 

 

 

@Iamglt ,

What are the DefaultSelectedItems of the Combo Boxes?

They had a default of "1".  I also commented out the Savefrm section of the code:

 

If(
    varMode = "Edit",
       Patch(
      NewEmployeeOnboarding,
      {ID:galDashboard.Selected.ID},
      {BusinessUnit: Dropdown2.Selected.Result},
      {Division:     Dropdown1.SelectedText.Value};
      //frmEmpDetails.Updates
   ),
    Patch(
        NewEmployeeOnboarding,
        Defaults(NewEmployeeOnboarding),
        {BusinessUnit: Dropdown2.Selected.Result},
        {Division:     Dropdown1.SelectedText.Value};
        //frmEmpDetails.Updates
    ));
Set(
    varMode,
    "Edit"
)

 

And this is still occurring.  It might have something to with the source data from these combo boxes comming from a separate SharePoint list....

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Users online (2,308)