How to auto populate subtasks from information coming in from a new Form entry

Taylor Riley

I found the below discussion, but after playing with cell linking, still wasn't able to figure this out. I have a form that marketers will fill out to create a request. After the request is entered, 3 teams need to complete an assessment based on the information provided. right now, I'm coping the request row over to the assessment sheet and then adding the children rows that are the tasks. These tasks will never change, so I'm hoping to automate this now very manual process.

https://community.smartsheet.com/discussion/17181/how-to-auto-populate-insert-10-15-task-rows-as-child-items-under-new-project-row

David Joyeuse

Hi @Taylor Riley

If the children tasks are always the same for the three teams, then it's quite possible to come up with an automated system that will do everything by itself.

In my view of it (which is purely theorical for now) it would be like this:

• Have a [Date created] automated column on the form, that is hidden.

On another sheet, create the parent row and children tasks with a [Date Submitted Request], gather all the information you need from the request using INDEX/MATCH, VLOOKUP.

In the [Date Submitted Column] use this formula for the parent row (if needed create a Rank helper column to differentiate Parents/Children):

=MAX({Request Sheet - Date Created})

If all the three teams have assessments to do, I believe you don't have to sort request depending on information they provide to decide to which teams they go.

On this sheet, create an automated workflow that will copy the parent row to the teams' sheets each time the [Date Submitted Request] is modified.

The parent row and children will be copied onto the teams' sheet automatically while you're doing pretty much nothing.

As I said, that's pure theory from my end, as I haven't tested it, but it should be working. Requires a bit of work too :)

Hope it helped!

Taylor Riley

@Andrée Starå, you helped out with this original request. I'm sure I'm missing something obvious, do you have any other tips?

Andrée Starå

Hi @Taylor Riley

Depending on the structure/use-case, it's possible to automate and copy the parent row to the other sheet, and then all the children will also be included.

Would something like that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Taylor Riley

@Andrée Starå, That sounds like it could work. I have the automation setup to copy the parent row to the Assessment sheet, but I haven't figured out a way to incorporate the necessary child rows. Is there a way to control where the form row will be copied? That way if I include space for future requests in the Assessment sheet, the children rows are already there.

David Joyeuse

Hi @Taylor Riley

If the children tasks are always the same for the three teams, then it's quite possible to come up with an automated system that will do everything by itself.

In my view of it (which is purely theorical for now) it would be like this:

• Have a [Date created] automated column on the form, that is hidden.

On another sheet, create the parent row and children tasks with a [Date Submitted Request], gather all the information you need from the request using INDEX/MATCH, VLOOKUP.

In the [Date Submitted Column] use this formula for the parent row (if needed create a Rank helper column to differentiate Parents/Children):

=MAX({Request Sheet - Date Created})

If all the three teams have assessments to do, I believe you don't have to sort request depending on information they provide to decide to which teams they go.

On this sheet, create an automated workflow that will copy the parent row to the teams' sheets each time the [Date Submitted Request] is modified.

The parent row and children will be copied onto the teams' sheet automatically while you're doing pretty much nothing.

As I said, that's pure theory from my end, as I haven't tested it, but it should be working. Requires a bit of work too :)

Hope it helped!

Taylor Riley

Thank you, @David Joyeuse! Apologies for the late reply, this is back to priority number 1 this week.

So far I've done the following:

1. Created an additional sheet for formatting that matches the columns on the request form (including a "Date Submitted" column
2. Added the Parents/Children rows with the pre-populated tasks for each team to complete
3. Added this formulate =MAX({Date Completed - Request Form}) to the "Date Submitted" Column on the formatting sheet

I'm working on implementing the INDEX/MATCH formula on the parent row to pull the information submitted in the requests. I'm getting confused on what search value to use for the MATCH piece since the parent row on the formatting sheet doesn't have any information in it to match.I feel like there's an obvious formula step that I'm missing

Taylor Riley

I think I might have figured out the formula gap,

I'm now Indexing the column that I want it to return and having the MATCH look at the [Date Submitted] columns. Going to add this to a few more columns to see if it works.

=INDEX({IRPL Lookup Column}, MATCH([Date Created]@row, {IRPL Date Submitted Column}, 0))

Taylor Riley

The INDEX/MATCH Formula above above to be working!

However, now I'm having problems getting the automation to work. I have the below workflow setup. Although I've entered a few new test requests, and nothing has been added to the Assessment, not even the parent row.

Setup for the automation is:

Trigger: When rows are added or changed

When: Date Created

Changes to: Any Value

Copy Rows to Assessment Sheet

David Joyeuse

Hi @Taylor Riley

Regarding the setup of the workflow, I would switched the trigger to "when rows are changed" forst.

Switch to the column "Date submitted", because that's this one that is looking for the max date.

Also, in your INDEX/MATCH use the date submitted as well, date created won't return anything because it's an automated cell and won't match anywhere in your form request sheet.

Hope it helped!

Taylor Riley

Hey David!

The information provided above worked great! Thank you for that input.

I ended up implementing those changes in my Test sheet and was able to get the automation to work earlier this week. However, after making these changes in the live sheets, I can't get the automation to work again. I have another discussion open to troubleshoot where I've referenced this discussion: https://community.smartsheet.com/discussion/comment/263110#Comment_263110

Thanks again for your help!