Hello - looking for help creating my sign in and out form.

bsaucedo

I am fairly new (yet familiar with the possibilities available) in Smartsheet. I am in the process of creating a tracking form for my job to see anytime a device is in use and which employee is in possession of a shared device and what location they are working at that moment. I am collecting the employee's name and ID number, created date, device number, and location they are working. I thought I figured it all out and set up an automation to send me an alert to notify leaders by EOD which devices were not returned. I quickly realized (thanks to other threads) that my one form and sheet would not be able to do what I wanted since new entries to the form do not change the previous rows (statuses/sign in session). Beyond the employee ID and name being text, most of my columns are drop downs.

I created three sheets since that was suggested to the other user (one for signing in, one for signing out, and one to archive the data). I know they suggested that the data would feed from both the sign in and out sheets to the archived, essentially combining whether the devices usage was complete or in progress but I have no idea how to get my sheets to do any of that and share the data to the archive sheet to get the full picture of daily usage. Any guidance on next steps is much appreciated.

James Keuning

I understand the idea of having a sign in sheet and a sign out sheet. But I also think you can accomplish this in one sheet. Setting aside the archive issue, I will discuss the one sheet for sign in and out.

You can have employee ID and name and all that in the sheet, so set that up. But you only need the Device ID for this solution. Your form will collect the Device ID, the Date, and the "Direction" - In or Out. If you really want to have a form for Out and a form for In, you can do that and just set the default for the Direction column to In or Out for each form. But my users would prefer one form, where they pick their name, the device id, the date, and In or Out.

You will have a Current State text column, with this formula:

=INDEX(Direction:Direction, MATCH(MAX(COLLECT([Row ID]:[Row ID], [Device ID]:[Device ID], [Device ID]@row)), [Row ID]:[Row ID], 0))

And an Alert checkbox column, with this formula (this column is not needed, because you can just trigger your alert notification off of the Current State column, but I am including it because why not):

=AND(MAX(COLLECT([Row ID]:[Row ID], [Device ID]:[Device ID], [Device ID]@row)) = [Row ID]@row, [Current State]@row = "Out")

Create a Row ID AutoNumber column.

Your data will look like this:

This will tell you the current state of every device, and the Alert checkbox will tell you if an alert needs to be sent. This works by grouping the Device IDs and looking at the row with the highest Row ID for each device, and recording whether that row is an In or an Out. I used Row ID instead of Date because there is no Time field, so In and Out on the same day will not pick up the Max reliably.*

In terms of archiving, you can set up a move workflow based on how old the record is, but you do not need to take the old records out for this to work, since it is looking for the Max. In fact, you can add a column, I called mine Filter Helper:

And if you create a filter to look for Filter Helper=True, you will see just the current state of everything:

*You can use a Date Created field to capture when a record gets created, and that can solve the time problem (because you can capture time this way), but you may run into problems if you need to add a record to fix something. In this case you will need to add some override logic which is not a problem, it just might not be needed.

bsaucedo

So my forms will have hundreds of possible users that are just visiting the form to gather the data rather them signing in to a sheet and this form will be produced for multiple work locations so adding their names and IDs into the sheet won't be ideal. Their information is only needed in case their leader needs to follow-up, so is it okay if I leave this information a text entry.

Your formulas worked! I just had to do some tweaking to my sheet. I don't think information on this sheet needs to stay beyond a month at most(but we'd like to keep the records until we no longer need them), do you know how I could transfer data here to an archived sheet if it is beyond the month mark. I added the filter helper but did not put any column formulas or anything in this column yet. You have been so helpful, thank you!

bsaucedo

Also, let's say the device is never signed out but the Leader at the EOD wants to mark it returned, is there a way to do that without changing the answers on the form so the alert doesn't keep going out.