Submit Form and Create Record in Second Sharepoint List Based Upon Lookup Field

[Anonymous]

Thanks in advance!

I have a main Sharepoint List called Projects. This List has several Lists related to it via a field called ProjectID. For now we'll boil those other lists down to one called HDA. HDA has a Lookup field called ProjectID that connects to the ProjectID field in the Projects List. In my PowerApps I have a form where the user can create a new record in Projects. I have a button to SubmitForm the form. I would like that button to also create a new record in the HDA List based upon the ProjectID field. The twist is that the ProjectID field in the Projects List is auto-generated/incremented using PowerAutomate, so that whenever a new record is created in that Sharepoint List the ProjectID field is automatically filled with the next number in the sequence. Because of this, I do not have a DataCard in the Form where the user inputs the ProjectID for the Projects List. I know I will be using the Patch function to accomplish this, but I'm not sure what syntax to use to make the HDA List ProjectID (which is a Lookup column) equal to the newly created ProjectID field in the Projects List. One thought I had was to add to the ProjectID field back in as DataCard but make it invisible, however, I'm still not sure of the syntax to use in Patch in order to use it or whether it would even work. 

Any help would be much appreciated.

[Anonymous]

I was only using the Flow to auto-increment the ProjectID field in the AHI Projects list. But I will move the auto-increment to PowerApps.

Yeah, Lookup column complicates the matter

[Anonymous]

Ther other lists (such as AHI HDA) use a Lookup Column (ProjectID) to the main list (AHI Project)

[cwebb365 · Accepted solution]

Ok, so do this. on the main Project Form, don't have the ProjectID in it. For the "OnSuccess" of the form use the following: 

Patch('AHI Projects', Lookup('AHI Projects',id=Self.LastSubmit.id), {ProjectID: Self.LastSubmit.id}); Patch('AHI ADA', Defaults('AHI ADA'),{ ProjectID: {Id: Self.LastSubmit.id,Value: Self.LastSubmit.ProjectID,'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}, Column1: "data", Column2: "data"})

I think this should work. 

Column1 and 2 etc. are just there to show that you can set other values in that AHI HDA list there with that patch other than ProjectID. 

Column1 and 2 are the other columns data whatever you wanted to add to that 2nd list. 

Also not sure if you have data in your Project list yet, but you can add whatever the highest current id is to the first patch above after the Self.LastSubmit.id to get it to catch up with current generated number.