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

[PowerAppsQuesti]

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?

[RandyHayes · Accepted solution]

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

[PowerAppsQuesti]

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: 

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