Using a Form for a Check-in/out build.

Ty Werven

Hello. I am using a form for visitors to fill out. We want this to be put on an ipad to fill out or have an option for a QR code. It looks like this.. pretty basic questions. I want to have it so when you have check-In selected it has all questions, and when check out is selected it only has the name of who is checking out (and maybe room number leaving).

This would ideally go into the sheet such as below.

My question's are these:

-Eventually, we want to have a dashboard that will show visitors at any moment how many people are in a given room. I will need to pull these things that are shown in the form and use them to make that dashboard. Once someone checks out then the dashboard would ideally show one less person once they do the check-out form that is just them checking out, their name, and room leaving. Si this possible to do fully automated (besides filling out the form that triggers it all)

-Should I do this another way? Ideally, I would have a metrics sheet that pulls both room number, and if their is 0,1, or 2 visitors. I would use a count ifs formula to get them using cross refencing.

-I do not know how to get the old visitors out of the sheet once they are gone I think is the main problem. Is their a way that once someone is checking out on the form to remove them completely from the sheet? Or get moved to a sort of "trash sheet"?

SoS | Dan Palenchar

Hello @Ty Werven,

First, you can use form logic to show/hide fields in your form. See here for more info.

To show the number of visitors in the Room you can indeed use a metrics Sheet and a cross sheet formula to count the number of rows where someone is checked in for the current room. Note that your dashboard will only update when you refresh it/it auto refreshes.

You can set up a move row automation that will send a row to a Sheet of your choosing (i.e., "Trash Sheet") whenever someone checks out.

Hope that helps!

Ty Werven

@SoS | Dan Palenchar Hey Dan.

What would I specifically say in the automation to get both of the rows to move? I have been trying to figure it out and do not know how to go about it. Since I would want both the check-in and check-out rows to move once the visitor does a "check-out"

It would have to be something like..

When rows are added (specifically check out rows)

move both their check in AND that check out row to the "trash sheet"

Unless I just made it so the check out row just moves the check in row and it did not represent a value that would count in the metric sheet?

Let me know what you think please. Thanks Dan.

SoS | Dan Palenchar

@Ty Werven

Ahh ok so there are two separate rows for check in and check out, I should have realized this given your form but was operating under the assumption you were inputting one check in row and then modifying that same row for check out.

To manage something like this you will definitely need a unique identifier for both check in/ check out rows. Perhaps the name of the user, but it will have to be truly unique and identical.

It seems like you want the checked in users to be in the Check In Sheet and then removed once they have Checked Out.

What about doing this:

• When users Check In put them in your existing Sheet as it already is configured
• When users Check Out use a move row automation to a Check Out Sheet
• Use a lookup formula in the Check In Sheet to see if the person (unique identifier) exists in the Check Out Sheet. If they do, use move row automation to get them off the Sheet. (Note you might run into an issue triggering move row with a formula, if you do, I would workaround this by using a Change Cell automation triggered by the same formula that does something like check a box to then trigger the move row automation.)
• For your metrics Sheet you can simply count the number of rows in the Check In Sheet to see how many people you have. If you want to control for people potentially entering twice you can use DISTINCT().

Another option would be to take people's email address when they check in and then send them an Update Request to respond to when they check out, so this way they would not check out via iPad. This would update the existing check out row but you might run into issues with people not doing it.

Ty Werven

@SoS | Dan Palenchar

Ideally, the main goal would be for this process to be automated as possible. This is just the way that I originally came up with but not sure if it is the best option. Maybe just editing the same row some how would be possibly easier but we are wanting to use forms to move the rows just so a worker does not have to always be present.

I think this would be the best option for us since we assume some people would not sign out if we relied on them to look at their email vs an ipad.

• When users Check In put them in your existing Sheet as it already is configured
• When users Check Out use a move row automation to a Check Out Sheet
• Use a lookup formula in the Check In Sheet to see if the person (unique identifier) exists in the Check Out Sheet. If they do, use move row automation to get them off the Sheet. (Note you might run into an issue triggering move row with a formula, if you do, I would workaround this by using a Change Cell automation triggered by the same formula that does something like check a box to then trigger the move row automation.)
• For your metrics Sheet you can simply count the number of rows in the Check In Sheet to see how many people you have. If you want to control for people potentially entering twice you can use DISTINCT().

So for this, I will make a check out sheet. Could you provide an example of an automation I could use that would move the visitors that are checking out? Would I provide 2 separate forms each combined to their separate sheets? A Checking-In and a Checking-Out form?

Thanks.

SoS | Dan Palenchar

Hey @Ty Werven,

RE: Could you provide an example of an automation I could use that would move the visitors that are checking out?

This would be via the Move Row Automation. Set the trigger to when rows are added and filter it when the Are you checking in or checking out field is Checking Out. For more on this see below

https://help.smartsheet.com/articles/2480261-move-rows-action

RE: Would I provide 2 separate forms each combined to their separate sheets? A Checking-In and a Checking-Out form?

You can use the existing form you have. The above automation will cause check out rows to move to a seperate Sheet. Then, by referencing that Sheet via a lookup formula (using Visitor Name as lookup criteria) you can update the Check In row and use that update to trigger the Check In row to move in the same way.

Note that this will not work unless the Visitor Name uses the EXACT same name with both submissions, so I would emphasize that in your form OR if you can use the API you can have the Checking Out users select from a DropDown list that would dynamically update with the names from the check in Sheet.

If you want, you can use a separate check out form and skip the move row automation part.

Hope this helps!

Ty Werven

@SoS | Dan Palenchar

Then, by referencing that Sheet via a lookup formula (using Visitor Name as lookup criteria) you can update the Check In row and use that update to trigger the Check In row to move in the same way.

Could you help me with this? All of the examples I am looking up on doing this return a cell value rather than moving an entire row from sheet to sheet.

Here is the main sheet the metric table pulls from/form gets dumped into. I will be trying to take Visitor Name "h" for this example.

Here is the "Dump sheet" so now "h" has signed out using the form connected to the original sheet that moves a row based on being "Checking Out"

Now, I would like to set up the lookup automation so that once the visitor has checked out for it to pull the original check in row back to this sheet in order to keep my dashboard up to date.

Lots of the examples start a sheet from scratch, but since we are pulling these rows into this sheet how do we get this to work? Or am I not following right? Am I supposed to put the lookup automation in the sheet with the form connected to it?

Thanks.