Help improve my data collection process, with multiple forms, update requests, approvals, reporting!

joelmold

Hi All, I'm considering using Smartsheet for a somewhat complicated process and I could use some help thinking through how this might work. I have the basics listed below but I would appreciate if the experts here could let me know if you see any issues or improvements. Sorry for the long post. Thank you!

Need:

Need to collect plans for returning to work following the COVID-19 stay at home order. Managers are responsible for creating plans for their staff, but the next higher manager will review and approve. Need to be able to report for different groups based on what is submitted. Here's the tricky part, the manager needs to be able to list all their staff with role and contact information, and they might have 0 staff or 50, so using fields/columns does not really work. The manager does not have access to all of the information on their staff, such as cardkey number, so ideally staff could be a child row that rolls up to the manager parent, and we could collect info we need from staff directly. There are likely to be thousands of rows, so we are trying to set this up with as little manual work as possible. The majority of users will not be Smartsheet users, so most interaction for users will be through forms.

Initial thoughts on process:

1. Manager fills out a form that feeds into a master sheet with basic information on unit, location, etc. No staff information yet.
2. Use an Auto-Number column to generate a Request ID
3. Use an Alert workflow when rows are added to send an email to the Manager that includes their Request ID and a link to a Staff info form.
4. This Staff form would also feed into the master sheet but fields would be limited. Manager would list their Request ID, staff name, staff contact info, etc.
5. Manager would repeat this form until they have added all of their staff. Maybe a question about If they have more to add, so we know once they are done.
6. Use Update Request workflow to send an email to staff asking them to add the information the Manager did not have access to.
7. Sort the master sheet by Req ID and change staff rows to be children of the Manager parent row. I think this would have to be a manual process.
8. Use an Approval workflow to send an email to the designated approver for the unit the Manager inputs
9. Use Reports to generate different views for different audiences
10. Managers may need to alter their plans in the future, and I think that is best accomplished by them letting us know (by email?) and us sending a manual Update Request.

Does anyone see any flaws, issues, or possible improvements in this plan?

I have also explored using Google Forms and Qualtrics, but keep coming back to Smartsheet because of the reporting and workflow capability.

Thank you!

Andrée Starå

Hi @joelmold

Good job on the process!

You could change if it makes sense to collect the Staff information in another sheet, and we could even include some information in the form (hidden if needed). Then we would use a VLOOKUP or INDEX/MATCH structure to collect the information to the main sheet by using the Request ID.

Make sense?

What do you think?

Which one do you prefer?

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