Delete record in related table when parent feature is deleted

[BrandonBoisvert]

I have recently set up a system for tracking our sign inventory in town. I have a sign post hosted feature layer with 2 related tables. One table is to track the signs on the sign post in town and the other is to track inspections/repairs done at each sign post.

My question is: Is there a way where if a sign post is removed, the signs associated with that feature are automatically deleted? 

I would only want this to occur in the sign table, as inspection and repair records are still useful to keep even if the feature no longer exist though I wont know what feature they were for but just that they occurred. When I look at the sign table I can see that for child records with a deleted parent record the GUID is empty. There is also a locked field called signpost that shows (0) compared to the signs with a parent feature still existing represented by (1). 

My ideas on possible solutions:

1st: I could possibly create a field in the sign post layer called 'discontinued' and filter out those records that have a 'Yes' value from my maps. Then I could maintain the relationship between the feature and records, however I would need to filter out the records related to a discontinued post when reporting or displaying with a dashboard the number of signs in town. 

2nd: I could have the field worker delete the sign post and probably use the empty GUID field in the sign table remove those records with a script but I am not familiar with the process of scripting for data in a related table hosted on arcgis online...

3rd: The field worker could delete the related signs prior to deleting the sign post...which is fine until they forget to do it

The primary purpose of tracking all of this is to know where the signs post are, what number signs we have in town, and which signs need to be inspected or repaired/replaced. I have gone a bit beyond that with also trying to track what work was done to the signs as well. The complete removal of a sign post is rare but I'm just trying to plan ahead so I can keep the sign counts accurate.

Anyway I am still new to this world of relational data so any thoughts are appreciated!

[jcarlson · Accepted solution]

What you're describing is a composite relationship. It's something you'd need to set up in the dataset before publishing. It's a setting on the relationship class, so a composite post/sign relationship would remove the signs with the post, but non-composite relationships between the posts and the inspections and repairs would retain the inspection and repair data.

Is it feasible for you to republish the service with the modified relationship classes?

[BrandonBoisvert]

Josh,

This is exactly what I need. I am still in the testing phase so I will republish with the appropriate relationship classes and see how that goes. I really appreciate your feedback and I will let you know how I make out. 

Best,

Brandon

[BrandonBoisvert]

Josh,

I just wanted to thank you for helping me! I republished with the modified relationship class and it solved my problem.

Best,

Brandon

[JeremyLw]

Another option if you don't want to setup a composite relationship and re-publish is to delete the orphan table records using ArcMap. Make sure you add the Feature Layer from the Catalog > My Hosted Services (assuming it's on ArcGIS Online) so it brings in the layer and its' related table. Adding the layer from the "Add Data" button does not bring in the related table. Use this edit method and delete the orphan records from the table, then sync edits with the service. I am sure you can do the same in Pro.

Jeremy L

[BrandonBoisvert]

Fortunately I was early enough in the design stage to create a composite relationship but I appreciate the alternative approach - especially for those further down the rabbit hole!