Vehicle mileage: pre-trip and post-trip mileage, auto calculating multiple rows

Ashley Bowman

I've created a new system to help out the finance department at my workplace to be able to pull in multiple types of information. The part I am hung up on involves vehicle mileage.

The user will enter all the required information via a smartsheet form to replace the paper forms. This is what info I need for every trip:

-the type of charge (vehicle mileage; auto fills via a hidden field on the form)

-the date (auto fills with created date)

-the name of the PI/lab

-a description of the trip

-the account/fund information "project number"

-the vehicle number

---user selects "mileage type" on the form to determine whether they will see "starting" or "ending" mileage fields---

-the starting mileage

-the ending mileage

Ultimately, I want the sheet to auto-calculate the TOTAL mileage for each trip, so that I can then use that mileage to auto-calculate how much should be charged to the PI/lab account listed.

Initially, I thought that at the end of each trip, the user would enter all of this information at once. However, in discussing with the finance crew here we realized that this would require the user to make a note somewhere of their starting mileage to save for later so that when they get back they can enter both the starting and ending mileage. It was suggested that people make two form entries when checking out a vehicle - the first entry for starting mileage, the second entry for ending mileage. However, this creates two rows so I can't as easily use the sheet to auto-calculate the total mileage. (There are multiple vehicles so there could easily be rows in between so it's not necessarily going to be the case that the pre-trip and post-trip row for one trip will be adjacent.)

The goal is to simplify the process for EVERYONE. My options seem problematic:

1. Ask users to write down starting mileage somewhere so that they have it to make their post-trip log. Why bother digitizing if they are still having to use paper?
2. Ask users to make one pre-trip and one post-trip entry. This means they have to do two steps, and also means that finance will have to calculate by hand on the back end.

If it's not going to be easier than using paper, it's not going to fly. Any suggestions?

Paul Newcome

Quick question about your sample data...

You have the same vehicle number starting two trips with the same starting mileage without an end of trip submission in between. Is that an actual possibility?

Andrée Starå

https://community.smartsheet.com/discussion/108872/vehicle-mileage-pre-trip-and-post-trip-mileage-auto-calculating-multiple-rows

Hi,

Answering here too, but let me know if it's another goal in mind with this one.

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!