Use one row to create multiple new rows in a sheet

Options

I'm trying to see if there is a way to use automation to create multiple new rows in a sheet when a row is added through a form.

My thought process was that I could setup an automation on the sheet that would move the row to another sheet and then I could have automations in the second sheet that would update a couple of fields and then move the row back to the first sheet. Then when the "updated" row got moved back to the original sheet, an automation on that sheet would copy that row back to the second sheet and additional automations in the second sheet would update the necessary fields to their new values and then move the row back to the original sheet, thereby creating a 2nd new row in that sheet. These back-and-forth process would continue until all of the additional rows were created in the original sheet. There should be no rows remaining in the second sheet because they were always moved back to the original sheet.

When I test this, the automation in the first sheet successfully moves the row to the second sheet and the first automation in the second sheet successfully updates a field in the row. But when the next automation in the second sheet is triggered to update another field in the row, it seems to be delayed long enough so that the next automation that is designed to move the row back to the original sheet never gets triggered. The I get a "workflow has become invalid" email from Smartsheet for the next automation that was designed to be triggered when the original sheet automation copied the "new" row back over to the second sheet in order to begin the process to "create" the next new row that I need in the original sheet.

I realize that is probably confusing to follow but hopefully it makes sense to someone and they will have some suggestions as to what I might be doing wrong - or tell me there is absolutely no way to do this in Smartsheet.

Answers

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭
    Options

    I have no insight or suggestions but would love to know if this is possible and the solution.

  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭
    Options

    So I've reduced the automations in the second sheet down to where only one field is updated before the row is moved back to the first sheet. But I still get the same error in the second sheet after the "new" row in the first sheet is copied back to the second sheet in order to update the field again in preparation for creating the 2nd "new" row in the first sheet. Below is the email I receive from Smartsheet. I don't even have 5 automations defined within the second sheet so I'm not sure why it is saying it was triggered "by at least 5 other workflows".


  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭
    Options

    So I changed my logic so that I have a single workflow with conditional paths to update the field depending on the current value of the field. But I still get an error from Smartsheet (albeit slightly different from the previous error).


    Here is how I have my workflow defined. It seems to me this should work but I'm guessing the logic within Smartsheet is not the same as my logic?


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Michael Chohrach

    I hope you're well and safe!

    Strange!

    I'd be happy to take a quick look.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭
    Options

    Hi @Andrée Starå

    Thanks for your kind offer to take a look.

    So I'm trying to use Smartsheet as a helpdesk tracking solution for our company. I started with the Smartsheet-provided template set and revised it to fit our needs. One need we have in a helpdesk system is to be able to push out separate tickets for various support resources when onboarding a new employee.

    Rather than having one ticket that is "assigned" to multiple people, I was hoping to be able to "split out" the initial ticket into individual tickets (rows) that would each be assigned to the responsible resource. So my intent was to first move the initial row (created from a form submission) over to a "temporary" sheet, (the initial row is not meant to be assigned to anyone so that's why I do a move instead of copy) where the ticket category would be updated to the appropriate category for the first resource. This "updated" row would then be moved back to the primary sheet where a workflow would then assign it to the appropriate resource based on the new category value.

    This row would then be copied back to the "temporary" sheet where the ticket category would once again be updated to the appropriate category for the next resource. Again, the updated row would be moved back to the primary sheet where a workflow would once again assign it to the appropriate resource. At this point there should now be two separate rows in the primary sheet, each assigned to it's respective resource.

    This process would repeat itself until all of the new, individually assigned rows are added to the primary sheet and no rows exist in the temporary sheet.

    At this point there is nothing confidential on these sheets so I will go ahead and share them both to you so you can hopefully better understand what I'm trying to do. Let me know if you have any issues accessing them.

  • bigbluebottle
    Options

    Hi, I'm wondering if you ever solved for this. I have a similar scenario where I'm asking people to fill out a form for a new project request. I then want to automate the creation of a handful of child rows beneath that for financial data (e.g. CAPEX - HW, CAPEX - SW, OPEX - Maintenance) so people can then provide financial details by cost account. Would love to know your outcome.

  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭
    Options

    @bigbluebottle I was never able to come up with a solution through automation. I ended up creating "daisy-chained" forms to create the individual assignment rows. As each form is submitted, the next form in the chain is presented to the submitter until all of the forms have been completed. Because each of my rows are standard since they are part of a standard onboarding process, I was able to setup each form in the chain to default to specific values for what would have normally been variable column values so there is very little the submitter has to fill in on each form in the chain.

  • bigbluebottle
    Options

    I'd love to see an example of this! I don't know how you "daisy chain" forms to be honest. :-(

  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭
    Options

    All you need to do to "daisy chain" forms is to reference the URL of the next form in the sequence on the current form settings page. When the form is submitted, the next form will open, and so on. On the last form of the sequence you just make this setting the default "Display this confirmation message".


  • Gemma_01
    Options

    I understand the form logic in terms of daisy-chaining, is there a way to copy the information entered by the user into the following forms so that they can be associated to the first line?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Gemma_01

    I hope you're well and safe!

    I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.

  • Gemma_01
    Gemma_01 ✭✭
    edited 02/16/23
    Options

    Thank you Andrée,

    We would have multiple users using the form so it is more how do you ensure that for one user all rows share the same initial information and then the new row would contain individual breakdown relating to that project?