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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,118)