Instrument Log Web Form

Graham Watt

Hi All,

 

I am after some advice please.

 

I have currently got a sheet and web form set up that my engineers use to check out instruments when they take them to site and then check back in when they return the instrument or the instrument changes hands however I am running into some trouble.

 

At the moment  every time an instrument is checked out a new line is created within the sheet. This causes a disconnect between the 'Date Out' Columna and the 'Date In' column which means instruments are being missed off the list.

 

Now, having investigated and read up on the subject, there isnt currently a way to get a web form to update an existing line within a sheet (unless I have missed something?) but does anyone have any advice of an alternative way of achieving this?

 

Any help or advice will be greatly appreciated.

 

Thanks,

 

Graham

Jeremy Myers

A webform can not be used to update an existing row in a sheet.  Couple of possible work arounds come to mind:

 

1.  Assign each of the instruments a unique Instrument ID #.  Create two web forms and provide the URL's of each to your crew.  Use one web form for instrument check out, one for check in.  A checkout generates one line, a checkin generates another line.  

 

Create a report that auto sorts on the Instrument ID number and checkout date so you can see those checkout lines that are missing a corresponding checkin line. Use the <send as attachment> routine to autopost that report to everyone on your crew each morning.  

 

Daily, use the <move line to another sheet> routine to remove and archive those in / out transactions that match to keep your report clean.

 

2.  Or: Publish / Share the check in / check out sheet to everyone who has instrument access.  They are responsible for entering their own data, one line for each out / in transaction.  Use the following or similar to check for missing checkin dates and set conditional formatting to highlight the checkin cell an angry red until a date is entered.

 

=IF(ISDATE($[Checkout Date]1), IF(ISBLANK($[Check In Dat]1), "Check In Date Valid", "Check In Date Missing"))

Graham Watt

Wow, thanks for your reply Jeremy.

 

I love the idea of using a report, I never though of that.

 

I had considered publishing the sheet as well but was keeping that as my last resort as most of the time the guys check the instruments out/in using their windows phone so isnt very user friendly.

 

Thanks again for the help.

Jeremy Myers

Auto send reports are my friends!  All you have to do is remove (archive) the completed items daily or every week to keep the thing trimmed down to size.

 

Include a hidden column on the sheet that stores the line create date so you can see exactly when the equipment was checked in / out.  To set it up, double click on the column header and select the approrpiate widget from the <autonumber system> type.  Keeps everyone honest, you can also right click on any data field to get the edit history, but I like the automatic date / time stamping as it is totallly automatic.

 

Happy days!

Tony Davidson - SmartSheet Certified User 2016

Great idea with the two web forms and reports..I would take it a step further and link that reporting into a Sights dashboard as well..

Richard Rymill SBP

Why not use a Report that constantly shows a list of all equipment available for Check/checkin along with Dates out then back in, maybe  add a column that says when the kits is expected to be back in by? So the next user knows or can notify their need for it next? 

With careful design and some extra columns for the sys admin to monitor progress (which dont need to be seen by the App user) wouldnt that be easier? 

The simpler the better for mobile users! 

Hope that helps

Richardr