Can I populate multiple lines in a report from one form

Options

I am trying to automate our joint health and safety checklist. I have created my data sheet with 88 columns and created my form. Each question has a drop down with yes, no or N/A. When no is selected a comment box comes up to insert the issue.

What I want to do in my report is create one row for each issue that arises during the safety inspection. For instance there could be three issues that arise in the inspection and corrective action needs to be taken for each issue. A separate line would be ideal to address each issue.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @stowton

    There's no easy way to do what you want. It would likely take a combination of premium apps.

    Something to consider:

    Of 88 questions, how many are usually "Yes" and need a comment? If it's usually just a handful of 88, why not create a dropdown with 88 questions? The user selects the first question they have a yes for, enters yes, and enters their comments, then submits. Then the form resets and they go again, this time choosing their next "yes" question.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/28/22
    Options

    Hi @stowton

    I hope you're well and safe!

    Here's a possible workaround or workarounds

    • Use a so-called helper sheet that collects the last submission of the form, and then that would be connected to different rows in the sheet and then Workflow with Copy row Action to copy the rows with comments to another sheet.

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

  • stowton
    stowton ✭✭
    Options

    HI Jeff,

    i wondered about this also. All questions have to be answered once each month as part of occupational health and safety around the plant. Can I have one question hit submit and it would automatically go to the next question or would they have to continue to choose from that drop down list each time.

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

    @stowton

    This might help!

    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 help?

    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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    No, it would reset the list each time.

    OK, here's another idea. Create your sheet, but with the questions all in the first column. Second column is your Yes/No/NA dropdown, third column for comments. (Optional, 4th column for Assigned User, 5th column for inspection date.) Select all 88 rows for that set of questions, and send an update request to the assigned user. (You could automate this, by copying Assigned User into all 88 rows at once, and have automation send the update request to that user.)

    They'll get a link to update the sheet, which brings up a multi-page web form. Each question comes up in order on successive pages of the update request; they go from page to page with the arrow button, and at the end, submit all the updates at once.

    So now you have 88 rows, one for each question. You can filter by yes answers, or create a report to only pull Yes rows. Or use Automation to copy Yes rows to another sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • stowton
    stowton ✭✭
    Options

    Hey Jeff,

    I think I can do that! I am very new to smartsheet so this is quite a trial and error and process for me. I may be reaching out for tidbits along the way if you don't mind me pestering you.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @stowton That's how I've learned just about everything in Smartsheet!

    I'd recommend starting out with a couple of rows and testing sending Update Requests - manually or through automation.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • stowton
    stowton ✭✭
    Options

    OK so my leader came back with questions. Sorry. So if I have a generic email address for each of my plants, I would send an open update form button email which they would click on and then they could go in page by page and answer the questions. From there, I can filter by the no's which would have comments and each no would be on a separate line to be dealt with. Is this what I hear you saying

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • stowton
    stowton ✭✭
    Options

    Sorry Jeff, If I do this every month for healthy and safety, my leader thinks that month over month the information will get over-rided and not continue down the page sorted by date entered that I can then pull to a report. Does that make sense? She seems to think that each month I would have to re-enter the questions for a new update form section.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Obviously you would need to create 88 new rows for the next month for each plant. That's how spreadsheets work. Add a month or date column to keep track and to use for filtered views and/or reporting.

    You have options though for what you do with all the data. If you just want to maintain the "no" answers with their comments, you can create workflow automation that copies those rows over to another sheet automatically:

    Trigger: When rows are added, when Answer column changes to "No"

    Action: Copy Rows to "Safety Issues Sheet" (or something like that.)

    Once that month's inspections are over, and all your "No" rows are archived, you could then reuse the same rows from the first sheet, and just send out a new update request.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • stowton
    stowton ✭✭
    Options

    Andree, I am interested in this as well. Can you provide more detail. I think you can see my conversation with Jeff.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!