How to set up a cascading dropdown from a separate lists in a form?

[Garethterb]

I hope that somebody can help as I am stuck and what I have found does not seem to work.

Here is the scenario:

I have a list with staff details and another list with Company and branch details.

I need to be able to edit a form , based on staff details with info from the Company and Branch comes from the the other list and Branch is a cascade from Company.

Here is my company card which seems to work perfectly:

Company

DataCard1  - Default = ThisItem.CompanyID, 

                  Update = Dropdown1.Selected.Result (what I have seen is that normally I should use .Value but this is not an option                                         and gives an error)

DropDown1 - Items = Sort(Distinct(CompanyBranchDetails,CompanyName),Result)

                     Default = Parent.Default

Branch

DataCard2 - Default = ThisItem.BranchID  (showing as an error under BranchID)

                     Update = Dropdown2.Selected.Result (value is not an option and Result is shown as an error)

DropDown2 - Items = Filter(CompanyBranchDetails,CompanyName = Dropdown1.Selected.Result).BusUnit

                       Default = ThisItem.BranchID (Parent.Default gives an error)

I have tried lots of different things and this is as close as I can get to it working correctly (with respect to the dropdown values being correct and defaulting correctly) but the problem is that changing the value in the Branch Dropdown does not register a change to the form and therefore does not update the record when updated.

[v-qiaqi-msft]

Hi@Garethterb,

Could you please tell me the column type of the Company and Branch, Text or Choice?

Do you have a custom form that you inset the controls one by one?

Actually, all the data cards in a form should share the same data source, you could not submit the form that has two different lists.

Based on your description, I think Company and Branch are Text type.

Please do answer the above question and share more about your form.

Regards,

Qi

[Garethterb]

Hi all the columns are type single line text yes. I have created the form from the list that that contains CompanyID and BranchID, then deleted the default dropdown from each of the cards and replaced it with a new dropdown. What is strange is that the CompanyID works perfectly just not the cascaded branch dropdown.

[v-qiaqi-msft]

Hi@Garethterb,

CompanyName and CompanyID, are they the same column?

---

@Garethterb wrote:

Hi all the columns are type single line text yes. I have created the form from the list that that contains CompanyID and BranchID, then deleted the default dropdown from each of the cards and replaced it with a new dropdown. 

---

If the CompanyID and BranchID have default dropdown in the form, then these two columns in your SP list should be Choice type rather than the Text type. 

So I am so confused about the CompanyName and BusUnit, are they corresponding to CompanyID and BranchID in fact?

Any screenshot would be great.

Regards,

Qi

[Garethterb]

Hi CompanyID and BranchID are the colums in list one which is the list that I am updating. CompanyName and BusUnit are from list two, this is the master data list for our companies and branches. So when editing a record I need to return the current values from List one but also be able to change them in the case that somebody move between companies or branches. As mentioned all works perfectly in Datacard1 and DropDown1.

[Garethterb · Accepted solution]

After a lot of research and still no solution I decided to include a new column for list one Called BranchID2 and start from scratch, the only thing that I have changed from the original is that I replaced  Filter(CompanyBranchDetails,CompanyName = Dropdown1.Selected.Result).BusUnit with Distinct(Filter(CompanyBranchDetails,CompanyName = Dropdown1.Selected.Result),BusUnit) something that I had already tried before so am sure that this was not the main issue. If I use the old statement in the new card it gives an error on the update only. So long story short it looks like something got corrupted on the list column or the connection between the app and the column.