Select to view content in your preferred language

Update Parent Table when Related Record is Created

3253
16
Jump to solution
06-05-2023 10:42 PM
alex_mapintel
New Contributor III

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.

16 Replies
ZachWasp
New Contributor II

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-i...

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

0 Kudos
DevakalaiselvanS
New Contributor II

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

0 Kudos
MitchellGrafstein
Occasional Contributor

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:

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

Mitchell Grafstein, Horticultural Inspector 1, NYS Dept. of Agriculture and Markets
alex_mapintel
New Contributor III

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 servi... 

ChristopherCounsell
MVP Regular Contributor

https://community.esri.com/t5/community-help-documents/arcgis-ideas-submission-guidelines-and-status...

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).

 

Gene_Sipes
Occasional Contributor

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/UpdateParentFeatur...

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

0 Kudos
ChristopherCounsell
MVP Regular Contributor
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.