Submit cascading multi-select SharePoint LookUp columns using an Edit Form

[Amik]

I recently came across the following scenario posted on the forums and I moved my response to this blog.

Scenario:

We have a SharePoint List which has three SharePoint LookUp Columns:

1. Country: configured in SharePoint as a Single-Select SharePoint LookUp column.
2. City: configured in SharePoint as a Multi-Select SharePoint LookUp column.
3. Town: configured in SharePoint as a Multi-Select SharePoint LookUp column.

We want to add these fields into an EditForm control, and cascade each Combobox selection according to the levels ordered above.

For this scenario, my preference is not to use the Patch function, but instead modify a few of the properties of our ComboBox controls, then leverage the SubmitForm function to update the output of those controls.

Please feel free to raise any comments/improvements (other than avoiding complex columns like SharePoint LookUps!).

Set up

1. Create a New Screen into your App.

2. Insert a Gallery control into your App and associate the Items property to your SharePoint List.

3. Insert an EditForm control into your App and associate the DataSource property to your SharePoint List. Note for this example, the name of our EditForm is Form1.

4. Add the relevant DataCards for each LookUp field into the EditForm. For the purpose of this scenario, the embedded ComboBox control names associated to each DataCard are:

1. Country: ComboBox1
2. City: ComboBox2
3. Town: ComboBox3

Steps

1. For ComboBox1 (Country), ensure the Items property is: 

Choices([@'Our SharePoint List'].Country)

2. For ComboBox2 (City), enter the below into the Items property:

DropColumns(
    AddColumns(
        GroupBy(
            Ungroup(
                ShowColumns(
                    Filter(
                        'Our SharePoint List',
                        Country.Value = ComboBox1.Selected.Value
                    ),
                    "City"
                ),
                "City"
            ),
            "Value",
            "GroupedItems"
        ),
        "Id",
        First(GroupedItems).Id
    ),
    "GroupedItems"
)

3. For ComboBox3 (Town), enter the below into the Items property:

If(
    !IsBlank(ComboBox2.SelectedItems),
    DropColumns(
        AddColumns(
            GroupBy(
                Ungroup(
                    ShowColumns(
                        Ungroup(
                            ForAll(
                                ComboBox2.SelectedItems As _selected_items,
                                Filter(
                                    'Our SharePoint List',
                                    _selected_items.Value in City.Value
                                )
                            ),
                            "Value"
                        ),
                        "Town"
                    ),
                    "Town"
                ),
                "Value",
                "GroupedItems"
            ),
            "Id",
            First(GroupedItems).Id
        ),
        "GroupedItems"
    )
)

4. Insert a Button control (we will call it “New Form”) and on the OnSelect property, enter:

NewForm(Form1);
UpdateContext({ctx_reset_combobox1: false});
UpdateContext({ctx_reset_combobox1: true});
UpdateContext({ctx_reset_combobox2: false});
UpdateContext({ctx_reset_combobox2: true});
UpdateContext({ctx_reset_combobox3: false});
UpdateContext({ctx_reset_combobox3: true})

5. On the OnSelect property of the Gallery control, enter:

UpdateContext({ctx_record: ThisItem}); //if you want to use a Global Variable, use Set(gbl_record, ThisItem);
UpdateContext({ctx_reset_combobox2: false});
UpdateContext({ctx_reset_combobox3: false});
EditForm(Form1);

6. On the Item property of the EditForm control use:

ctx_record

7. On the OnSuccess property of the EditForm control, use:

UpdateContext({ctx_record: Self.LastSubmit}); //if using a Global Variable, use: Set(gbl_record, Self.LastSubmit)
UpdateContext({ctx_reset_combobox1: false});
UpdateContext({ctx_reset_combobox2: false});
UpdateContext({ctx_reset_combobox3: false})

8. On the OnSelect property of ComBobox1, enter:

UpdateContext({ctx_reset_combobox2: false});
UpdateContext({ctx_reset_combobox2: true});
UpdateContext({ctx_reset_combobox3: false});
UpdateContext({ctx_reset_combobox3: true})

9. On the OnSelect property of ComBobox2, enter:

UpdateContext({ctx_reset_combobox3: false});
UpdateContext({ctx_reset_combobox3: true})

10. No need to add anything onto the OnSelect property of ComBobox3.

11. On the Reset property of ComBobox1, enter:

ctx_reset_combobox1

12. On the Reset property of ComBobox2, enter:

ctx_reset_combobox2

13. On the Reset property of Combobox3, enter:

ctx_reset_combobox3

14. On the DefaultSelectedItems property of Combobox2, enter:

If(
    ctx_reset_combobox2,
    Blank(),
    Parent.Default
)

15. On the DefaultSelectedItems property of ComBobox3, enter:

If(
    ctx_reset_combobox3,
    Blank(),
    Parent.Default
)

16. No need to modify anything in the DefaultSelectedItems property of ComboBox1.

17. Add another Button control into your screen (we call it "Submit Form"). On the OnSelect property, enter:

SubmitForm(Form1)