Unanswered

Linking forms for data entry into SQL

(0) Share

Report

Posted on by _Brian_

I'm a fairly new user to Power Apps and I have a struggle I need help with. Here is what I have:

The attached pic is for an expense report. There are three sections: Top is the header, which goes to the header table in SQL. Middle is for inserting a new line in the detail table in SQL. The bottom one is the gallery of what has been input as a line item into the detail table in SQL.

Here is what I'm trying to do:

I want the user to be able to enter the header table data for that information and save it to the header table. Then I'd like that portion of the screen to keep that data on the screen and just go to FormMode.View. At the same time, I'd like the HKey field in the middle portion of the screen to get the PKey field from the header and hold it there for all line item entries so that I can have a link from the header to the lines.

My struggle is getting the PKey field to the HKey field. What is the best way to accomplish this? I've tried global variables and context variables with no success. I'm certainly missing something, but can't figure out what! Please help!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

timl 37,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @_Brian_

The technique you would use is to save the header record by calling Patch. There are some details on this technique here.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Retrieve-an-ID-from-SQL-table-after-patching-a-record-to-it/m-p/142441

Set(varHeader, Patch(HeaderTable, Defaults(HeaderTable), {field data} ) )

With the above, varHeader.Hkey will then return the PK value and you can use this to add your line item records.

Was this reply helpful? Yes No
_Brian_ 25 on at

Like (0)

Report
Copy link

Link copied!

@timl Thank you for your reply. I think I'm almost there, but I'm missing something with the patch. I tried running it like I think I understood, but I'm still running into a wall. The {field data} that you mention is what is getting me. With the PKey on the header table not yet defined since it's not saved yet, how do I handle that? If I am just using SubmitForm first, then do I just figure out how to get the record back to the form afterwards?

Sorry for the newbie questions.

Was this reply helpful? Yes No
timl 37,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @_Brian_

If you call SubmitForm, the PKey value will be accessible via the LastSubmit property of the form.

Form1.LastSubmit.PKey

If you store the LastSubmit into a variable (eg, varLastCreatedRecord) in the OnSuccess property of the form, you can set the Item property of the form to the variable in order to display the record that was just created.

Set(varLastCreatedRecord, Form1.LastSubmit)

In terms of calling Patch, the {field data} record would contain the field/value pairs that you want to save. For example:

Set(varHeader, Patch(HeaderTable, Defaults(HeaderTable), {Address: txtAddress.Text, Postcode:txtPostcode.Text } ) )

After you patch the header record, you can retrieve the HKey value and patch the line item record(s).

Patch(LineItemTable, Defaults(LineItemTable), {HKey:varHeader.Hkey, BusinessPurpose:txtBusinessPurpose.Text } )

Was this reply helpful? Yes No