Update Parent Table when Related Record is Created

[alex_mapintel]

Say I have a feature service named "trees" (hosted feature service in AGOL). Within it I have a feature layer (point) called "tree_assets" and a related table called "inspections". Each time an inspection is created or edited, I want to update the tree_assets feature layer attribute "latestInspection" with the edit date of the latest inspection record. This way, I can symbolise the parent layer (tree_assets) to have green dots (inspected in the last week) and red dots (inspected prior to 1 week ago) on the map.

I want this to be dynamic i.e. something that happens automatically. Not a python script, not an FME script, not a MAKE scenario. This functionality is synonymous with a database trigger but I don't see this functionality available in a hosted feature service). It has to work offline too.

Any suggestions would be great.

[JoshuaSharp-Heward · Accepted solution]

Unfortunately I don't believe that's possible with just an AGOL deployment. If you had enterprise you would be able to do this via Attribute Rules, but in AGOL alone generally you'd either have to 1) calculate the value via python/FME/webhooks as you mentioned or 2) use a hosted feature service view linking the tree with the most recent inspection - the catch being that views created in these ways cannot be used offline.

[alex_mapintel]

For those how want to read a good article of using webhooks and FME to accomplish a similar task then follow the link below 🙂

https://community.safe.com/s/article/Automating-Workflows-from-ArcGIS-Field-Maps

[MattyMaps]

Have you tested this enterprise fgdb attribute rules workaround? What are the steps? Is there a document that outlines how to pass the inspections date from a related table to the parent layer via attribute rule?

[alex_mapintel]

Hey @MattyMaps , The following "idea" may be something that answers your questions:

Run Calculate Attribute Rule when adding a relationship to a feature 

[ChristopherCounsell · Accepted solution]

For the benefit of all readers of this post, there are 4 options:

• Don't update the point/parent table. Access the related records through Arcade in pop-ups, dashboards, reporting tools etc. You can't symbolize by related table.
• Update the related table, after the survey123/field map collection. This can be done through Notebooks (hosted), FME Server, manually in python/Pro,  or MAKE (though I recommend one of the earlier options to supplement as MAKE cannot rerun scenarios that 'failed successfully')
• Have the user update the parent table in Field Maps. Not ideal as it is two steps, but it is possible and potentially viable in some scenarios. e.g. one 'status' or date field for an inspection site

The fourth option is what you are looking for and really the only scenario within ArcGIS Online - use the Survey123 Inbox:

1. Publish a hosted feature layer with a point and related table
2. Set up a view with sync enabled that allows editing updates only (no adds or deletes)
3. Target the view with Survey123 Connect. Enable the Inbox. Disable sent folder and new surveys to be collected. Set a repeat count of 1 and hidden calculations + calculationmodes to update the parent table based on the repeat data being collected
4. Use the Survey123 Inbox to open and submit survey responses. It will add a repeat value always, and update the parent records;
5. Or use Field Maps to open Survey123 Inbox by passing the globalid with the inbox editing parameters, and callbacks

You can combine this with a lot of different things, like status filters on views, or the powerful calculations on Survey123. Limitations/downsides are:

• It is an advanced workflow with Survey123 
• Users have to download the survey values to their inbox
• Photos cannot be viewed offline (can be added and will be visible if device is online)
• Two bits of software for fieldworks to work with (mostly for downloading inbox, survey forms and map areas. Otherwise not too many issues)

Which option is best really depends on your workflow, resources and where the pain points are. I use option four regularly but for some projects I would not, instead preferring the advantages of the other options. Particularly the robustness of just adding to the related table and running FME post. 
I would consider the field maps > Field maps or survey123 add to related table > script/tool to update post to be the best solution. It's add only, works offline, less moving gears for the data collection part. But you need to be careful with automated tools, their triggers, and changes to the data...

[alex_mapintel]

Thanks for the input and suggestions.

Updating a parent or related record is common practice in RDMS and I see this as a major limitation with hosted feature services in AGOL. AGOL has a postgres database behind it (that supports triggers) and sqlite database on IOS field maps (that also supports triggers). I wouldn't think its too much of a stretch to add this functionality into the core product. Possibly something I'll add to the "ideas" page.

Otherwise, we will all be forever creating FME Workbenches on FME Server (if you have access to it), MAKE scenarios (where we need to build in if/else/error catching) if you want to pay for it, python code (if you know how to write and maintain it etc etc etc for such a fundamental database concept "TRIGGERS".

The ideal world would be to add a trigger on a feature layer that says "Hey, When I get an update, delete or create, then go and grab that data and put that data on some other feature layer". I could think of a million things that I would do with triggers on hosted feature services that I am now writing FME script for and python code for. 

[ChristopherCounsell]

Thanks.

It's a bit of a discussion on user vs system managed data.

The hosted layers, portal and ArcGIS Online are to put GIS in the hands of
non-spatial users.

ArcGIS Server/ Enterprise are there for rdbms based workflows.

Enterprise admins can have a hard time getting both the hosted and
referenced publishers in line with best practices. The thought of
unleashing the more powerful capabilities to both gives me a headache. I
imagine the ArcGIS Online developers have a similar concern with
maintaining data store performance.

ArcGIS Online offers REST API for more advanced workflows. And survey123
meets the advanced data collection workflow.
And they're bringing new functionality in Arcade, attribute rules, feature
webhooks, reporting, forms etc.

The best idea for this would be in Field Maps forms, to allow a single form
to update a point and add a related record, similarly to how the survey123
inbox works. I believe this idea exists already but can't find it easily on
mobile.
It shifts the entire backend issue into a relatively simpler applyEdit
request construction (I imagine, sorry Field Maps Dev team)

[alex_mapintel]

I 100 % agree with you @ChristopherCounsell about getting this into the Field Maps Form. Great spot to have this functionality.

Although I'd say that this isn't unleashing more power to the ArcGIS Online user as this concept is almost already there. A user today can navigate to the hosted feature service, click the data tab, click the attribute field of a parent table (say the LatestInspection field), click "calculate" and then write an arcade expression to update this value to the latest related inspection record. All of this is available and working and accessible with no MAKE, enterprise sde, FME etc

To accomplish this task, I wouldn't expect one someone would need to implement an entire enterprise environment or automated nightly scripting or webhooks from survey123.

I'm thinking that a local bushcare group should be able to collect tree data in a simple AGOL environment, 1 feature service with 1 point layer and a related table. When they add an inspection record to the related table, then the point layer gets the Latest Inspection Record. They just set this up on the hosted feature service (or like you mention in a form). IMO, this isn't complicated data management concepts or high compute load that requires a mindset of a RDMS on an enterprise setup.

[ChristopherCounsell]

Agree. My comments are more to say I imagine there's some challenges for Esri to retain the friendly user experience for non-spatial users in balance with those more comfortable working with database concepts. My thoughts are it's easier for the users to understand fieldworker interaction - form hits layer and related table - than comprehend the implications of database triggers. The calculate tools, REST API, FME etc are middle-ground workarounds that negate the outright need for an enterprise deployment.

What we should be doing is ensuring ArcGIS Ideas and Enhancement requests are created for all of these things... I'll loop back with some later