How to trigger a Data Table Lookup on a multi-select contact field

vakhanka
vakhanka ✭✭
edited 08/15/24 in Smartsheet Basics

Hi folks, would anyone have ideas on how to execute the following process, please?

I am building the workflow for an internal award nomination + evaluation process for a company of 850+ people. I have the workflow worked out for people nominated for individual awards, but I am struggling with the best way to adjust this for team awards. This is my process for individual awards:

  1. Nomination is submitted via a form. The form contains a contact field with email validation for the Nominee, populating the worksheet.
  2. Worksheet is connected to a Data Table via Lookup mode that retrieves the Nominee's Manager email from the full employee list.
  3. Worksheets for each award (there are 10+) populate a report that is used in Dynamic view for Managers. Dynamic View is limited by the manager field so the managers can only see their direct reports nominated. They use this view to provide feedback before the nominations go to the next step (not part of this discussion).
  4. Dynamic View is in the Workapp.

The issue I ran into is around the awards for teams instead of individuals.

My constraints:

  1. Teams can be interdepartmental. This means that the team members will have different managers.
  2. The teams can also be quite large.
  3. Teams can also be pulled together for a short term project and then disbanded, so they are not static. This means that I don't have a dataset of Team names with their members that I could use a reference, nor does the employee Data Table contain team names.
  4. An employee may have been part of multiple teams/projects throughout the year.

I can set up the Nominee field to accept multiple selections, but I can't come up with a good method to turn these selections into individual rows on the worksheet so that they trigger the Data Table Lookup. Alternatively, I can make my nomination form have separate fields for each team member, but then the person filling out the form would need to reenter the team/project name next to each team name to distinguish between the submissions. Also, this would make the nomination form super-long to accommodate large teams.

Any ideas?

Thanks in advance!

Tags:
· Share on FacebookShare on Twitter

Best Answer

Answers

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video