QR Codes

Michael Trejo

I’m here at Engage looking to implement QR codes with Smartsheet Forms to track vehicle mileage for University Vehicle’s.

Lindsay Whitbread

Hi Michael,

I see your message is a bit old but we're just wrapping up a project to do exactly that - vehicle loan tracking for a University! The solution included a QR code component to access a form to handle the check out/in process. The complicated part was to detect when somebody had forgotten to check the vehicle back in - which was able to be done with some formulas and exception reports.

It's certainly a good use of Smartsheet, and the QR code element was easy to and makes it a better experience for the user.

Did you end up succeeding with your own project?

Cheers,

Lindsay

Ashley Bowman

Curious as to what you both ended up with. I am working on something similar. My issue at the outset is - I want a user to be able to enter a pre-trip and a post-trip mileage using a form, but I also want for the total mileage to be able to be calculated automatically in a sheet. If they do 2 entries, though, it's 2 rows so I can't just create a column formula to calculate the total mileage. So I instead feel like I will have to rely on someone writing down the mileage when they start, or some other way remembering it... and then when they return the vehicle, making one form entry for both values.

Thoughts?

Paul Newcome

@Ashley Bowman You would need the users to enter some sort of unique identifier when they submit the first form and then enter that same value when they submit the second to be able to pull the two together. Maybe something like a vehicle ID. I would set up two separate forms (one for start and one for finish) so that you can use a default value in each to indicate which entry is which for that vehicle ID. Then you can leverage the vehicle ID as well as an auto-number column (so we can narrow down which entry is for which trip in case the same vehicle is used for another trip later) to pull in the appropriate start/stop mileage and run the calculation.

Ashley Bowman

@Paul Newcome yes - this is what i'm working with, but still having an issue. Vehicle ID will be on there already but i don't think that'll help in this particular conundrum.

if they did 2 different form entries, wouldn't that generate two different auto-numbers though? My struggle is to get the starting and ending mileage in the same row OR to get a way to calculate from multiple rows.

It seems like no matter what, someone is going to have to manually figure out what goes with what, and/or to manually calculate the total mileage. It sounds like you might know of a way to calculate from multiple rows though - any further insights on this?

Thanks!

Lindsay Whitbread

We built a solution for exactly this use case earlier in the year for a University.

The path we decided to go down for the best user experience was to send an automated alert to the person who booked the vehicle, providing a link to the form with the booking ID pre-filled in the form. Then we used a formula to find the start and end rows in the sheet to work out the total mileage.

It worked well and even though we use QR codes to help people get to the blank forms (without the booking ID available), in the end the best option was to take the different approach of using automated emails with links.

Cheers,

Lindsay

Andrée Starå

Hi @Ashley Bowman

I hope you're well and safe!

I recently developed something similar, but for time for a client.

You could use the URL-Query feature to pre-code the user/object in it and then have the form show two options, Start and End or something similar. You could collect them in a so-called helper sheet and then have formulas in the main to collect the Start and End on the same row.

There are also other options, but with the information at hand, I'd recommend this one.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

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!

Paul Newcome

@Ashley Bowman Yes. It will have a different auto-number, but we can write a formula to pull in the next highest auto-number based on the vehicle ID, match on that, and then use an INDEX function to pull in the mileage from that row. Nesting that output into a basic subtraction formula would give us our total mileage for the trip.

I believe we are also working on this in another thread?

Ashley Bowman

I think the issue is (aside from the fact that I have no idea what a lot of the terms mean that y'all are using) all of this seems to rely on the assumption that every user will always do things the way they're supposed to :) If a user forgets to do one of their two steps (either start or end mileage) it will throw the whole thing off. When this happens on a handwritten sheet, the admin person who's translating the handwritten sheet to a spreadsheet just figures out what happened and adjusts accordingly.

Paul Newcome

@Ashley Bowman It would be impossible to try to accommodate everything someone can do wrong. For that particular concern, you can make the mileage field required in the form itself so they can't submit it without entering the mileage.

Ashley Bowman

Right, it's just a common enough user error that I want to accommodate this one in particular. And the concern is just that someone will completely fail to make an entry - not just that they'll miss that particular item.

I'm going to ask around to get user insight and see if they have any ideas on how to make this work for them. Trying to make it work in the way I initially thought, is going to be more complicated than I'm capable of handling as an admin. Thanks for the help!