Answered

OnSuccess not writing masterID column to SPO list

(0) Share

Report

Posted on by 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?

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
sb92 40 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
sb92 40 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
sb92 40 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
Verified answer

ChadVKealey 1,393 on at

Like
a
(1)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
sb92 40 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
sb92 40 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Chad. I removed the patch as you recommended, and added in MasterID to the form, as an editable number. And set its update property to MasterGallery.Selected.ID
And it worked!!!!!!!! Hours tearing my hair out about this and now finally solved. Thankyou so much.
A couple of other columns don't carry across either: RiskLevel and AdequatelyControlled.
They do not rely on the other list at all. They are both text columns on the HazardDetails sharepoint list. On PowerApps, RiskLevel's text property is a nested IF statement and Adequately Controlled is also an IF statement. Any thoughts on why this might be?
The IF statement for RiskLevel is as follows:

If(Dropdown2_1.Selected.Value="Minor",If(ProbabilityEditorDropdown_1.Selected.Value="Frequent","Medium","Low"),If(ProbabilityEditorDropdown_1.Selected.Value="Frequent","High",If(ProbabilityEditorDropdown_1.Selected.Value="Likely",If(Dropdown2_1.Selected.Value="Moderate","Medium","High"),If(Dropdown2_1.Selected.Value="Moderate","Low","Medium"))))

Is it because the IF statement has been put in the DEFAULT property of the text input, when it should be in the UPDATE property of its parent datacard???
Thinking I need to go back and relearn about forms and what their properties mean lol

Was this reply helpful? Yes No