Looking for some advice.
Our app runs for the purpose of:
- Tracking when equipment is run in an experiment
- Tracking problems on those runs, sometimes multiple problems, and whether they were solved, and by which technician.
- Tracking maintenance of the equipment.
- Tracking various values over time, across multiple experiments and maintenance tests. (Such as temperatures, pressures, magnetic field values, etc.)
- Inventory of all our equipment, and which accessories can be used with each, where they can be used, etc.
My first version of our app tracks all of these values and events for 1-4 on a single sheet, with one slice for maintenance and one for active experiments. Each row was a DateTime, so there were multiple rows for each experiment if there were multiple events. We can record temperatures at a timestamp, but not over time in a nice way. It is workable, but not elegant. Then also the inventory was its own sheet.
My second version, based on feedback from our techs and requests from management for enforcement of checklists, just absolutely ballooned the number of columns beyond what I think is workable. It gotโฆ out of hand in terms of number of columns and what our techs wanted to see in each case, which is different depending on which type of equipment. We wanted a single entry for each experiment, which meant multiple columns for problems/solutions and timestamps for each. Iโve read that there are limits on the number of cells in Sheets, so weโll run into that quickly, mostly on fields that only some entries will actually use. There is no way Iโm putting this version out for use.
So now my thought is this: Have the main sheet to track problems/events for experiments and offline tests of equipment. More of a narrative, with one row for each experiment or offline test. Then, have one separate table for each piece of equipment (about 60). On this table set, each row is a timestamp and can record time-changing variables and maintenance checklists. So for example, on Equipment#19 I can track that the cooling water was changed on Timestamp, and that during a test the temperatures at the next Timestamp were 5 and 7, and an hour later they were 4 and 6. The two sheets will need to have some way to tie together in forms, and I guess that means nested forms, but Iโll have to figure that out at a later time. We will still have a separate sheet for the Inventory, since those are generally not time-variable records.
Am I on a good track here? Is there a way to ensure that each Equipment table will have the same columns across all equipment, so they can later be compared, and so I donโt have to change 60 tables individually every time someone asks for a different value to be recorded? Am I working myself into a different corner than the one Iโm in now?
Thanks for any advice you can give. For full disclosure, Iโm actually one of the techs, and have been asked to develop this tracking system for our team as a side project.
TJ
LinkedIn
Twitter
