Sign in sheet barcode matching

Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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?

  • sammiereise
    Options

    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?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 06/22/23
    Options

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/22/23
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!