Ideas for structuring facility inspection survey

Question

I am trying to digitize a Facility Inspection Workflow that will allow for our Safety team to submit quarterly inspections per each of our facilities (5 total). Each facility has a somewhat different set of checklist items but each set of checklist items has the same questions that need to be answered per item (i.e. satisfactory?, deficiencies noted, date of corrective action, photos, etc.).

I am a little stumped on the best way to approach building out this workflow. My thought now is:

* Have a parent feature class "Facilities" - Will hold only Name, Location, and GlobalID info per each facility.
* Have 1 related inspection table per facility (Ex: Facility #1 - Inspections, Facility #2 Inspections, etc.) - Will hold all remaining fields

This is what the related inspection table for my first facility is looking like:

I guess my question is, is there a better way to structure this? It seems since the same set of questions are asked per each line item there must be some way to condense this? I can't wrap my head around how that would work though with the different items being inspected and in their groups (General Housekeeping, Electrical, Fire Prevention etc.). I also like how clean the survey is looking now with minimal effort for our inspector.

The Safety group wants to do some analysis with identifying Osha regulations that have been flagged recurringly but writing an expression that evaluates so many fields (GH_Q1_OSHA, GH_Q2_OSHA, etc.) might be cumbersome with some surveys having 500+ fields.

Any ideas are much appreciated!!

abureaux · Answer

We have around 60ish facilities in North America. Each prov/state has little quirks, but in general we try and go with the "strictest" guidelines to maintain a standardized approach to facility inspections across Canada/USA.

When a form is submitted, data is validated via Power Automate and subsequently dumped into SharePoint. All data analysis is done in PowerBI.

Our facilities are kept in a CSV, which is used in External Selects within all S123 forms that require offices. Basically, no Feature Layer for facilities as they relate to S123 as that was unnecessary. I do have a FL with facilities, but it is used in a public web map, and very separate from our H&S processes.

All questions relating to a facility inspection are stored within the "Facility Inspection" S123 form.

As I mentioned, we operate in Canada (all provinces) and USA (eastern states, but quickly expanding). At present, we are compliant with all local Province/State regs, regularly exceed the requirements of our external audits, and were (sadly just a) runner up in Canada's Safest Workplaces (though I've added even more since then).

TL;DR, I do way too much H&S work.

maiam · Answer

Thank you for your response @ChristopherCounsell. What you have laid out is essentially what my current structure is.

I guess my main question is would maybe a nested repeat make sense in this scenario since there is redundancy in the questions asked per line item (Satisfactory?, Deficiencies Noted?, Date of Corrective Action, etc.). In my mind that seems like it should be a related table. I don't know the best way to have that though without the inspector having to select which line item she is inspecting per related record.

I like your idea of adding a hidden question to count the number of unsatisfactory choices or maybe a hidden question to build an array of osha regs that have been flagged per inspection (if possible). But is writing a calculation that evaluates 100's of fields feasible?

ChristopherCounsell · Answer

I would suggest

* Facility (point or polygon)
* Inspections (no geometry)

If auditing the first time, survey that creates the facility + an initial inspection (repeat count 1)

If auditing an exisiting facility, launch a different survey that targets the related table only and passes through parentglobalid. This could be from a link and typically seen in Field Maps.

This associates inspections to facilitaties, a 1-M relationship over time.

If the questions for each facility overlap, use relevancy to hide groups/questions based on the facility.

If the questions are all wildly different, then your data is all different, and you may as well build different surveys for each facility (as they are different surveys).

Otherwise building a table for each facility is not great, as you could filter by parentglobalid (or a name field passed through to the related table for laziness).

For your questions, you have redundancy. Yes/no could be replaced with satisfactory/, unsatisfactory. This removes the need for both title and question. Don't feel obligated to recreate the paper form as is. It's a digital form. Have some UI liberty.

Make the questions tied to unsatisfactory relevant to the choice so they are hidden. E.g. photos, date, comments.

If you can, do analysis after data collection. This way your survey doesn't change when different analysis is wanted. Just make sure you're capturing enough data.

You could be lazy and do aggregation within the survey e.g. if any housekeeping questions are unsatisfactory, have a hidden housekeeping question that counts the number of unsatisfactory choices.

Hope this helps. Be prepared to test it and work with all stakeholders as much as you can.