Auto-Create Child Rows WITHOUT Bridge or API
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.
Comments
-
@Paul Newcome - this is awesome AND is super useful
Smartsheet Overachievers Alumni
-
-
Thanks for this solution @Paul Newcome. When I try it, the child actions do not copy across and the first column I have does not pull data from the initial form.
The formula I have in the first column is: =INDEX([Job Number 1]:[Job Number 1], MATCH(MAX([Auto Number]:[Auto Number]), [Auto Number]:[Auto Number], 0))
How can I resolve this? Thanks
-
@lauren.green You will need to remove that error from the [Job Number 1] column using an IFERROR statement.
-
Thanks Paul for this post, this was exactly what I was looking for!
Simple and effective.
-
-
This is awesome - however, I have an "intake" sheet that I want to flow to different task lists based on check boxes on the "intake" sheet. Would this work here as well?
-
@PaulNewcome
Hi! Sorry -- new to SmartSheets myself, and I'm trying to follow along. My use case is that I would like a child row for resource for one parent row, company. I've set up a form to dynamically show fields: Resource Name, Resource Bill Rate, Resource PLC, and Resource Hours based on how many resources, so it would populate the respective Resource Name 1 - 10 on the form. What I'm trying to do now is to set up a formula to read each resource # 1 - 10 to create a child row under the company parent, such as:
So, when I set up the formula, nothing seems to happen:
Is there an easier way to do this? I'm trying to keep this simple, but what I would like to see is a child row for every resource entered in 1. Resource Name, 2. Resource Name, 3. Resource Name...etc...
Is this possible?
-
Very good with the formula. It works 100% perfect. I use it
Form Sheet
Work Sheet
excelent thank you very much
formulas that I used
=INDEX(campoestado:campoestado, MATCH(MAX(autonumber:autonumber), autonumber:autonumber, 0))
=INDEX(Campoproyecto:Campoproyecto, MATCH(MAX(autonumber:autonumber), autonumber:autonumber, 0))
=INDEX(camporeferencia:camporeferencia, MATCH(MAX(autonumber:autonumber), autonumber:autonumber, 0))
i only change the submission column name for all
Johann
-
@Susan C. You have to create the clank child rows under the parent row and use formulas to bring the appropriate data into each of the child rows.
-
@Paul Newcome If I want to move the parent row from the form sheet to the work sheet, incluiding the formulas on it, how can I do that?? Actually, this formulas copya the row to the work sheet only with the values, but no with the formulas on it.
thanks for your help
Johann
-
what would the if error statement be? thanks in advance
-
Not sure what I am doing wrong. I am getting an incorrect argument error
-
@Nicole_Codi17 IFERROR wraps around an entire formula.
=IFERROR(original_formula, "")
The image in your last post has a closing parenthesis misplaced. You need to move one from the very end to immediately after the first [Auto-Number]:[Auto-Number] range to close off the MAX function.
-
ALL DAY PAUL, I have been staring at that formula, ALL DAY and it was a parenthesis...ugh! Thanks you, Thank you!!!!!!!
