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,

[BrianBulla]

Hi Josh,

I'm going to plead ignorance on this one.  I have no clue how web-editing works, so not really sure what to think.  I am just a front-end user and do all my work in ArcMap/ArcGIS Pro with some development work customizing the desktop with Add-Ins.  But I will take what you have suggested to our IT department who manage all of our servers/GIS and see what they think.

We don't really do a lot of web-editing right now, but have been experimenting with Geocortex GO.  Would what you are suggesting work with any web-editing app??

Thanks for your input!

[jcarlson]

Assuming your layer is a published service in your GIS portal, it just runs against the service directly, so apps and web editing don't really come into play here. Since the ArcGIS Python API only requires authentication with your portal, it can be run nearly anywhere, even on a headless Linux machine.

[Bud]

Ugh, Maximo Spatial. You poor soul.

We took a deep dive with our Maximo/GIS integrations a couple of years ago. We abandoned most of the IBM mechanisms, including the MXCREATIONSTATE field thing. 

I honestly don’t know where to start. There are so many things I want to warn you about.

@jcarlson is on the right track with the last_edited_date thing, as an alternative to MXCREATIONSTATE.
We did it by using database views on our feature classes to generate a sync_needed field, in conjunction with a database link to the Maximo database:

select
    ...,
    case  
        when fc.last_edited_date > maximo_asset.lastsyncdate or maximo_asset.lastsyncdate is null then 1
        else 0
    end as sync_needed
from
    gis_fc fc
left join
    custom_maximo_owner.asset@gistomax maximo_asset --this is a dblink in the Maximo database that lets GIS select from the Maximo asset table
    on fc.asset_id = maximo_asset.assetnum

--note: lastsyncdate is a custom field we created in the Maximo asset table. It's easy to populate that field with the Maximo system time using the &SYSDATE& default value: https://i.stack.imgur.com/2cNyk.png

One additional benefit to using a db view in GIS is that you can translate/format your data before it gets to Maximo. That's potentially easier than trying to do the translation in Maximo's JSON Mapping application.

[Bud]

Various other things to keep in mind:

1. Regarding Maximo Spatial's MXCREATIONSTATE / sync mechanism: Bear in mind that when Maximo syncs a record, it will then update the MXCREATIONSTATE field in GIS to "successfully synced" or whatever the actual numeric code is (I can't remember). The problem with that is: I believe the edit will trigger editor tracking, since the edit is done through the ArcGIS REST API. So, if you were using that editor tracking field for other stuff in GIS, then it means that Maximo has compromised your editor tracking info. Your editor tracking will indicate the last time Maximo edited the field, not necessarily the last time a user edited the field.
2. Maximo Spatial's cron task for syncing GIS records to Maximo sometimes fails. There are scenarios where it runs, but nothing happens -- no records are synced. So you don't want to use logic like the following: "If the last Maximo cron run date is less than the GIS last_edited_date, then sync the record". The last cron run date in Maximo will be misleading in that case...records likely failed to sync, and wouldn't be picked up in the next sync.
3. If I remember correctly, GIS's editor tracking fields need to be set to UTC time -- for ArcGIS-specific reasons that I can't remember off hand. However, the time zone in Maximo will likely be different. So comparing date fields between the two systems/time zones can get messy.
   With that said, we put some thought into the logic at play, and didn't think the time zone thing would end up being a problem for us -- for our weekly asset syncs.

[BrianBulla]

---

The problem with that is: that edit will trigger editor tracking, since the edit is done through the ArcGIS REST API.

---

Did your group look into SQL Triggers to help with this?  I'm not a DBA, but I would think if you could set up rules where the Triggers will not run when Maximo is doing it's reconciliation of records, and then enable them again once complete.

[BrianBulla]

Hi Bud,

Thanks for sharing.  Yes, I represent an end user group (public Works) where many of our assets we maintain through GIS need to be used within Maximo.  I do not think we are implementing the mapping functionality of Maximo, but we still need to maintain the Maximo field within our GIS.  The following fields are added to our data:  MXCREATIONSTATE, MXSITEID and MSASSETNUM.

From what I remember, we update MXCREATIONSTATE to "1" for each edit/new feature, and then once Maximo syncs itself up, Maximo sets them all back to "0".

