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 ,

What is the Patch code you are using?

[Iamglt]

Hi Warren,

This is what I had on my Save button:

If(
    varMode = "Edit",
    Patch(
        'New Employee Workflow',
        galDashboard.Selected,
        frmEmpDetails.Updates
    ),
Patch(
        'New Employee Workflow',
        Defaults('New Employee Workflow'),
        frmEmpDetails.Updates
    );
Set(
    varMode,
    "Edit"
)

And this is what I have tried changing it to (just to save the division field for this attempt):

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

The division field is a choice field - should I delete and re-create as a text field?  This is the error I am getting on the Save button with the code above:

The Data Card Value label is DataCard137:

However, when I click on the field its called 'Result':

The Items (for the Division Drop Down Box) has the following code:

With(
   {wList: 'Business Units'},
   Sort(
      Distinct(
         wList,
         'Division (Title)'
      ),
      Result
   )
)

Maybe I should change Result to Division?  Or would updating the Patching on the OnSelect/OnChange for each of the fields be a better option to write back to these fields?

Cheers,

Geoff.

[WarrenBelz]

Hi @Iamglt ,

Try this

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

but I am wondering why you are bothering with the Division as if it is changed in frmEmpDetails, it will be included in the Updates and if not changed will simply retain its value. 

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 thought it would be included with the frmEmpDetails as well, but both Division and Business Units has never saved - its been driving me nuts for 2 days.  I tried your code above, and I'm getting the following error:

Ive been doing some searching on updating Choice fields, and I tried this as a test, but the record never updates:

If(DataCardValue137.Selected.Result = "Corporate Operations and Services",
Patch('New Employee Workflow',
   Defaults('New Employee Workflow'),
{Division:
 {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
   Id:0,
  Value:"Corporate Operations and Services"
 }
}))

Cheers,
Geoff.

[WarrenBelz]

Hi @Iamglt ,

I hope Division is a Choice field (not a Lookup) in your data source - if so

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

Also you do not need

'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"

any more - Microsoft quietly dropped the requirement a little while ago.

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]

This is from the SP List:

That code worked perfectly.  I have just trying to get the BusinessUnit (SP Text Feild to Update):

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

So can I do multiple field updates as above?  I get red lines everywhere when I add the Business Unit line...

[Iamglt]

Ok, I have got it to this with no errors, but the Field BusinessUnit is still not saving:

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

[WarrenBelz]

@Iamglt ,

Your curly brackets were somewhat out of place - if you parse it like this, you will see the structure required (I assume they are both Choice fields in the data source).

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

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 tried it that way before, and this is what comes up:

I have to go to a meeting - will check it out more later...

[WarrenBelz]

@Iamglt ,

What is the error saying?

Have you pulled it apart and tested both sides of the If statement?
Then take out Division, then BusinessUnit and see which one might be an error.
Are both BusinessUnit and Division Choice fields in your list?