Can I populate multiple lines in a report from one form

stowton · 02/25/22

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.

Jeff Reisman · 02/28/22

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

Andrée Starå · 02/28/22

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!

stowton · 02/28/22

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å · 02/28/22

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

Jeff Reisman · 02/28/22

https://community.smartsheet.com/discussion/comment/321073#Comment_321073

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.

stowton · 03/01/22

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 · 03/01/22

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

stowton · 03/01/22

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 · 03/01/22

https://community.smartsheet.com/discussion/comment/321199#Comment_321199

Exactly.

stowton · 03/01/22

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 · 03/01/22

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.

stowton · 03/02/22

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

Can I populate multiple lines in a report from one form

Answers

Help Article Resources

Categories