ArcGIS Dashboard Data Expression, "unable to execute Arcade script"

erica_poisson · ‎04-28-2021

Hi,

I am very new to Arcade (which you probably be able to tell by my filter expressions below).

I am attempting to create a Data Expression that can be used to power an Indicator widget in a Dashboard. I have built and tested this in the Data Expression builder within my Dashboard and when I click "test" it returns the proper result. I click done, but then see a little triangle with an ! telling me "Unable to execute Arcade script".

Why would I see this message? My Arcade script is below. Any help would be appreciated.

var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xxx';
var layerID_CMpts = 0;

var fs1 = FeatureSetByPortalItem(Portal(p), itemID_CMpts, layerID_CMpts, ['Point_Status', 'EQ_File_Number'], false);

var filterCMpts = Filter(fs1, "Point_Status ='Unstable' AND EQ_File_Number ='EQ2015-078' OR Point_Status ='Unstable' AND  EQ_File_Number = 'Eq2013-026' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2018-082' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2018-052' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2001-080X' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2003-021' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ1989-015' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2002-023' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2002-045X' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2019-110' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2020-017' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2019-091' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2018-029' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2020-033' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2019-036' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2020-052' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2019-064' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2017-040' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2020-031' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2021-003' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2011-016' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2017-026' OR Point_Status ='Unstable' AND  EQ_File_Number = 'EQ2021-020'");

var itemID_join = 'xxx';
var layerID_join = 0;

var fs2 = FeatureSetByPortalItem(Portal(p), itemID_join, layerID_join, ['Point_Status_revisit', 'EQ_File_Number'], false);

var filterJoin = Filter(fs2, "Point_Status_revisit ='Stable' AND EQ_File_Number ='EQ2015-078' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'Eq2013-026' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2018-082' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2018-052' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2001-080X' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2003-021' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ1989-015' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2002-023' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2002-045X' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2019-110' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2020-017' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2019-091' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2018-029' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2020-033' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2019-036' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2020-052' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2019-064' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2017-040' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2020-031' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2021-003' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2011-016' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2017-026' OR Point_Status_revisit ='Stable' AND  EQ_File_Number = 'EQ2021-020'");

var CntCMpts = Count(filterCMpts)
var CntJoin = Count(filterJoin)

return CntCMpts - CntJoin

Thank you!

Erica

erica_poisson · ‎05-07-2021

So with this example, I see (what looks like) the same result in the console, minus spatial reference info:

Use Console Function to output messages.

{"fields":[{"name":"EQNum","type":"esriFieldTypeString"},{"name":"InspecDate","type":"esriFieldTypeDate"},{"name":"PtStatus","type":"esriFieldTypeString"}],"geometryType":"esriGeometryPoint","features":[{"geometry":{"x":-7997522.018099999,"y":5207172.07},"attributes":{"EQNum":"EQ2013-026","InspecDate":"2020-07-29T16:00:00.000Z","PtStatus":"Unstable"}},{"geometry":{"x":-7997970.5297,"y":5214400.591300003},"attributes":{"EQNum":"EQ1989-015","InspecDate":"2020-09-30T16:00:00.000Z","PtStatus":"Unstable"}},{"geometry":{"x":-7993994.632999999,"y":5212258.8379999995},"attributes":{"EQNum":"EQ2020-052","InspecDate":"2020-10-19T16:00:00.000Z","PtStatus":"Unstable"}},{"geometry":{"x":-8002495.5656,"y":5219019.943500005},"attributes":{"EQNum":"EQ2017-040","InspecDate":"2020-11-23T17:00:00.000Z","PtStatus":"Unstable"}},{"geometry":{"x":-7998590.2183,"y":5226627.4831000045},"attributes":{"EQNum":"EQ2021-020","InspecDate":"2021-04-21T16:00:00.000Z","PtStatus":"Unstable"}}]}

Erica

XanderBakker · ‎05-07-2021

Hi @erica_poisson ,

I assume that despite the changes it is still not working right? I validated the json that is returned in json lint and it is valid. I have also stored the json in a file and used json 2 feature and it creates a valid featureclass with the features. The only thing that does not come through are the dates. Maybe it is expecting a value (epoch) rather than what it has at this moment. Could you do another test and change the output field type of the date to string. Just to see if that passes. If so we will focus on converting the date to epoch.

erica_poisson · ‎05-07-2021

@XanderBakker

That worked! I changed the esriFieldTypeDate to esriFieldTypeString, and it worked perfectly.

Thank you!

Erica

XanderBakker · ‎05-07-2021

Hi @erica_poisson , I assume that the date is passing as a string. I provided another above some code to pass the date as epoch which might work.

XanderBakker · ‎05-07-2021

Hi @erica_poisson ,

I have a better idea. Can you try this:

// settings
var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xxx';
var layerID_CMpts = 0;
var relationshipName = "";

// access first featureset
var fs1 = Filter(FeatureSetByPortalItem(Portal(p), itemID_CMpts, layerID_CMpts, ['Point_Status', 'EQ_File_Number', 'Inspection_Date'], true), "Point_Status ='Unstable' AND EQ_File_Number In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', 'EQ2018-052', 'EQ2001-080X', 'EQ2003-021', 'EQ1989-015', 'EQ2002-023', 'EQ2002-045X', 'EQ2019-110', 'EQ2020-017', 'EQ2019-091', 'EQ2018-029', 'EQ2020-033', 'EQ2019-036', 'EQ2020-052', 'EQ2019-064', 'EQ2017-040', 'EQ2020-031', 'EQ2021-003', 'EQ2011-016', 'EQ2017-026', 'EQ2021-020')");

