Prepopulate form fields for utility meter update submissions

Options

On our campus we have 314 utility meters. Electricity, Water, Gas, etc.. Some of the meters are "Smart", but a majority still require manual readings monthly. I'm interested in learning how to create a form, where a user can select a meter, by number, and have specific fields related to the meter in a separate table pre-populate as reference data that isn't editable.

Fields in the Meters table:

  • MeterID (int)
  • RouteID (int)
  • Stop (int)
  • MeterNumber (string)
  • Type (string)
  • Location (string)
  • Multiplier (int) nullable
  • RolloverDigit (int) nullable
  • DigitsToRead (int) nullable
  • IsSmartMeter (int)
  • Notes (string) nullable

Fields in the Readings table:

  • Row ID (auto)
  • Created (auto)
  • Read By (string)
  • MeterNumber (Link drop down from Meters table, can be static list in form though, doesn't change regularly)
  • Type (link from Meters table, Read Only)
  • Location (link from Meters table, Read Only)
  • Multiplier (link from Meters table, Read Only)
  • LastReading (return previous value from latest submission in Readings table where match on MeterNumber - Read Only)
  • NewReading (int)
  • Notes (string)

Today this data is captured on paper, then transposed into a spreadsheet, and imported into a MS Access database. Not ideal, but it's where we are today. Sometimes there are errors in the meter readings recorded categorized as mis-reads. In this scenario, we believe adding the option to attach a photo from a mobile device which is accessing the the form would resolve this as a reference if needed.

We have another form in production use today for storing monthly odometer readings for our fleet, and it works well for what we need. But I would like to learn how to look up last months submission for a given vehicle number and have some validation to prevent new readings from being less than the previous month's reading, and not greater than n# of miles to prevent mis-reads. I could see this use case being useful in this new project I'm working on today for utility meter reading.

I'm hopeful someone else has solved this solution using Smartsheets already. I've watched a few videos on YouTube about using the URL query string to pre-fill data, but I'm not following it entirely. I keep getting #UNPARSABLE and I'm not really sure how to setup all the dependencies either. Any guidance is greatly appreciated.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!