web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Submit Form and Create...
Power Apps
Answered

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

(0) ShareShare
ReportReport
Posted on by Community Power Pla... Microsoft Employee

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.

Categories:
Building Power Apps
I have the same question (0)
  • cwebb365 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

    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. 

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    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.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

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

     

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

  • cwebb365 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

    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. 

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    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 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

    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 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

    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 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

    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 Profile Picture
    cwebb365 3,294 Most Valuable Professional on at

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

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 494

#2
WarrenBelz Profile Picture

WarrenBelz 352 Most Valuable Professional

#3
11manish Profile Picture

11manish 323

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans