Alert when a "check out" event is missing

Alex T.

Hello Fellow SmartSheeters, we're using SS to track when individuals come and go from our laboratory. We're using this to identify individuals at risk of COVID exposure. For example, student A is working in laboratory in limited capacity. She develops COVID symptoms and tests positive. We would like to be able to rapidly identify individuals who were in the laboratory in the 72 hours prior to them testing positive.

In order to determine who may have been exposed, we would like to have a record of when people are in the laboratory. We have a check-in / check-out form that we are using and is working well. However, I would like to send an alert when there isn't a pair of in/outs for a given day.

Any thoughts on how to accomplish this?

Stefan

Hi Alex,

not knowing how your sheets look I still think subtracting the count of OUT from the count of IN would be sufficient to trigger an automated alert. Let it fire off at the time the lab closes and then look up who missed an entry in your sheet.

Another way could be to use a report that only shows rows where the OUT info is missing.

Hope this helps

Stefan

Alex T.

Hi Stefan, thanks for your answer. I have the following columns.

Name(more on that in a second) | Laboratory | Laboratory 1 Member | .... | Laboratory 6 Member | In/Out | Activity

I organized it this way, so we can take advantage of the form logic, so once a lab is selected, only members from a particular lab are displayed to check themselves in. Check in time is captured from the system generated "Created on". In/Out tells us if you're coming or going.

The problem now is:

1) How to find & alert people to a missing "in/out" pair on a given day.

2) The Name column is there to make it easier to see who was there, with out needing to scroll all the way to the right. I was planning on using a nested if statement to fill this in. However, I'm mystified by the SS behavior here.

=[Laboratory 1 Member]@row; yields "Dr. Doom" ... as expected.

However

=ISBLANK([Laboratory 1 Member]@row); yields invalid column value. This is also true for istext etc.

Any pointers would be greatly appreciated!

Stefan

Hi Alex,

a first quick shot at the alert.

But I‘ll start with a few questions.

1) The person needs to be alerted via eMail?

2) As an automated alert needs a trigger; would it make sense to send an alert after a defined timespan?

You would need both to setup a workflow that triggers when person x has no out-time in his row for e.g. 6 hours.

The alert would be sent to the email address of the registered account.

But... are you trusting, that the people read their email in time?

Hope this helps

Greetings

Stefan

Alex T.

Hi Stefan,

1) The person needs to be alerted via eMail?

Alert would go to a person we've designated to manage this.

2) As an automated alert needs a trigger; would it make sense to send an alert after a defined timespan?

Yes, I was thinking after 10pm; it's a lab so it's not uncommon for people to stay late.

Stefan

Well, then just go with the report described in my earlier post and set it to be sent every workday 10pm to the designated email address.

Process ist:

1) Lab worker checks in using a form and the form creates a new row in your sheet.

2) At 10pm the same day a report is being sent to a defined email address. The report has a row for every row in you sheet, where there is no pair of in and out time.

3) The recipient of the report checks the report and does whatever needed to clarify the situation.

So you only need the report and the automated delivery.

Hope this helps.

Stefan

Alex T.

Hi Stefan, thanks for the back and forth. Ideally, smartsheet would automatically monitor for a missing in/out pair. That way we can unburden this individual from having to check the sheets manually.

Also, do you have any ideas what might be broken here:

2) The Name column is there to make it easier to see who was there, with out needing to scroll all the way to the right. I was planning on using a nested if statement to fill this in. However, I'm mystified by the SS behavior here.

=[Laboratory 1 Member]@row; yields "Dr. Doom" ... as expected.

However

=ISBLANK([Laboratory 1 Member]@row); yields invalid column value. This is also true for istext etc.

Any pointers would be greatly appreciated!

Stefan

Hi Alex,

completely overlooked your column structure, sorry!

You try to use Smartsheet as a substitute for an access control system ;-) Even though I think this has some shortcomings, here is a suggestion for you.

I assume you could also work with a SingleSelect Dropdown column for every lab. The dropdown values would be the names of the students. You then could use an easy nested IF formula in the "names" column just for the lab columns instead of all the columns for the students names. Example for 2 labs:

=IF(Laboratory@row = 1, [Lab1 members]@row, [Lab2 Members]@row)

To check if there are missing pairs of In/Out entries i suggest to use a helper column for every laboratory. The column would be a checkbox column and it will be activated if the count for a given name in the lab is uneven.

=IFERROR(IF(ISEVEN(IF(ISBLANK([Lab1 members]@row), "", COUNTIF([Lab1 members]:[Lab1 members], =[Lab1 members]@row))), "", 1), "")

This basically counts the name at the row and if the count is uneven, the checkmark is set.

In the report you should select only rows with the checkmark, sort the report by 1. names and 2. by lab (or the other way round) and have the report being sent automatically to the defined email address.

Hope this time I could help

Stefan

PS: To not loose the formulas AND have them automatically copied into new lines, create two sample rows at the top of the sheet with all the formulas, lock the rows and eventually hide them by using indentation.