COVID - unvaccinated test result tracker

Has anyone set up a template package for how to track those persons that opt to do the weekly testing?

We have a number of locations across the US and are seeking a way to manage this.

Having to ensure that test results are uploaded every 7 days creates a need for some automation, but I'm not sure where or how to start.

We do not need anything to capture the vaccine cards as we have a system to do that, but I'm looking for those that will be doing the weekly testing. Capture the results, set reminders, and be able to audit to ensure this is getting done.

If anyone has something that is simple and can be shared, I would very much appreciate it.

thank you in advance!

Find more posts tagged with

Accepted answers

All comments

Becky Cummings

We are in the same boat. Additionally, we need automation to our HR department when a test result is positive.

Genevieve P.

Hi @Tammy Luther

I don't have a template to share, but the way I would organize a sheet like this is to have one top row in the sheet set as my "Alert" row.

Screen Shot 2021-11-11 at 2.00.43 PM.png

Then I would set up a weekly alert to send out with a link to the form, using the "Alert" row as the condition so it only sends once.

Screen Shot 2021-11-11 at 1.59.35 PM.png

You can use the Action Block to only send a message without a link to the sheet or other fields, and include the Form Link in the message (see: Customize the content of your alerts and requests):

Screen Shot 2021-11-11 at 2.00.21 PM.png

Then I would use a Report to Group by WeekNumber to quickly see a number of how many people filled in the form that week. I have a column in the sheet that looks at the Created Date row and returns the Week Number with =WEEKNUMBER([Submission Date]@row)

Screen Shot 2021-11-11 at 2.04.23 PM.png

If I had 3 Employees then this would be a quick way to see that everyone filled out the form.

However if you want to be more granular, you could also have a separate sheet which checks against each Person to see if they've filled it in this week or not:

Screen Shot 2021-11-11 at 2.08.08 PM.png

The Today's Week Number formula is =WEEKNUMBER(TODAY())

Then the Completed Weekly Test formula is a cross-sheet formula which checks to see if the Employee and the Weeknumber exist on the other sheet or not:

=IF(COUNTIFS({Employee}, Employee@row, {Week Number}, [Today's Week Number]@row) > 0, "Yes", "No")

@Becky Cummings you could set up an alert on the original submission sheet so that when a row is added with "Positive" it sends an email to the HR email, no problem. See: Save time and work faster with automated workflows

Cheers!

Genevieve

Tammy Luther

Genevieve,

Thank you for the detail. Although you laid it out with a lot of detail, I'm struggling with the time to be able to create this. I was hoping that Smartsheet would have developed a template to meet this need as I would assume that there a number of folks that would be searching for something already ready to go. Hopefully someone else could benefit from your efforts. Thanks again.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.