Prepopulate form fields for utility meter update submissions
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!