Retrieve an ID from SQL table after patching a record to it

(0) Share

Report

Posted on by ggeorge

Hi

I'm fairly new to PowerApps. I have two forms in my app, one called Supplier and another called Supplier Services.

On the Supplier form I have a submit button:

Patch('[dbo].[tblCompany]',Defaults('[dbo].[tblCompany]'),{Name: TextInput1.Text});Navigate(SupplierServices,ScreenTransition.Fade)

Now on the Supplier Services screen, I need to get the value of the ID back from SQL for the newly patched Company so that I can assign services against that company.

On the submit button of the Supplier Services form I want to patch the services across, along with the SupplierID

Patch('[dbo].[tblSupplierServices]',Defaults('[dbo].[tblSupplierServices]'),{SupplierID:Value(TextInput3.Text),ServiceType: ComboBox2.Selected.ServiceID});Navigate(SubmissionScreen,ScreenTransition.Fade)

At the moment I can't seem to recall the ID, I have played around with variable but have had no joy.

Is there a way?

Many thanks

Gareth

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

TML 185 on at

Like (0)

Report
Copy link

Link copied!

By ID, do yoou mean a GUID or Auto Increment created in SQL? If so, I think you will have to query your company table with filter or lookup and using enough field/data to uniquely identify the company if company name is not enough. You can pass the info between screens with the Navigate function optional parameters and retrieve the record OnVisible of the second form. If I understand your question correctly.

Was this reply helpful? Yes No
Verified answer

timl 37,212 Super User 2026 Season 1 on at

Like (7)

Report
Copy link

Link copied!
Hi Gareth,

The return value from Patch includes any server generated identity values. If you did this for example,

ClearCollect(NewCompany, Patch('[dbo].[tblCompany]',Defaults('[dbo].[tblCompany]'),{Name: TextInput1.Text}) )

... you could use NewCompany.ID to retrieve the identity value that SQL Server generates.

The same method applies if you were adding multiple records via Collect, as I mentioned here:

https://powerusers.microsoft.com/t5/General-Discussion/Can-you-return-primary-key-ID-after-DB-insert-SQL-Server/m-p/90812/highlight/true#M34246

Hopefully, this gives you enough to work with. If not, let us know.

Was this reply helpful? Yes No
ggeorge 27 on at

Like (0)

Report
Copy link

Link copied!

Hi Timl

This is great, thank you.

I noticed in the collections tab it has the ID. How do I recall this into a text box on the Supplier Services form. I keep getting an error message.

Thanks again.

Gareth

Was this reply helpful? Yes No
ggeorge 27 on at

Like (1)

Report
Copy link

Link copied!

Solved

First(NewCompany).CompanyID

Thanks for you're help, that has been bugging me for hours 🙂

Was this reply helpful? Yes No