OnSuccess not writing masterID column to SPO list

[sb92]

I have an app that reads and writes to 2 lists - one is called hazardmaster and the other hazarddetails.

I can get the app to simultaneously create a new list item to hazardmaster and submit a collection of items for hazarddetails that are given the masterid of the last submitted item id of hazardmaster. So that works fine, but:

I also tried to create a button and form that allows the user to create a new item in hazarddetails with the masterid of an already existing item in hazardmaster.

MasterGallery.Selected.ID=number

masterID column in sharepoint = number

The form will create a new record but it will not write to all columns. MasterID and another column with type Text will not write.

I've tried everything i can think of, have spent hours upon hours trying to solve this problem 😞

the masterId column is called masterId, not title (i checked)

the column format and the form input control are the same matching types of data (i checked)

can anybody help?

[RandyHayes]

@sb92 

Can you provide some of the formula that you are having problems with?

In general, you will reference the yourForm.LastSubmitted.ID to get the ID you are looking for in your formula.

i.e. (guessing here because not sure what you're actually doing in your formula)

    Patch(yourOtherList, Defaults(yourOtherList), {masterID: yourForm.LastSubmitted.ID})

I hope this is helpful for you and gives you a clue - if not, please clarify some of your formula used.

[sb92]

Hi Randy

In my app I only refer to lastsubmittedid when I'm creating two items to two different lists at once on the same screen (a form and a gallery/collection submitted together). This already works fine..

But!

When I already have an existing item in list 1 that already has an ID, and I want to create a related item in list 2 (with a column called masterID which is the same number as ID in list 1) then there is no last submitted form to refer to, as the original item could have been made weeks ago. So what I've done in this instance is set masterid to equals mastergallery.selected.id so that an item created for list 2 can have the same ID as an item in list 1.

Here's the onsuccess formula for the form that creates a new item in list 2 after already selecting an item in list 1 from a gallery on the previous screen:

---

Patch(HazardDetails,Defaults(HazardDetails), {Title:DataCardValue1_1.Text, MasterID:MasterGallery.Selected.ID, How:DataCardValue2_1.Text, Where:DataCardValue3_1.Text, WhoIsAtRisk:DataCardValue5_1.Text, 'Current Safety Measures':DataCardValue6_1.Text, Probability:ProbabilityEditorDropdown_1.Selected.Value, Severity:Dropdown2_1.Selected.Value, RiskLevel:TextInput1_1.Text, 'Additional Safety Measures':DataCardValue10_1.Text, NewRiskLevel:DataCardValue11_1.Selected, AdequatelyControlled:DataCardValue12_1.Text, 'Senior or Director Sign Off':DataCardValue13_1.Text} ); Navigate(HomeScreen);

[ChadVKealey]

Try wrapping the ID from the MasterGallery in "Value", like this:

MasterID: Value(MasterGallery.Selected.ID)

You wouldn't think that would be necessary, but I've seen a LOT of cases where it was.

[RandyHayes]

@sb92 

I believe I am putting the pieces together.

But I have some confusion that I need some clarity for - your EditForm is for HazardMaster or HazardDetails?

I'm going to assume for this moment that the EditForm is for HazardMaster and that your OnSuccess formula is to create a new record in the HazardDetails with the values from the EditForm (and other places) to populate the columns.

If this is all good...read on...if not, stop and clarify.

So, the fact that the master record was created prior does not impact LastSubmit.  It will still have all of the values of the record.

So, you should change your OnSuccess action formula to the following:

Patch(
    HazardDetails,
    Defaults(HazardDetails), 
    {Title: DataCardValue1_1.Text,                //change this to the column
     MasterID: yourEditForm.LastSubmit.ID, 
     How: DataCardValue2_1.Text,                  //change this to the column
     Where: DataCardValue3_1.Text,                //change this to the column  
     WhoIsAtRisk: DataCardValue5_1.Text,          //change this to the column 
     'Current Safety Measures': DataCardValue6_1.Text, //change this to the column
     Probability: ProbabilityEditorDropdown_1.Selected.Value,
     Severity: Dropdown2_1.Selected.Value,       
     RiskLevel: TextInput1_1.Text, 
     'Additional Safety Measures': DataCardValue10_1.Text, //change this to the column
     NewRiskLevel: DataCardValue11_1.Selected,    //change this to the column
     AdequatelyControlled: DataCardValue12_1.Text, //change this to the column 
     'Senior or Director Sign Off': DataCardValue13_1.Text  //change this to the column
    } 
); 

Navigate(HomeScreen);

NOTE: I have indicated above the "//change this to the column" remarks.  DO not rely on the values of controls in the EditForm during your OnSucess.  They will have changed in many cases (like your form is in New mode, and others).  Instead you should reference the value from the record in the LastSubmit.

So, for example, let's say Title...you should replace DataCardValue1_1.Text with yourEditForm.LastSubmit.Title

Do this same thing with ALL of the columns in your Patch that reference a control in your form and replace with the LastSubmit.columnName

[sb92]

Hi Chad

I tried that originally and that didn't work either! 

MasterGallery.Selected.ID is evaluating as a number, and it is 29 or 31 depending on which list1 item i have picked so it is defo working in that sense! 

[sb92]

Hi Randy

Sorry no the editform is for HazardDetails. The form is to create a new record in HazardDetails that will be able to be associated to an item that already exists in HazardMaster because the ID of the hazard master is to match the masterID of HazardDetails and vice versa. A HazardDetail item might be created days or weeks after a HazardMaster item has been created.

[RandyHayes]

@sb92 

Okay.  So the point about the LastSubmit column values is highly important to do.  In general the controls in a form should only be referenced in certain situations.  An EditForm has two record properties that supply anything you need from the form - Updates and LastSubmit.  Updates will be all the update properties of the form combined into a record and LastSubmit will be all of the column values that were last submitted (as of the SubmitForm action).

You should not get to rely on the DataCardValuex controls as they can change and give unexpected results depending on your form.

Now...going back to the MasterID.  I have a suspicion that your Gallery is getting changed (the selected row) when you are submitting the Form.  This can be because your datasource is being updated (which it is) and your gallery is based on that and it need to re-calculate its rows (very common).

So, I would suggest "grabbing" the MasterID in a snapshot variable.  In the action where you do your SubmitForm, add the following BEFORE your SubmitForm - UpdateContext({lclMasterID: MasterGallery.Selected.ID})

Then change your formula to the following:

Patch(
    HazardDetails,
    Defaults(HazardDetails), 
    {Title: DataCardValue1_1.Text,                //change this to the column
     MasterID: lclMasterID, 
     How: DataCardValue2_1.Text,                  //change this to the column
     Where: DataCardValue3_1.Text,                //change this to the column  
     WhoIsAtRisk: DataCardValue5_1.Text,          //change this to the column 
     'Current Safety Measures': DataCardValue6_1.Text, //change this to the column
     Probability: ProbabilityEditorDropdown_1.Selected.Value,
     Severity: Dropdown2_1.Selected.Value,       
     RiskLevel: TextInput1_1.Text, 
     'Additional Safety Measures': DataCardValue10_1.Text, //change this to the column
     NewRiskLevel: DataCardValue11_1.Selected,    //change this to the column
     AdequatelyControlled: DataCardValue12_1.Text, //change this to the column 
     'Senior or Director Sign Off': DataCardValue13_1.Text  //change this to the column
    } 
); 

Navigate(HomeScreen);

Again...changing those other references to DataCardValues to be the LastSubmit.column

[ChadVKealey · Accepted solution]

@sb92 , to clarify the sequence, you have a gallery showing the "HazardMaster" list. You click something to open a form allowing users to create a new "HazardDetail" that will be linked to that Master item. You say you have that Patch statement in the "OnSuccess" for that form, but that is what happens when the form is submitted. So, the form submit is creating an item, then the Patch statement would create another item (since you're using "Defaults(datasource)") in the Details list. Is it possible that this is what's happening (two Detail items are being created) and you're looking at the one generated by the SubmitForm action, not the Patch? 

What you might want to do, rather than using the Patch, is adding the "MasterID" field to the form and set it's Update property to "MasterGallery.Selected.ID". You can then hide that field on the form (NOTE: set visible to 'false' - do NOT delete/remove the field!). I would actually leave it visible for testing; once you're sure it's entering the value correctly, hide it.

[sb92]

Hi Chad, what would I write in place of the Patch in the OnSuccess property of the form?