Select to view content in your preferred language

Immediate Calculation Attribute Rule Not being Triggered when table is updated on SQL Server and not in ArcPro

221
2
Jump to solution
09-18-2024 09:40 AM
GreenJosh
Emerging Contributor

Hello,

I have an attribute rule that should update 3 fields in the feature layer 'DayTotals_Dining'. A date Field, a Revenue Field, and a Traffic Field. These should be updated when new entries are inserted into the table 'DinningAll'. The Rule simply sums these values using GroupBy(), and returns an update array. What I've found is that if I manually make inserts to the 'DinningAll' table in ArcPro, the rule functions as intended. The problem is that the updates to the 'DinningAll' table are handled server side via an ETL by our IT department. These updates happen daily, and are reflected in the table when viewed in ArcPro, but for some reason the attribute rule is never triggered.
Has anyone else ever experienced this behavior?

Code Snippet Attached

var name = $feature.Name
var yster = Date(DateAdd(DateOnly(), -2, 'days'),Time("11:59 pm"))
var tdy = Date(DateOnly(), Time("12:00 am"))
var data = Count(Filter(FeatureSetbyName($datastore,"PRJ.DinningAll"), "Name = @name AND @tdy >Time AND Time > @yster"))
if(data > 0){
    var rev = GroupBy(Filter(FeatureSetbyName($datastore,"PRJ.DinningAll"),"Name = @name AND @tdy >Time AND Time > @yster"),'Name',[{name: "rev",expression: "Revenue",statistic:"SUM"}])
    var transa = GroupBy(Filter(FeatureSetbyName($datastore,"PRJ.DinningAll"), "Name = @name AND @tdy >Time AND Time > @yster"),'Name',[{name: "transa",expression: "Count",statistic:"SUM"}])
    var target = Filter(FeatureSetbyName($datastore,"PRJ.DayTotals_Dining"), "Name = @name")
    var targetftr = first(target)
    //console(targetftr)
    console(first(rev)["rev"])
    console(first(transa)["transa"])
    console(targetftr.GLOBALID)
    console(DateAdd(Date(), -1, 'days'))
    return{
        "edit":[{"classname":"PRJ.DayTotals_Dining",
        "updates":[{"GlobalID":targetftr.GLOBALID,"attributes":{
        "Revenue":Round(first(rev)["rev"]),"Count_":first(transa)["transa"],"Time":DateAdd(Date(), -1, 'days')}}]}]}}
0 Kudos
1 Solution

Accepted Solutions
RobertKrisher
Esri Regular Contributor

If the ETL is going directly against the database and not through the Esri software, then this is the expected behavior. In order for the rule to run, the edit must occur in a location where the Esri software can respond to the edit and run this code.

If you have web services published for these features you can have your IT department rewrite their ETL to use those web services. If you don't have web services or ArcGIS Enterprise available you could consider having your IT department use something like the ArcGIS Data Interoperability extension or Safe Software's FME to perform the ETL.

If none of those options work, you can consider having your IT department perform this calculation as part of their ETL or performing this calculation as a batch process that runs after the ETL. This batch process would likely be a python script that recalculates this value using a field calculate that has this Arcade script embedded in it.

View solution in original post

0 Kudos
2 Replies
RobertKrisher
Esri Regular Contributor

If the ETL is going directly against the database and not through the Esri software, then this is the expected behavior. In order for the rule to run, the edit must occur in a location where the Esri software can respond to the edit and run this code.

If you have web services published for these features you can have your IT department rewrite their ETL to use those web services. If you don't have web services or ArcGIS Enterprise available you could consider having your IT department use something like the ArcGIS Data Interoperability extension or Safe Software's FME to perform the ETL.

If none of those options work, you can consider having your IT department perform this calculation as part of their ETL or performing this calculation as a batch process that runs after the ETL. This batch process would likely be a python script that recalculates this value using a field calculate that has this Arcade script embedded in it.

0 Kudos
GreenJosh
Emerging Contributor

Thanks for the response, Robert!

Looks like this'll be a lift for our IT, we'll need to explore other options.

0 Kudos