Vaccination Status/Testing Report

Stacy Hammons
Stacy Hammons ✭✭
in Smartsheet Basics

Does anyone have an easy solution to tracking employee weekly testing? My thought is that any employee that shows as unvaccinated will receive an alert each week to upload their test results. (I don't have a problem setting that up). From there, if an employee doesn't upload the test results each week then an alert will be sent to our HR team at the end of the day. This is what I'm having an issue setting up. I've tried to set this up several different ways and always run into an issue. I want the process to be as simple as possible for our team. The templates that are pre-loaded don't really work for this process. I feel they are more geared towards the health field. We are only tracking due to the new government requirement.

Does anyone have a solution for this?

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Stacy Hammons

    You are correct, a workflow can only trigger based on what is present, versus checking for something that doesn't exist (ex. a row that wasn't submitted).

    What you could do is set up a second Metric sheet which uses a cross-sheet formula to see if each person has a submission for that week, using COUNTIFS.

    Ex:

    =IF(COUNTIFS({Column with Names}, Name@row, {Created Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())) > 0, "Submitted This Week", "No Submission This Week Yet")


    Sheet Example:

    Screen Shot 2021-11-15 at 10.56.19 AM.png


    Then you can set up a workflow on this Helper Sheet that runs at the end of the week, based on a date, and uses the Condition that Submitted has to be a certain value.

    Workflow Example:

    Screen Shot 2021-11-15 at 10.57.41 AM.png

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Stacy Hammons ,

    Maybe try adding a field update automation to update a checkbox field for "vaccination status uploaded" that automatically populates after the employee updates their status. If this checkbox is not checked, that's what triggers the HR notification?

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

    · Share on FacebookShare on Twitter
  • Stacy Hammons
    Stacy Hammons ✭✭

    That's part of the issue. I may just be overlooking something. When I set up the automation to trigger it only gives me the option to select "When rows are added or changed" or "When rows are changed". If they don't check the checkbox then nothing is added or changed so there's no trigger.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Stacy Hammons

    You are correct, a workflow can only trigger based on what is present, versus checking for something that doesn't exist (ex. a row that wasn't submitted).

    What you could do is set up a second Metric sheet which uses a cross-sheet formula to see if each person has a submission for that week, using COUNTIFS.

    Ex:

    =IF(COUNTIFS({Column with Names}, Name@row, {Created Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())) > 0, "Submitted This Week", "No Submission This Week Yet")


    Sheet Example:

    Screen Shot 2021-11-15 at 10.56.19 AM.png


    Then you can set up a workflow on this Helper Sheet that runs at the end of the week, based on a date, and uses the Condition that Submitted has to be a certain value.

    Workflow Example:

    Screen Shot 2021-11-15 at 10.57.41 AM.png

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Stacy Hammons
    Stacy Hammons ✭✭

    Thanks, I'll give that a try.

    · Share on FacebookShare on Twitter

Categories

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

 Watch video