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.

[cwebb365]

Using anything that generates numbers is risky cause of possible contention, but it's probably rare as I'm sure the form submissions happening at same time will be very low. But depending on what the flow does you could have logic do the same for the ID as you said, just add the field. Set the datacard visible to false. Then under the datacard "Update" property you can put your logic to create the ID. Not sure what your logic is but can help with that if you can write it out in words. So when the form is submitted then that ID is created. You can then use Form.LastSubmit.ProjectID to get your ID or you can just set a variable in the Update command or something and use that as well in your Patch() to the other list. 

[Anonymous]

Currently the flow is setup so that when a new item is created in the sharepoint Project List two variables are created, a MaxProjectId and NewProjectID. Then I Get Items from the Project List and sort the list descending based on the ProjectID field with a Top Count of 1. Then I set the variable MaxProjectID to ProjectID, and the variable NewProjectID to MaxProjectID + 1, and insert that value into the ProjectID field for the new record.

I just read how to do that in PowerApps though, so maybe that would be a better solution. 

So would my syntax then be...?

Patch('AHI HDA',Defaults('AHI HDA'),{
ProjectID: {Id: Form.LastSubmit.ID, Value:Form.LastSubmit.ProjectID}})

AHI HDA is the second list in which I want to populate ProjectID.

[Anonymous]

Also, would this code work for the Update on the ProjectID Data Card?

First(Sort('AHI Projects', ProjectID, Descending)).ProjectID+1

[cwebb365]

Yeah those should work, however, If you're just incrementing the ID's I'd recommend piggybacking off the id column in SharePoint. So for the form Success Property, do a Patch to the main project list Patch('AHI Projects', Lookup('AHI Projects',id=FormName.LastSubmit.id), {ProjectID: FormName.LastSubmit.id})

If you need Text or something in the ProjectID then you could use string manipulation like ProjectID: "PROJ-" & FormName.LastSubmit.id etc. to set it. If you need to start at 100 or whatever you can add that value to it for mthe id like 

ProjectID: "PROJ-" & FormName.LastSubmit.id + 100 etc. etc. This will guarentee you won't get duplicates etc. 

[Anonymous]

Ok, but I'm still stuck on how I create a record in the AHI HDA List using that project ID. This code doesn't work: 

Patch('AHI HDA',Defaults('AHI HDA'),{
ProjectID: {Id: Form.LastSubmit.ID,

Value:Form.LastSubmit.ProjectID}})

[cwebb365]

Actually now that I think about it, You have to do a lookup back to the database actually for it, but the problem is, how is your flow running to generate the ID? Is it part of the PowerApp or sitting monitoring the list? If the later, you won't have that value right away to utilize since flow randomly decides to run sometime later and you need the value right away,  so you'll have to move the ID generation to the powerapp first. 

[cwebb365]

Once you get the ID generating then it would look something like

Patch('AHI HDA',Defaults('AHI HDA'),{
ProjectID: Lookup('AHI Projects',id = Form.LastSubmit.id).ProjectID})

[cwebb365]

Oh geez, I completely missed the part where ProjectID is a lookup column. That complicates matters a little lol.... So you use Flow to add the project to the list of projects that everything else has a lookup column too? 

[cwebb365]

Or are the other lists using a lookup column to the main project list?