topic Re: Update Parent Table when Related Record is Created in ArcGIS Field Maps Questions

Update Parent Table when Related Record is Created

alex_mapintel — Tue, 06 Jun 2023 05:42:52 GMT

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.

Re: Update Parent Table when Related Record is Created

JoshuaSharp-Heward — Tue, 06 Jun 2023 07:59:59 GMT

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.

Re: Update Parent Table when Related Record is Created

ChristopherCounsell — Tue, 06 Jun 2023 11:26:21 GMT

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:

Publish a hosted feature layer with a point and related table
Set up a view with sync enabled that allows editing updates only (no adds or deletes)
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
Use the Survey123 Inbox to open and submit survey responses. It will add a repeat value always, and update the parent records;
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...

Re: Update Parent Table when Related Record is Created

alex_mapintel — Tue, 06 Jun 2023 11:51:19 GMT

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.

Re: Update Parent Table when Related Record is Created

ChristopherCounsell — Tue, 06 Jun 2023 12:15:27 GMT

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)

Re: Update Parent Table when Related Record is Created

MitchellGrafstein — Tue, 06 Jun 2023 15:09:22 GMT

For offline use, since the underlying mobile geodatabases are SQLite databases, this functionality is inherently "available." That said, how this would be implemented would be opening a giant can of worms for the developers. Somehow being able to add database triggers to the mobile geodatabases would be an answer. We mostly function offline, so I have not investigated what is "under the hood" for online maps in use - but if it is different, then the rules/triggers would also need to be applied at the service level when an add to a related table is submitted - and updates would also need to fire the correct trigger. Regardless, the interface to add these triggers would have to be at the service level.

Currently, we allow our users to update the parent feature and then create the related table records overnight using the Python API. This gives the user the visual effect they are looking for, and the historical information is created overnight. The biggest limitation here is that you are limited to one update between script runs and you need to track how to find these updates via date fields or through other calculated fields in the form(s.) Users editing multiple features, since this does not enforce the forms or calculated values, is another obstacle if they are allowed to edit multiple features.

Another option using dynamic joins, which I have yet to test, was also passed along to me:

https://community.esri.com/ccqpr47374/attachments/ccqpr47374/arcgis-online-questions/1973/1/Dynamic ...

I hope this helps. (Sorry if you saw this twice - I wanted to add it under a different account.)

Re: Update Parent Table when Related Record is Created

alex_mapintel — Wed, 07 Jun 2023 00:38:02 GMT

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.

Re: Update Parent Table when Related Record is Created

alex_mapintel — Wed, 07 Jun 2023 00:51:15 GMT

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

Re: Update Parent Table when Related Record is Created

ChristopherCounsell — Wed, 07 Jun 2023 23:02:24 GMT

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

Re: Update Parent Table when Related Record is Created

MattyMaps — Thu, 08 Jun 2023 17:31:42 GMT

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?

Re: Update Parent Table when Related Record is Created

alex_mapintel — Fri, 09 Jun 2023 00:31:19 GMT

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

Run Calculate Attribute Rule when adding a relationship to a feature

Re: Update Parent Table when Related Record is Created

alex_mapintel — Fri, 09 Jun 2023 00:58:58 GMT

I've created an Idea on the Ideas forum so if this is something you think will be handy then head over and give it a kudos. And also comment if you think it should be refined or updated. Feedback and discussion welcome @ChristopherCounsell, @JoshuaSharp-Heward, @MitchellGrafstein. Its my first contribution to the ideas forum so hopefully I haven't butchered it 🙂

Run Calculate Field or a TRIGGER when performing an INSERT, UPDATE or DELETE on hosted feature services

Re: Update Parent Table when Related Record is Created

ChristopherCounsell — Fri, 09 Jun 2023 01:17:54 GMT

https://community.esri.com/t5/community-help-documents/arcgis-ideas-submission-guidelines-and-statuses/ta-p/904874

For Ideas, I would encourage:

Be clear on what you want to happen and why, in workflow terms.
Add business/workflow context and value. This is often missed but super important. They need to know why you want the functionality
If there is business value (not just a cool feature) submit an enhancement request to Support. Provide the link to the idea so that it is connected to the request in Esri's system. Put the ENH number back on the idea, so people can also request it.
Create additional ideas if they can be delineated between the products. For example, attribute rules / database triggers working at ArcGIS Online service level are different to ArcGIS Field Maps form submission achieving the same thing. I think you could post it there from a Forms perspective (user edit vs backend update).

Re: Update Parent Table when Related Record is Created

ZachWasp — Fri, 15 Dec 2023 20:24:40 GMT

Hi,

I tried following your steps for "Option 4" which is the exact workflow I need, but I ran into an issue where it is working for me as owner of the data, but when I share the survey to another user they get an error where they were unable to submit the survey. I am unsure if I missed something from your instructions or maybe I forgot to change a particular setting, but I can't figure out why it works for me, but not my other user.

For reference, the exact setup I have is as follows:

- Existing point layer with last update field for symbolization
- Related 1:M table for recurring task with also has a date field.
- Created view layer with parent point layer & related table, set to editing updates only and sync enabled.
- Created survey in 123connect from view layer. Enabled Inbox. Allow inbox surveys to be edited. Disable sent folder.
- In the XLSform I hid all the fields in the parent layer except for the "Last updated" date field I want to use for symbolization in maps. Set repeat count to 1. Set the date field in the repeat to pull from the parent layer "last updated" field (I tried this the other way, but it wouldn't pull from the repeat up to the parent field when I tested it).
- Created dynamic link in Field Maps pop-up to load the survey from the GlobalID of the selected point per this guide: https://community.esri.com/t5/arcgis-survey123-questions/use-a-url-to-open-existing-record-to-edit-in-app/td-p/1320277

When I published and tested, I am able to load the survey from the URL parameters in the pop-up, it successfully updates the parent layer fields and also allows me to submit a new related record (without being able to see or edit the existing related records). I don't know why it's not working for another user.

Any insight is greatly appreciated!

Thanks,
Zach

Re: Update Parent Table when Related Record is Created

DevakalaiselvanS — Thu, 15 Feb 2024 04:46:28 GMT

it is possible automatically updated field values from parent table to relate table give me some ideas sir . if i updated an attribute value in one field its automatically updated in the relate table parent table and relate table having common field

Re: Update Parent Table when Related Record is Created

Gene_Sipes — Wed, 03 Apr 2024 20:25:22 GMT

I'm trying to achieve something similar, i.e. update a parent FC with data from a related record. I found this Github page for attribute rules and got really excited that I could set this up in my enterprise database, but I have not had any luck recreating it in my FGDB environment. Funny things is, the repository also provides a FGDB demo where this script is configured, and it works. When I try and set up a my own feature class and relate table to do the same it just doesn't work. Been so frustrating.

https://github.com/Esri/arcade-expressions/blob/master/attribute_rule_calculation/UpdateParentFeature.md

Can anyone tell me if this is still relevant (was 5 years old)?

Re: Update Parent Table when Related Record is Created

ChristopherCounsell — Wed, 03 Apr 2024 21:02:34 GMT

I'm not sure if this is relevant as this post is talking about it working
in ArcGIS Online.

What you've shared may work in a fgdb or a feature service using an
enterprise geodatabase, but won't work as a hosted feature layer in ArcGIS
Online at this time.

If you're not working in ArcGIS Online I'd suggest making a new post to get
more traction.