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

Options

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

Best Answer

Answers

  • Taylor Riley
    Options

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Taylor Riley
    Options

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

  • Taylor Riley
    Taylor Riley ✭✭
    edited 09/14/20
    Options

    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
    Taylor Riley ✭✭
    edited 09/14/20
    Options

    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
    Options

    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
    David Joyeuse ✭✭✭✭✭
    Options

    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
    Options

    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!