How to get multiple form entries into the same row...?

Bradley Wroten · 09/09/21

Hi everyone, I am looking for help regarding how Smartsheet forms populate rows. We are currently using Smartsheet to complete our annual employee evaluations, which consist of three sections, one for the employee, and two manager sections (completed by two different people). We initially had the employee fill out a Smartsheet form for their section, and triggered a workflow that would send 'Update Forms' for the two final sections...however Update Forms are only based off of column headings (as far as I know), which does not work with the length of our questions, and does not allow for customization (such as adding headings, instructions, etc.).

Now, we decided to have three seperate Smartsheet forms (one for each section), where we manually email the links to the relevant individuals and have them fill out the forms. This is where the issue arises...Smartsheet only allows one form per row, so when an evaluation is complete, we have to manually copy two of the rows up so all the information is in one row (We export the rows to an official document using Document Generator which is why having one row per evaluation is important). This can be problematic as everyone begins there evaluations at the same time but don't all submit them at the same time, so 10 employees could submit an entry before any of the other two sections are submitted. This makes it all the more time consuming to sort through and copy and paste rows etc.... Is there any way to prevent these 'multiple row' entries, or way to use the update form that I am not aware of?...Any suggestions?

Thank you in advance!!

Andrée Starå · 09/09/21

Hi @Bradley Wroten

I hope you're well and safe!

There are multiple ways to structure it.

One sheet each and then connect them with cross-sheet formulas.
Everything in one sheet and then connects them with formulas
It could be combined with the URL-query feature to set default values that can be used to connect the information.

Make sense?

Would any of those options 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!

Kris V. Le · 12/07/22

https://community.smartsheet.com/discussion/comment/304424#Comment_304424

Andree, I have a quick question about this as I'm working on something using this method.

I'm building a sheet that tracks a sample through its lifecycle (Submission - Processing - Results). I'm trying to make this work with multiple forms to track information/tasks at each phase of the lifecycle. At submission, the first column auto assigns a sample ID to the row. On the processing sheet, the first data entry will be to type in the sample ID, and same goes for the results.

My plan is that Sheet 1 will be the submission sheet, but it will also query the Processing sheet and results sheet to add the information back to sheet 1. If I want to get all of this data into a single row on a single sheet, how do I get the cross-sheet to add info to columns based on that sample ID auto-assigned on the submission sheet to that same row?

Am I making sense?

Kevin M. · 12/13/22

@Kris V. Le

I think you would want to structure your Submission sheet, or even a fourth, all-encompassing sheet with all of the columns you'll need. Then it would be a matter of using VLOOKUP or INDEX/MATCH column formulas on the empty columns to pull from your other sheets, using the static Sample ID on each as the common link.

In my experience, you'll need to ensure that the form submitters for all subsequent sheets in the workflow are careful to enter the ID numbers exactly, as typos won't be matched successfully. A "somewhat" workaround to this is to forecast a number of Sample IDs into a dropdown list for the second and third forms in the sequence.

I would love to see Smartsheet add an automation that sends a form to update an existing row. I've encountered so many use-cases where this would have made my life so much easier.

Kris V. Le · 12/13/22

https://community.smartsheet.com/discussion/comment/354109#Comment_354109

Thanks for the feedback. I used this solution for the most part. The main data sheet is the one associated with the sample submission where the first form (sample submission form) starts a row and completes the first several columns. Then the remaining updates to that sample are collected with subsequent forms entering data onto other "hidden" sheets. Then I used a simple VLOOKUP to pull the data back into the main data sheet. Seems to be working well, although you are correct. There are so many use cases to be able to multiple forms to collect data for a single row.

Kevin M. · 12/13/22

Great! I think that would work well enough for most uses. I wasn't familiar with the URL-query method that Andree mentioned, so I looked into that. It seems like a viable option, too. You may still need multiple sheets to get everything into one line, but I think you could also configure an automation to remove incomplete lines. I think I'll play with this option the next time I have this need.

The beauty of it is that it would resend a form URL that pre-populates fields that you specify. For instance, User 1 could fill out the form with fields A, B, and C. You'd have a column that generates a new form URL where A, B, and C are already populated, and User 2 fills in fields D and E. This will create a NEW row with A, B, C, D, and E filled in, and then an automation could then remove the initial, incomplete row.

See this post for an example of the URL formula needed: URL Query String to Populate a Form - Issues with " \ % characters — Smartsheet Community

How to get multiple form entries into the same row...?

Answers

Categories