Auto-Create Child Rows WITHOUT Bridge or API

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 02/17/24 in Best Practice

This initial solution is based on form submissions being made and each form submission triggering the creation of a set of "template" child rows per submission WITHOUT the need for a 3rd party app, the premium add-on Bridge, or the API.

So if your form submissions look like this:

Your working sheet could look like this:

There are a number of modifications that can be made such as the number of children, the number of levels, column data in the child rows, etc.. The below is just the basics to help everyone get started.

So here we go...

On the sheet where your form submissions will be made, you will need two sets of columns. One set that has the data to be pushed over, and another set that collects the data from the form submissions. You can leverage custom field titles in the form so that the form looks decent but the sheet stays easier to organize/manage. You are also going to need an auto-number column.

At the top of the form sheet and utilizing the primary column, you will put in your parent/child "template" set.

There are a number of formulas that can pull in the most recent form entry into that parent row, but for the sake of this example I have used

=INDEX([Form Field A]:[Form Field A], MATCH(MAX([Auto-Number]:[Auto-Number]), [Auto-Number]:[Auto-Number], 0))

Of course that same formula is tweaked and used in the B and C columns pulling from the corresponding Form Field columns.

The final step is creating a Copy Row automation. Set it to trigger whenever the A column changes (or a different column or combination of columns used in the top row of the template to accommodate your needs). The action would be to Copy Row to your Working Sheet.

Additional screenshots are below. This is just the base idea and can be expanded on in a number of ways, but accommodating every single possibility just isn't feasible in a single post.