Vaccination Status/Testing Report

Stacy Hammons

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?

Genevieve P.

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:

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:

Cheers,

Genevieve

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?

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.

Genevieve P.

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:

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:

Cheers,

Genevieve

Stacy Hammons

Thanks, I'll give that a try.