Combox Default Select Items not writing to SharePoint on form submission

[dw909]

I'm writing a canvas app with a multi-tabbed Wizard to guide users on the information I want to extract from them, in a user-friendly way.

The last tab of the wizard is a Summary page, which I've setup as a New Form which collects the data from the various tabs of the Wizard using DefaultSelectedItems property to grab the different values. I then simply submit this New Form to  write to my SharePoint List backend. Everything is writing to SharePoint successfully except for one column which is for selecting one or more reasons for something from a list.  In my SharePoint List, the problematic column is a multiselect from a Lookup of another SharePoint list, where we pull through the Title property.

In my Wizard to capture the information, I have a small gallery with check boxes where users can simply check the one or more boxes that apply.

The OnCheck property is using this syntax to write the values to a Collection: 

Collect(colReason,{Value:ThisItem.Title})

Likewise the OnUncheck property is using similar syntax to remove the values from the Collection:

Remove(colReason,{Value:ThisItem.Title})

 All good and the collection colReason creates a table with the Value containing the text selected by the user.

On my Summary page's New Form, I've imported the Reason field for the SharePoint list I'm writing to.

The ComboBox has the imported value for Items property like:

Choices([@'My SharePoint List'].Reason)

In order to populate the items the user selected within the Wizard I'm using the DefaultSelectedItems property like this:

{Value:colReason.Value}.Value

Data type: Table is reported and the Value in the table does indeed contain the selected items on different rows.

Everything is readable in the ComboBox on the New Form, and if I have the ComboBox in DisplayMode=Edit then you can see it has selected the items.

My problem is when I then Submit the New Form, the Reason selections simply aren't being written to SharePoint column. The annoying thing is, with the same syntax in place, if I de-select the items that were default selected, and then manually re-select them, then when I Submit the New Form it will write the data! 

I'm tried various other syntax which was accepted and displayed the values in the New Form but had the same issue of not writing to SharePoint. I've tried other workarounds like a separate ComboBox in the same card to pull the values from the collection, then select the DefaultSelectedItems of the original ComboBox to pull from the new ComboBox... but still the same problem. I even tried to Patch the values separately like this, but to no avail:

Patch('My SharePoint List',LookUp('My SharePoint List', 'ID' =NewForm.LastSubmit.'ID'), {Reason: DataCardValue180.SelectedItems});

 I'd be ever so grateful if someone could please help here! Surely there must be an answer to this problem? 

 Thanks all!                        

[dw909 · Accepted solution]

After a bit more research on other forums and a bit more experimentation I've found the solution. I'm posting my solution here, in the hope it might help someone else in the future!

First thing I've changed is the Gallery with checkboxes where the users were selecting their Reasons. Instead of this Gallery's Items property set to pull the Reason (Title) value directly off the lookup SharePoint list (Reason), I'm pulling that entire SharePoint list into a  new Collection called colReasonList in my App's StartUp with not only the Reason description (Title), but also crucially with its original ID (ID). 

ClearCollect(colReasonList, ShowColumns(Reason,"ID", "Title"));

Modification of the OnCheck property for the checkbox in the gallery now is:

Collect(colReason,{Value:ThisItem.Title, Iterator:ThisItem.ID})

Modification of the OnUnCheck property for the checkbox in the gallery now is:

Remove(colReason,{Value:ThisItem.Title, Iterator:ThisItem.ID})

This takes just the required selected entries out of the colReasonList collection and puts them into a new collection called colReason - with the Value being the Reason Description (Title) and the original ID now being an Iterator field.

Meanwhile, in my Wizard's summary page, that form does have the Reason box in DisplayMode view with DefaultSelectedItems just displaying the selected reasons still using 

{Value:colReason.Value}.Value

  - although this doesn't write to SharePoint as part of the form submission it's needed just so the user can read his summary.

The writing to SharePoint I handle not through Form submission, rather through patching in a multistep approach:

Step 1 - create a new Collection called colReasonTable with special formatting for patching to SharePoint, based on values written temporarily to the Collection colReason upon selection in the Gallery:

Clear(colReasonTable);
ForAll(
    colReason,
    Collect(
        colReasonTable,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpanderReference",
            Id: Iterator,
            Value: Value
        }
    )
);

Note this takes the Iterator which was the original ID from the SharePoint lookup list via the initial Collection, and puts it as an ID value for the final collection ready for patching. If you don't have the correct ID from the original SharePoint LookUp column's SharePoint list then it will not write the correct data when you patch, rather will patch a different Title value from the SharePoint LookUp list!

Step 2 - Patch to the SharePoint List the Reason selection(s) after the main Summary form is submitted.

In the OnSuccess properties of my Summary Form I have a patch command:

Patch('My SharePoint List', LookUp('My SharePoint List','ID' = varLastID), {Reason: colReasonTable});

(Note I set a variable for the ID just written to by the Form in my SharePoint list using 

Set(varLastID, WizardNewForm.LastSubmit.ID);

but this I suppose is optional - I needed it because I was doing various other functions with the same ID and it was easier to save the value as a variable for those purposes).