With your system, how are you dealing with system deletes out of the GIS.  How is Maximo recognizing deleted items??

Thanks for reminding me about user tracking.  We do have Editor Tracking enabled right now, so something will need to be figured out for that.  I think that is why I originally started creating Visual Studio Add-Ins to listen for edit/new feature events, so I could auto populate the editor fields as well as the Maximo fields, but it's not fool-proof.

[Bud]

Regarding triggers: No, we didn't investigate them. We have 20-40 GIS layers that we integrate to Maximo. Triggers on 40 layers seemed like it'd be a lot to manage. The db view option seemed simpler for our purposes. And as analysts, we could create and manage the views ourselves, whereas we'd need to get other staff involved with triggers, which would have taken extra time.

The db views (and dblink) option I mentioned worked well for us. And like I said, we needed to translate the GIS data for Maximo anyway, so db views worked good for that too.

Edit: I forgot to mention that we take the db views and export them to feature classes on a schedule. Reason: static feature classes are faster than views.

Regarding deletes: If I remember correctly, once an asset is created (or integrated) in Maximo, Maximo won't allow us to delete it. We can only "decommission" it. So our hands were tied -- we had to come up with a "don't delete GIS assets, only flag them as decommissioned" business rule for our GIS technicians. We'll likely enforce that rule using ArcGIS Pro attribute rules. We've already done it for a couple of FCs. But it sounds like attribute rules aren't an option for you.
Is your GIS data versioned? If so, then it isn't possible to allow edits, and at the same time, specifically prevent deletes. At least not through the ArcMap/ArcGIS Pro privilege settings for a given feature class. 

With that said, I did come up with a proof-of-concept db trigger that seemed to work with versioned feature classes. But my testing was limited (I didn't end up needing the trigger; we'll probably use attribute rules instead). Prevent deletes in a versioned table at the database level 

[BrianBulla]

Thanks Bud,

This is giving me lots to think about, although being a front-end user I'm not sure how much say in any of this I really have.  I just know that we don't want to make huge changes to our current work processes in order to accommodate Maximo.

Regarding "Deletes", how does your GIS function if you don't remove features?  For example, if you do a road rehab contract and completely replace the storm sewer, what happens with the original features?

For us, we need to remove features immediately.  For instance, if a hydrant gets replaced, we delete it and add a new one.  Our GIS feeds into our inspection programs, asset management, mapping, web viewers, etc.  I can't have our depot guys going out looking to do inspections on hydrants that aren't really there.

It just seems that Maximo does not have a way to deal with the deleted GIS features, and I guess they just stay in Maximo forever??

[Bud]

Yeah, I think they just stay in Maximo forever.

We have status fields in our GIS layers. When a GIS asset gets removed due to a road reconstruction, etc, we just change the status to something like removed, decommissioned, abandoned, inactive, etc. 
We use that status field to hide the removed GIS assets from various inspection maps, web maps, etc…via a definition query…so that they appear to be deleted. But really they’re just flagged as removed and hidden from sight.
In our GIS db views for Maximo, we translate the GIS stratus to a Maximo status, such as Maximo’s decommissioned status. Alternatively, you can create a status “synonym” in Maximo.

[BrianBulla]

Hi Bud,

Have you looked into having Maximo look for deleted items from the GIS, and then Maximo updates it's database accordingly??  For example, a simple query such as this would allow Maximo to figure out what is missing (in this case for Hydrants), and then Maximo could do what it wants with the associated record.  In this table MAXIMO has all the hydrants, and table GIS Hydrants has all the hydrants, minus the deleted ones.

SELECT MAXIMO.ID
FROM MAXIMO LEFT JOIN [GIS Hydrants] ON MAXIMO.ID = [GIS Hydrants].ID
WHERE ((([GIS Hydrants].ID) Is Null));

One thing we don't want to do is change our workflows, just for Maximo.  And keeping removed items in the GIS will eventually cause problems.  People will forget to definition query them out on a map, you have to edit all of your webservices, any existing query that you use for reporting will need to be modified, etc.  It seems like a real pain when a simple query will tell Maximo what is missing.