Sign in sheet barcode matching
Hello,
We're looking to create a workflow for visitor sign-in where there's a dashboard with two options, to sign in and sign out. We have barcodes on our visitor badges. These are assigned when the visitor badge is assigned to a visitor before arrival. When our visitor signs in, we want to have a dashboard with two options to sign in and sign out. For each, we want the barcode to be recognized from the visitor request form, move that row to the signed in sheet when the signed in form is submitted, and change the status to in-progress. For sign-out, we essentially want the same. Except for signing out, we want the barcode to recognize that from the signed-in sheet and move the row to archived visitors. We're looking to make the visitor logging process automated so employees don't have to go to different sheets to change the visitor information.
Answers
-
There is no way to get it to work exactly how you want it to work, but there may be other options.
How many people do you have signing in and out in a single day?
-
Not very many at all. At most, we've had about 7-10 visitors in one day. What solution do you think could work for this?
-
Hi @sammiereise
I hope you're well and safe!
I've developed something similar for a client recently.
Do you have a barcode scanner at the station where they sign in/out?
There are a couple of ways to structure a solution for this.
Can you go into more detail about the process?
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I would suggest a total of three sheets. First sheet is sign in sheet/form as well as the master tracking sheet. Second sheet is the sign out sheet/form. Third sheet is the Archive sheet.
Both sheets will need the system generated Created (date) type column inserted to track the date/time of the submission.
You would have your visitors sign in using the barcode. In a "Status" column you would use something along the lines of:
=IF(COUNTIFS({Sign Out Sheet ID Column}, @cell = [ID Column]@row, {Sign Out Sheet Created Column}, @cell> [Created Column]@row) = 0, "In Progress", "Visit Complete")
Then you can set up an hourly Move Row automation with a condition of the Status column being "Visit Complete" to push the rows over to your archive sheet.
-
If I'm understanding correctly I think I may have a solution. It is a bit complex and you will have to create your references to the sheet I tried to make it clear by the refrence name what it was referencing. Both your Sign/in and Sign Out Form and your Archive will have all of the Sign In's and outs Your Visitor Request Form will have any pending sign ins and your Sign In/Sign Out Sheet will only show those currently signed in. The only Maintenance it should require will be your Visitor Request sheet adding the Badge # for expected visitors and your Sign In sign Out Sheet for those that forget to Sign Out
You would need a Visitor Request Sheet
A Archive Sheet
A sign In/Out Sheet
A Sign In/Out Form Sheet
On the visitor Request Form have a formula in your status column
=IF(OR(COUNTIFS({Sign In/Sign Out Form Badge #}, [Badge #]@row, {Sign In/Sign Out Form - Sign In/Sign Out}, "Sign In") = 1, (COUNTIFS({Sign In/Sign Out Form Badge #}, [Badge #]@row, {Sign In/Sign Out Form - Sign In/Sign Out}, "Sign Out") / COUNTIFS({Sign In/Sign Out Form Badge #}, [Badge #]@row, {Sign In/Sign Out Form - Sign In/Sign Out}, "Sign In")) > 1), "In Progress", "")
Set your automation on this sheet to move the row to the Sign IN/Out Sheet when the status changes to In Progress
On your Sign In/Sign Out Form set an automation to copy the row to the Archive Automation when the Sign Out column changes to Sign Out - This is so the counts will remain on this sheet.
On your Sign In Sign Out Sheet create 3 helper columns
Sign In Sign out Form Helper - input formula =COUNTIFS({Sign In/Sign Out Form Badge #}, [Badge #]@row, {Sign In/Sign Out Form - Sign In Sign Out}, "Sign In")
Archive Helper - input formula =COUNTIFS({Archive Sheet Badge #}, [Badge #]@row, {Archive Sign In/ Sign Out}, "Sign Out")
Archive Sheet Helper - =IF([Sign In Sign Out Form Helper]@row = [Archive Helper]@row, "Move")
Set up your Automation to on this sheet to Move when the Archive Sheet helper changes to Move
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!