Prepopulate form fields for utility meter update submissions

sstubbs

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.

Matthew J McAteer

Hi @sstubbs,

I don't believe it is possible to create a form with data from two sheets. You would have to bring all of the fields onto a single sheet to start if you want to use a Form.

Matthew