Auto-Updating features

[BrianBulla]

Hi,

What I need to do is figure out how to update a field in any feature class each time an edit is made to it.  This is to support our asset management system, Maximo.

The field is called MXCREATIONSTATE and will exist on many different feature classes in our corporate SDE.  Some users use ArcMap, some use ArcGIS Pro, some use FME to make updates, some use web editing tools.  Going forward other front-end apps could also make edits.

What is the best way to handle this sort of situation?  The out-of-the-box Attribute Rules/Attribute Assistant don't really seem to cover all the bases here.

Basically, anytime an edit is made (or new feature is created) MXCREATIONSTATE must be updated to 1.

Any ideas are appreciated.

Thanks,

[ABishop]

Hello Brian,

What version of ArcGIS Desktop are you using?  ArcMap? ArcGIS Pro?

[jcarlson]

This is one of the "greatest hits" here on the Community. Unfortunately, there's nothing "off the shelf" in the Esri-verse that accomplishes what you're looking to do, if Attribute Rules doesn't quite fit the bill. Why exactly doesn't an Attribute Rule work here, though?

Alternatively, you may be able to use a SQL trigger in your SDE, but that's a bit out of my wheelhouse.

And also, you could just create a bare-bones Python script to run a field calculation at regular intervals to get as close to "real-time" updates as possible.

[JohannesLindner]

Why exactly doesn't an Attribute Rule work here, though?

Brian first asked this question in the ArcGIS Pro SDK community, where I told him that. My reason for that was that some of the users work in ArcMap, which doesn't like Attribute Rules. I don't know about FME, webservices and "other front-end apps" probably depends.

If there was a way to get Attribute Rules to work reliably in all those systems, they would of cource be the goto choice for something this simple.

// Calculation Attribute Rule
// field: MXCREATIONSTATE 
// triggers: insert, update
if($editcontext.editType == "INSERT") {
    return 1
}
if($feature.MXCREATIONSTATE != $originalfeature.MXCREATIONSTATE) {
    return $feature.MXCREATIONSTATE
}
return 1

[BrianBulla]

Hi Johannes,

Because as you seem to know already, not all systems work with Attribute Rules.  Even with between ArcMap and ArcGIS Pro, I would need implement two different systems to accomplish the same thing.  Then add in all the ArcGIS Online products.  If I use QuickCollect to capture features then I also have to accommodate for this, as well any other web-editing product.  It would be a perpetual cycle of constantly having to make adjustments to any bit of code/app anyone creates going forward in order to accomodate for this MXCREATIONSTATE field.

IMO the best solution is something that works on the back-end and is invisible to the front-end users/developers.

SQL Triggers might just be it, but that is up to the DBAs to decide.

[ABishop]

Hello Brian,

I was thinking maybe a relationship class?

See info here: https://pro.arcgis.com/en/pro-app/2.8/tool-reference/data-management/create-relationship-class.htm 

[BrianBulla]

Hi Amanda,

I don't quite see how a relationship class would help.  Can you explain??

[ABishop]

Good Morning Brian,

The idea is to take advantage of the parent/child relationship that is possible with a relationship class. Depending on the cardinality of the relationship, you may have to assess a different approach, but the relationship class description for the composite relationship seems appropriate.

• A composite relationship is one in which the lifetime of one object controls the lifetime of its related objects. For example, power poles support transformers, and transformers are mounted on poles. Once a pole is deleted, a delete message is propagated to its related transformers, which are deleted from the transformers' feature class. Composite relationships are always one-to-many.

[BrianBulla]

Thanks Amanda.  I'll keep that in mind.

So far I've been messing with the Attribute Rules/Assistant as well as creating custom Add-Ins in Visual Studio to listen for editing events.  They both work OK, but are not 100% fool-proof.  There's always certain situations where they don't work.

I'm hoping our DBA's can sort out something on the back-end.

Thanks for your input.

[jcarlson]

Any thoughts as to the Python-based approach?

If your service has editor tracking enabled, and thus a last_edited_date field, this kind of thing would be incredibly simple. You'd need to keep track of when the script was last successfully run, perhaps with a config file. I have a script that does this sort of thing, though it only runs daily. Still, the whole thing only takes a couple seconds to run, so this could be firing many times a minute with room to spare.

from arcgis import GIS
from configparser import ConfigParser
from datetime import datetime

# portal connection
gis = GIS('your portal url', 'user', 'pass')

# get layer
fl = gis.content.get('itemid of service').layers[0] # or whatever layer index applies

# read config file
config = ConfigParser()
config.read('config.ini')

# get last run timestamp from config
last_run = config.get('last_run', 'time')

# calculate field, using timestamp to selectively identify new adds/edits
fl.calculate(
    where = f"last_edited_date >= timestamp '{last_run}'",
    calc_expression = {'field': 'MXCREATIONSTATE', 'value': 1}
)

# set last run timestamp to current date
config.set('last_run', 'time', datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

with open('config.ini', 'w') as c:
    config.write(c)

 Note that the method you use to query a layer based on a timestamp may differ depending on your DB. We're on postgreSQL, so timestamp 'YYYY-MM-DD HH:MM:SS' is what we use.