Better Power Apps: Patching nested Parent/Child records with Auto-Generated IDs using just one Save Button

Introduction

This article is the first in a series on best practices for developing better Canvas Power Apps.

Have you ever wanted to design a Power App, in which you save related data to two separate tables at once, while preserving the link between the two data points being created? Perhaps you are even using Auto-Generated IDs as the Primary Key in the two tables? Then you have come to the right place!

This short article will teach you:

• How to create a Parent and Child record in two different data sources with one Save Button.
• How to save the Auto Generated ID of the Parent Record, in a Child Record column, for easy relationships between the two.
• How to do the same, but for multiple Parent Records, and multiple Child Records.

Initial Setup: Booking and Reminder Entries

This article will use the example of a simple Booking and Reminder Creation input, in which users may enter a Booking Name and Description, and add a corresponding Reminder with a date.

This information is saved to two separate tables: (1) Bookings and (2) Reminders

Both tables have auto-generated primary keys:

And of course we need to be able to look up which booking a reminder is tied to, when we are to send out reminders. Hence, the Reminders Table also includes a column for storing the Booking ID of the Parent Booking.

The Problem: Saving Auto-generated Primary Keys

As our Primary Keys are auto-generated, we don’t know the value of the Primary Key, until the record has been created, making it difficult to Patch the Parent Booking ID in the Child Reminders Table, in the same action.

One workaround I often see, is that makers split up the action in two steps.

First patch the Bookings with one Button, and then retrieve the saved ID, to be used in another button which patches the Reminders. This however, is poor practice, and results in a bad user experience.

Another workaround is using Forms to patch the Booking, and then use the formula LastSubmit(), to retrieve the ID of the latest submitted record. This however, works poorly for creating multiple records.

For our solution, we want the end user to save both the Parent Booking, and the Child Reminder, with one action. To support this, I've designed a simple UI (excuse my poor design choices):

The interface allows for adding a booking, a corresponding reminder, and seeing the created Reminders on the right hand side.

The Solution: Using Set() together with Patch()

The proper solution to the problem is to programmatically store our Patched Booking Record in a variable, using the Set() function. Yes - you can wrap your Patch() function in a Set() function, saving all the patched values, even the auto generated ones, locally!

The code looks as follows:

This code allows us to save the Patched Parent Booking in the variable 'varLastSavedBooking', and reference the autogenerated 'Name' column of the Parent, that is the auto-generated ID, when we patch the Child Reminder Booking.

Testing out the app, we get the following result:

Happy days! We are able to see that the auto-generated Booking ID from the Parent Booking, is available in the child table of Reminders!

Extra: Using Collect() together with Patch() to create multiple records

If you find yourself in a situation where you are using ForAll() to patch multiple records (or Bookings in our case), you will realise that Set() is not compatible with ForAll() - Which makes sense when you think about it, as Variables can’t hold more than one record at a time.

But do you know what can hold multiple records? Tables and Collections!

Hence, we can use Collect() in place of Set(), in our multi-record patch formula, to save all the records in a collection, to be used in our Reminder creation.

First I’ve created a slightly modified interface, allowing for multiple bookings to be created, along with a logic for defining a reminder date.

This time, our code is modified to save the patched values, not in a variable with Set(), but in a Collection using Collect(), which is subsequently used to patch our Reminders:

The end result is, is easy mass creation of reminders, including their corresponding Booking IDs available for Look Ups:

Summary

Using Set() and Collect(), we are able to store one or more records that we are patching to a data source, in a variable or a collection. These variables and collections will even include auto-generated columns, and we can use those values in other formulas further downstream in our Power FX code.

Use this to your advantage, to combine multiple Patch operations into one action for the user, and provide a better user experience!