cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerAppsQuesti
New Member

How can records be inserted into multiple tables in a Microsoft Power App canvas application?

I have created three tables.

  1. Assignments
  2. Departments
  3. AssignmentsDepartments - This is a table used to link an assignment to one or more departments.

In my canvas application, I have an EditForm control for creating records in the Assignments table. I also added a ComboBox configured for multiple selecting, that allows the user making the assignment to select which Departments will be assigned to.

The idea is upon saving, one record would be created in the Assignments table and one or more records would be created in the AssignmentsDepartments table. (One record for each department assigned to the Assignment).

 

I can't figure out a way to update the AssignmentsDepartments table upon creating a new record. I have an EditForm control for the Assignments table, but I can only set the datasource to one table. I have also seen mention of a PATCH method, however this appears to only allow for creating multiple records in the same table and I need to update multiple tables.

 

Is this possible to do?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@PowerAppsQuesti 

You will be able to do this in the OnSuccess action of the form.

 

Assuming you are just recording the ID's of the assignment and the department, something like this would create the record:

    Collect(AssignmentsDepartments, {AssignmentID: Self.LastSubmit.ID, Department: someDepartmentID})

Not really sure where you are getting your department information from, but you would include that too.

 

Keep in mind the above would just create the records.  Your question didn't really spell out what you wanted to do exactly with the AssignmentsDepartments list and what it should contain, where it comes from, and what should happen to it (i.e. once created, done.  If it exists, update it.)

 

But, the general point is to do this in the OnSuccess action.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@PowerAppsQuesti 

You will be able to do this in the OnSuccess action of the form.

 

Assuming you are just recording the ID's of the assignment and the department, something like this would create the record:

    Collect(AssignmentsDepartments, {AssignmentID: Self.LastSubmit.ID, Department: someDepartmentID})

Not really sure where you are getting your department information from, but you would include that too.

 

Keep in mind the above would just create the records.  Your question didn't really spell out what you wanted to do exactly with the AssignmentsDepartments list and what it should contain, where it comes from, and what should happen to it (i.e. once created, done.  If it exists, update it.)

 

But, the general point is to do this in the OnSuccess action.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

PowerAppsQuesti
New Member

Thank you Randy,

 

This has pointed me in the right direction. A few follow up questions. 

 

1.  I am getting my department data from a table called departments. It is represented on the form by a ComboBox with the datasource set to the departments table. Is there a way I can refer back to the selected departments in the ComboBox when using the Collect method?  Can I do something like this:

 

 

{Name: Self.LastSubmit.ID, Department: CmbSelectedValue.Department , Assignment: NameOfAssignmentJustSavedOnSubmit})

 

 

2. When testing this, I could not use [Self.LastSubmit.ID]. It gives me an error stating: 

 

2021-10-14_15-40-31.jpg

What does Self.LastSubmit.ID do? 

 

Thanks 

 

Edit. I tinkered with this a bit more. For anyone else finding this, here is what I came up with.

Collect('Table Name',{Name: First(cmbDepartments.SelectedItems).Name, 'Assignment Column': Self.LastSubmit, Department: First(cmbDepartments.SelectedItems) }); 

 

A few notes about the above. First(cmbDepartments.SelectedItems).Name refers to a name in the table that is bound to the ComboBox. Self.LastSubmit refers to the assignment that was just submitted. The data value in the AssignmentsDeparments table is a DataEntity. I believe Self.LastSubmit is also of type DataEntity. Same thing when just referring to First(cmbDepartments.SelectedItems). 

 

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,804)