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.

[Tiff]

Wow, thanks for this detailed explanation @MobiusSnake. I can see how you went through a similar thought process in identifying which attributes needed to be in all tables or features. Was the biggest roadblock in calculating repeat attributes based on the parent because of the grandchildren? If so, that might not be a factor for us and may be a feasible option just to have those same fields and have Survey123 calculate them. I don't think I would see running into an issue with that based on my S123 knowledge. Otherwise, sounds like a sophisticated script!

[MobiusSnake]

There are some scenarios, even in simple parent-child relationships, where parent attributes calculated onto the child repeat records can be incorrect.  Here's an example:

• User opens a form and enters a parent-level attribute, like an Asset ID of "123".
• User enters the repeat and populates the first child record, the repeat has a hidden field that copies the parent's Asset ID using a calculation, setting the child record's Asset ID to "123".
• User increments the repeat to move to child record number 2.
• User realizes their Asset ID was incorrect, goes back to the top and changes it to "124".
• User completes child record 2.
• User submits form.

In this scenario, the first child record will still have an Asset ID of "123", it won't be recalculated after the edit.  The second child record will have the updated value of "124".  Recalculation will only occur if the user navigates back through each of the repeat's records.  Setting a calculation mode of "always" in the bind:parameters column doesn't resolve this, either.

Given the upcoming release of the next-gen Survey123 client I don't expect this to be fixed in the "classic" version, but it may not be an issue in the next-gen client.

[Tiff]

Ahhh I see! Thanks for pointing this out. This is one of those situations where it would benefit from a separate workflow. I can see us calculating the relevant parent attributes within Field Maps or another editor, then locking those fields down within Survey123 so they can't be changed and then carry over properly. Of course the same issue applies though if there is any inaccurate or incomplete data within the parent layer (and thus both options #1 - separate layer vs. #2 - related record) where you want to carry the attributes over would both be incorrect. So many considerations...

[MGAL]

You're right to point out that AGOL has some limitations when it comes to representing related data. Arcade can get you what you need using data expressions & other custom logic for symbology & popups. However, in certain scenarios where you need to loop through the entire table (like this relevant example, where a table of inspections is joined to assets), row counts over 100k start to eat away at performance.

Collecting the inspection data using option #2 in Field Maps / Survey123 would be more organized coming back to your desk, and much more user friendly or your end users. You can build custom url parameters to open survey123 / field maps that include prepopulated data from your stormwater assets. 

I would be in support of option #2, contingent on what you need the two datasets to look like as a cohesive whole. Can you provide an idea of what your end goal is? 

[Tiff]

Thank you for this @MGAL! I totally understand that #2 is a better practice. It just stresses me out a bit knowing there may be limitations to visualizing the data and filtering based on the relationship, making it difficult for users or stakeholders to fully utilize or view the data that has been collected. I have one good example I could use for testing (though it has been challenging so far) and perhaps like you said Arcade might be the key to strengthening or getting us closer to a solution. Another issue is there is a known Field Maps bug that has not been resolved - that prevents calculations using "FeatureSetbyPortalItem" tool if there is a related table, which causes a spinning wheel of death when collecting data in Field Maps - it's really really bad.

Another reason why the change management is a bit steep is that for the specific two projects (two types of inspections/evaluations) that we are looking into, have already been created with method #1 - before I joined the organization. So it would not be building something from scratch, but trying to transition away from what is currently in place, although there need to be some improvements (i.e., the unique ID has varied over time pointing us towards downsides of #1 again). Granted they are not visualizing their data in dashboards or anything that would need to be replaced, but change is change.

Essentially I'd like a well sustainable project and product that allows us to have confidence in our data and our ability to visualize and view the data long-term, helping with inspection tracking and so forth. There do seem to be some roadblocks in getting to #2 but I acknowledge it's a better solution. I may band-aid the solution by incorporating better and permanent unique IDs for our data first, and could look at #2 as a long-term solution. I guess in the interim, what I was looking to see is if there were reasons to go with #1 as that's how we currently have it.

[MGAL]

In the past, I've achieved this using Field Maps to interact with the mapped assets. In the pop up for each, I would build a URL to "carry" data from the parent feature to the inspection table to link Survey123, where the inspection would take place. I'm pretty surprised that Field Maps hasn't already put this into practice. As of last year's Dev Summit, "add a related record" is on the road map.

In fact, I just tried this myself. It's still not a thing. FWIW, I published a small sample dataset if you wanna copy it to your own AGOL and make it your own. 

All in all, I can appreciate what you're up against. My advice is play around with option #2 while continuing to maintain the old. Test, trial, train, then roll out.

Best of luck!