Submit New form to one SP list then Patch to Lookup column in other SP list

(0) Share

Report

Posted on by R Bakker

725

Hi Everyone

This one has been bugging me for a while. On a slight variation on the below I had it writing to two sharepoint lists every time the FormIncident was submitted, but I only want it to write to both when it's a Newform. FormIncident is saving to SP list Incident, then I want it to Patch IncidentLog when FormIncident is new. So ReportID is a text column in the first list and ReportIdNumber is a Lookup in the second list

SubmitForm(FormIncident);Set(NewReport,FormIncident.LastSubmit);If(FormIncident.Mode=1,Patch(IncidentLog,Defaults(IncidentLog),{ReportIdNumber:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:NewReport.ID,Value:NewReport.ReportID}}))

So to recap my intent is, when editing a exiting record, it only saves to the first database when submitted. But when creating a new record it saves the record to the first database and also saves to a single lookup column in a second database when submitted.

It is obvious what I want in my head, but I realise how it comes out in writing might be as clear as custard. any help would be much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report
@Corissandageri

First, your formula should ONLY be SubmitForm(FormIncident).

Putting additional functions after the submit will not be guaranteed to be accurate as the Form submit may not (and most likely will not) have completed by the time that PowerApps evaluates the next function (your Set in this case.)

PowerApps does NOT wait for SubmitForm to complete before moving forward in the formula evaluation.

How, in your formula, do you know that the form submitted properly?? You don't. It might have failed. Your OnSuccess action (and OnFailure) on the Form are designed for this purpose.

Your OnSuccess action formula should be:

Set(NewReport, Self.LastSubmit); If(Self.Mode=FormMode.New, Patch(IncidentLog, Defaults(IncidentLog), {ReportIdNumber:{Id:NewReport.ID, Value:NewReport.ReportID}} ) )

However, I would also not rely on the Mode property at this point in the process. You might do well to establish a variable to specify this ahead of time.

Also, the odata.type column you were specifying is no longer needed and can be removed.

You should be specifying the correct value for your Value on the lookup. If your list is set to have the connected field be ID, then it is fine, but if it is defined as something other like "Title", then that should be specified instead.

I hope this is helpful for you.

Was this reply helpful? Yes No
R Bakker 725 on at

Like (1)

Report

Hi Randy, Thank you for your reply, I came for one solution and you have taught me a bunch in one post, I will try this soon, thanks again.

Was this reply helpful? Yes No
R Bakker 725 on at

Like (0)

Report

Hi again
It's writing to to first list but not to the second, I wonder if that's because at the moment of patching to ReportIdNumber: the form is no longer in New form mode?

Was this reply helpful? Yes No
R Bakker 725 on at

Like (0)

Report

I think I come up with a solution, will post it tomorrow when my brain is working again.

Was this reply helpful? Yes No
R Bakker 725 on at

Like (0)

Report
So building on Randy's solution, on my save button just before my Submit form I have the below:

;Set(varNewItem,LookUp(ListA,ReportID = DataCardValue4.Text))

And because if it is a new record, varNewItem returns Blank it will then Patch the lookup column.

Set(NewReport, Self.LastSubmit);If(IsBlank(varNewItem), Patch(IncidentLog, Defaults(IncidentLog), {ReportIdNumber:{Id:NewReport.ID, Value:NewReport.ReportID}} ) )

Then finally also on Success I Set(varNewItem,Blank())
I don't know if this is the write way, but it seems to work.

Was this reply helpful? Yes No
TomCollins 75 on at

Like (0)

Report

Hi Randy
Is there also a way to patch an existing records instead of creating a new entry.

I have a text column "ParentTeam" and a combox named "Team" on the same list (myList).
The item property in the column "Team" is set to: Filter(myList;Not(IsBlank(ParentTeam)))

If I submit the form with a choosen entry from ParentTeam the sharepoint column "Team" stay empty. So I try to patch this field after submit the form.

OnSuccess I added this formula:

Set(NewTeam; Self.LastSubmit);
Patch(myList,
{Team:{Id:NewTeam.ID; Value:NewTeam.Team.Value}}
)
Instead of patching my existing field a new empty entry is created. When I check the variables of the formulara everything is correct (I can resolve the LastSubmited variable and also the NewTeam.ID) but NewTeam.TeamValue is empty. I guess this is because the selected item is in text format.

Best regards

Was this reply helpful? Yes No