cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stephenkln
Resolver I
Resolver I

Adding Records via Form to Related SQL Tables

Hi all,

 

So I'm trying to build an app to view and add employee records to a SQL database. I'm learning as I go. My app is currently set up with two screens.

 

Screen 1 = View Employee Records

This screen has a gallery that populates employee names from the employeesTbl, and job titles via lookup to the jobsTbl.

On selecting an employee in the gallery, the most recent records for that employee (via a SQL view) will populate into a form.

 

Screen 2 = Add New Employee Records

 

Let's say my basic SQL table structure looks like this:

stephenkln_0-1593640055434.png

What I'm trying to do is on Screen 2, have either a form or text input boxes to add records to the detailsTbl and moredetailsTbl.

I want to add records to the appropriate employee based on whichever employeeId is selected in the gallery.

So if employee John Smith with an employeeId of 1001 is selected in the gallery, I want to add records to the related tables for employeeId 1001.

 

All of my tables are related appropriately in the SQL dB. What would be the best way to implement this in power apps?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @stephenkln ,

Do you want to add new records into your detailsTbl and moredetailsTbl related to current selected Employee in the Gallery?

 

Based on the needs that you mentioned, I think two Edit forms could achieve your needs. You could consider add two Edit form in your Screen2. One for detailsTbl, another for moredetailsTbl.

Connect EditForm1 to detailsTbl, connect the EditForm2 to moredetailsTbl. Set the DefaultMode property of the EditForm1 and EditForm2 to following:

FormMode.New

then enable desired field data cards (including the "employeeId" field data card) within the EditForm1 and EditForm2 . Within the EditForm1 and EditForm2 , unlock the "employeeId" field data card, then set the Default property of the "employeeId" Text Input Box to following:

Gallery1.Selected.employeeId  // bind the selected employee to new entry back to detailsTbl and moredetailsTbl Table

Then add a "Submit" button in your Screen 2, set the OnSelect property to following:

SubmitForm(EditForm1);
SubmitForm(EditForm2)

Note: The Gallery1 represents the Gallery in your first screen.

 

In addition, you could also consider use the Patch function to patch new entry back to your detailsTbl and moredetailsTbl Table. Please consider modify the formula within the OnSelect property of the "Submit" button to following:

Patch(
      '[dbo].[detailsTbl]',
      Defaults('[dbo].[detailsTbl]'),
      {
        employeeId: Gallery1.Selected.employeeId
      },
      EditForm1.Updates
);
Patch(
      '[dbo].[moredetailsTbl]',
      Defaults('[dbo].[moredetailsTbl]'),
      {
       employeeId: Gallery1.Selected.employeeId
      },
      EditForm2.Updates
)

Patch function

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Drrickryp
Super User
Super User

Hi @stephenkln 

In your SQL tables the name of both the primary key and the foreign key are the same.  This will create a problem with disambiguation in PowerApps.  Disambiguation occurs when you want to reference a field in another table that has the same name as that table referencing it in a PowerApps formula.  While there is a work around for that problem, it would be better if the Primary Key of each table was simply ID and foreign key stayed just like you have it (concatenating the table name containing the primary key and "ID").  Would it be possible to rename the Primary keys in all of your tables back to simply ID. Like in the screenshot below?   If you can't do it, there are a number of ways to get around it in PowerApps but it is better to avoid the problem in the first place. If you cannot avoid it, watch this video by @mrdang ,  https://www.youtube.com/watch?v=ApdKlDTRprA  relations.png

v-xida-msft
Community Support
Community Support

Hi @stephenkln ,

Do you want to add new records into your detailsTbl and moredetailsTbl related to current selected Employee in the Gallery?

 

Based on the needs that you mentioned, I think two Edit forms could achieve your needs. You could consider add two Edit form in your Screen2. One for detailsTbl, another for moredetailsTbl.

Connect EditForm1 to detailsTbl, connect the EditForm2 to moredetailsTbl. Set the DefaultMode property of the EditForm1 and EditForm2 to following:

FormMode.New

then enable desired field data cards (including the "employeeId" field data card) within the EditForm1 and EditForm2 . Within the EditForm1 and EditForm2 , unlock the "employeeId" field data card, then set the Default property of the "employeeId" Text Input Box to following:

Gallery1.Selected.employeeId  // bind the selected employee to new entry back to detailsTbl and moredetailsTbl Table

Then add a "Submit" button in your Screen 2, set the OnSelect property to following:

SubmitForm(EditForm1);
SubmitForm(EditForm2)

Note: The Gallery1 represents the Gallery in your first screen.

 

In addition, you could also consider use the Patch function to patch new entry back to your detailsTbl and moredetailsTbl Table. Please consider modify the formula within the OnSelect property of the "Submit" button to following:

Patch(
      '[dbo].[detailsTbl]',
      Defaults('[dbo].[detailsTbl]'),
      {
        employeeId: Gallery1.Selected.employeeId
      },
      EditForm1.Updates
);
Patch(
      '[dbo].[moredetailsTbl]',
      Defaults('[dbo].[moredetailsTbl]'),
      {
       employeeId: Gallery1.Selected.employeeId
      },
      EditForm2.Updates
)

Patch function

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,855)