Inspections Data Management - looking for suggestions

[Tiff]

Hello all! I'm hoping to gather input and suggestions about best practices and data management when it comes to mapping inspections - such as outfall inspections and evaluations, stormwater inspections, etc.

Here are two options in mind, with examples:

1. A feature layer for storm drains/outfalls/etc. AND an unrelated feature layer to map outfall inspections (e.g., one point for each inspection) with a common ID.

2. A feature layer for storm drains/outfalls/etc. AND a related table containing all outfall inspections.

I know best practices for data management will often point towards #2; however, there are such limitations to visualizing data and trends with related tables using ArcGIS Online as well as other potential data collection issues (bugs associated with AGOL arcade calculations with related tables), I'm wondering if anyone has more to share regarding either option, what their organization does, and any advantages/pitfalls that they've experienced.

Thank you!

[MobiusSnake]

I've used both approaches.  Since the second approach is probably more common, I can tell you about my experience with the first, particularly why we went with it.

On a job a few years back, the group I was working with had an asset type that would require various types of inspections and jobs in the future, but the specific types of inspections and jobs weren't fully defined, with a high likelihood of new job types being added in the future.  To keep it flexible (and avoid having to add new related tables to an existing service, since that's a pain), we went with an "asset service" that had one feature layer, along with one service with a table for each type of inspection/job that we needed.

The asset layer had Global IDs enabled, while each inspection and job table had a GUID field named "parent_id", a lot like how you'd set up the child dataset in a relationship class.  We used Field Maps to access assets, with a pop-up that had links to the various types of S123 inspections and jobs.  The Global ID was passed in as a parameter to a hidden field in the S123 forms, and saved in the parent_id field.  We also passed a human-readable ID through, to make queries against the "related" tables easier.

Visualization was primarily through dashboards, and it was easy enough to link the assets with the various types of inspections and jobs using the Global ID - GUID link.

One theoretical downside would be the loss of cascading deletes that relationships provide, but there was never a case where we wanted to delete our assets, so that had zero impact.  It could also be a bit of a hassle if you were working with the data in Pro, it's nice having those relationships already in place to traverse from parent to child in the Attributes panel (but a relate within a map would do the same thing).

Since the inspections/jobs were linked to the asset, there was no reason for us to make the "related" tables spatial, non-spatial tables were fine.  If you want to map them independent from the assets themselves, there might be an argument to copy the original geometries, or capture a new one.  (Especially if your assets are lines or polygons, you could capture the specific inspection point along the line or within the site.)

[Tiff]

Hi @MobiusSnake, thank you for your detailed response! I appreciate your input. So if I'm understanding correctly, the biggest reason for your separate/unrelated tables (but keeping the GlobalID - GUID link) is because there would be variations in how many more inspection tables you would need to add to the overarching layer/data schema (requiring routine republishing, etc.) 

The same would actually apply for our situation, but to a lesser extent - there may be two related tables (possibly not more) but they won't be completed at the same time. 

Were there any other reasons you opted to go this route? I actually feel that with this approach, you may still met with some of the limitations I've run into, such as building an ArcGIS dashboard and not being able to filter on attributes unless it's the GlobalID/GUID attribute; for example, if you were to filter by "park name" for the parent layer, the filter wouldn't work for the standalone table unless the table itself also had the "park name" attribute. Did you run into this consideration, and/or bypass some of these issues by hardcoding the attributes needed for filtering in the tables? I do also feel that non-spatial tables likely have their limitations in ArcGIS platforms too - though I can't speak to any specifics off the top of my head.

Similarly, pop-up functionality - did you find creative ways using Arcade to display all the related records? That would also be another workaround needed for something that related record functionality would support out-of-the-box (despite its limitations).

And another broader question about the data collection - so you were able to link Field Maps to various Survey123s, and so each Survey123 itself pertained ONLY to the individual table, correct? The only reference to the feature layer was its GlobalID value?

Thank you again so much!

[MobiusSnake]

Although it wasn't a concern on the project I mentioned (filtering on the human-readable ID that was passed along to the "child" table was all we needed), I have run into similar limitations on other projects, but still used a proper relationship.

On a natural resources project I worked on last year, we had a survey where basic information about each site was collected.  Within that were four repeats (related tables in S123) where more detailed data was collected.  These related tables contained most of the values that were used for analysis, but having several of the site's main attributes were important for filtering.  To complicate it further some of these repeats had their own nested repeats, so we had a parent-child-grandchild situation with multiple children and multiple grandchildren.

To make analysis of the child and grandchild tables easier, I did the following:

• Created copies of the main layer's important filtering attributes on the children, but did not make them part of the survey
• On the main layer and all child/grandchild tables, added a "processed" flag (0 / 1 value) that was not part of the survey, and defaulted to 0 (No)
• Created a notebook that would do the following:
  • Query all records from the main layer where processed = 0
  • Cascade the key attributes from the parent to the children, and then down to the grandchildren
  • As part of the edits, I set the processed flag on each edited record to 1
• I set this notebook to run on a schedule every few hours
• Users working with this data did so using views that filtered all layers and tables so only records with processed = 1 are visible

Survey123 does include the ability to automatically calculate repeat attributes based on those of the parent, but there are some scenarios I've found where that isn't 100% reliable (particularly with nested repeats in the mix), so I don't like relying on that.