// create data schema
var Dict = {
    'fields': [
        {'name': 'EQNum', 'type': 'esriFieldTypeString'},
        {'name': 'InspecDate', 'type': 'esriFieldTypeDate'},
        {'name': 'PtStatus', 'type': 'esriFieldTypeString'}],
'geometryType': 'esriGeometryPoint',
"spatialReference": {
  "wkid": 102100,
  "latestWkid": 3857
 },
'features':[]};

// analysis and fill Dict
var i = 0;
for (var f in fs1) {
    var fs2 = FeatureSetByRelationshipName(f, relationshipName, ['Point_Status_revisit'], false);
    var cnt = Count(fs2);
    if (cnt == 0) {
        Dict.features[i] = {
            'geometry': {
                'x': Geometry(f)["X"],
                'y': Geometry(f)["Y"]
            }, 
            'attributes': {
                'EQNum': f["EQ_File_Number"],
                'InspecDate': DateDiff(f["Inspection_Date"], Date(1970, 0, 1, 0, 0, 0), "milliseconds"),
                'PtStatus': f["Point_Status"]
            }
        };
        i++;
    }
}

Console(Text(Dict));

return FeatureSet(Text(Dict));

erica_poisson · ‎05-07-2021

This also works, and provides a nicely formatted time. Thank you for that. I was just digging into this blog to figure out how to format that.

Erica

Boke · ‎08-06-2021

Thank you @XanderBakker I was cracking my head for days. The DateDiff function solved it for me.

AnnaritaMacri · ‎07-15-2022

Hi,

I adopted this dashboard that has some arcade expressions written in it and I need to create an update to the script, but the same problem as this topic. I click "test" and it returns the proper result. I click done, but then see a little triangle with an! telling me "Unable to execute Arcade script". Although, I don't have a relationship table to this feature service.

var init_fs = FeatureSetByPortalItem(
    Portal('https://arcgis.com'),
    'XXXX',
    0, 
    ['Borough',
    'District',
    'label',
 //   'WorkComplete',
    'SLA',
  //  'Approval',
    'DateOfWork',
   // 'GrassActive',
   // 'InCompReason',
   // 'Hr48_flag'
   ],
    False);

var mostRecentVisit = Filter(
    GroupBy(init_fs,
    ['Borough',
    'District',
    'label',
    'SLA',
 //   'GrassActive',
 //   'Approval',
   // 'WorkComplete',
 //   'InCompReason',
 //   'Hr48_flag'
 ],
    {name: 'MaxDate', expression: 'DateOfWork', 'statistic': 'MAX'})
    ,"DateOfWork is not Null")
    ;
    
var novy = {
    'fields': [
        {'name': 'Borough',
        'type': 'esriFieldTypeString'},
        {'name':'District', 
        'type':'esriFieldTypeString'},
        {'name':'SiteName', 
        'type':'esriFieldTypeString'},
        {'name':'SLA', 
        'type':'esriFieldTypeInteger'},  
     //   {'name':'Approval', 
     //   'type':'esriFieldTypeString'},
        {'name':'DateOfWork', 
        'type':'esriFieldTypeDate'},
      // {'name': 'WorkComplete',
      //  'type': 'esriFieldTypeString'},
      //  {'name': 'InCompReason',
      //  'type': 'esriFieldTypeString'},
      //  {'name': 'Hr48_flag',
      //  'type': 'esriFieldTypeInteger'},
        {'name': 'met_SLA',
        'type': 'esriFieldTypeString'}
    ],
    'geometryType': '',
    'features': []
}

for (var v in mostRecentVisit) {
    
    var date_cutoff = When(v.SLA == 1, DateAdd(Today(),-7,'days'), 
        v.SLA == 2, DateAdd(Today(),-14,'days'), 
        v.SLA == 3, DateAdd(Today(),-21,'days'),
        DateAdd(Today(), -28, 'days'));

    //bizarre workaround so that date variable doesn't break the feature set: https://community.esri.com/t5/developers-questions/arcade-dictionary-to-featureset/m-p/1048379#M5824
    var start = ToLocal(Date(1970, 0, 01, 0, 0, 0, 0));
    //use the most recent date variable: MaxDate
    var dt = DateDiff(v.MaxDate, start, 'milliseconds');

// This was the old var -->   var met_SLA = IIF(dt > date_cutoff, True, False); below is the new var, and now i'm getting this issue

    var met_SLA = When(dt > date_cutoff,true, dt > date_cutoff && 
    "WorkComplete = 'yes'", True, False);

    var new_f = {'attributes':
        {'Borough': v.Borough,
        'District': v.District,
        'SiteName': v.label,
        'SLA' : v.SLA,
      //  'Approval': v.Approval,
        'DateOfWork': dt,
      // 'WorkComplete': v.WorkComplete,
       // 'InCompReason': v.InCompReason,
       // 'Hr48_flag': v.Hr48_flag,
        'met_SLA': met_SLA
        }
    }
    
    Push(novy.features, new_f)
}

//Filter by date threshold
return FeatureSet(Text(novy)) ;

Any suggestions?