cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ggeorge
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User III
Super User III

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...

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

View solution in original post

4 REPLIES 4
TML
Advocate II
Advocate II

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. 

timl
Super User III
Super User III

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...

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

View solution in original post

ggeorge
Frequent Visitor

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

ggeorge
Frequent Visitor

Solved

 

First(NewCompany).CompanyID

 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (65,412)