nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

erica_poisson

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 = 'xyz';
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 = 'xyz';
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!

Encontre mais publicações marcadas com

ArcGIS Online

Accepted answers

XanderBakker

Hi @erica_poisson ,

That is interesting. It returns the correct value, but it does show an error. Maybe the name for the relationshipclass that should be used has to be "Construction_Monitoring_Revisit" (I am not sure). The best way to validate this is to add the layer to the map and use the navigation in the interface when creating an expression as described earlier. Just to be sure that you are using the correct name.

I am also wondering if it is enough to return the count as a number or that a featureset is expected. I think the latter. In that case, you could use something like the expression below.

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

var fs1 = Filter(FeatureSetByPortalItem(Portal(p), itemID_CMpts, layerID_CMpts, ['Point_Status', 'EQ_File_Number'], false), "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')");

var tot = 0;
for (var f in fs1) {
    var fs2 = FeatureSetByRelationshipName(f, relationshipName, ['Point_Status_revisit'], false);
    var cnt = Count(fs2);
    if (cnt == 0) {
        tot += 1;
    }
}

// return tot;

// create data schema
var Dict = {
    'fields': [
        {'name': 'CountNoAttach', 'type': 'esriFieldTypeInteger'}],
    'geometryType': '',   
    'features': []};

// fill the data schema with the data
Dict.features[0] = {
            'attributes': {
                'CountNoAttach': tot
            }}

// return the featureset
return FeatureSet(Text(Dict));

XanderBakker

Hi @erica_poisson and @KenBuja ,

You actually can return an in-memory featureset with the data that you want to graph. Since I am not sure what you are exactly after I will provide an example and you can adjust accordingly.

See the example below (please note that the filter expression is using the syntax suggested by Ken). The first part is pretty much the same, but after getting the counts, you create a schema for the output featureset, and fill it with the data. In this case, it will be a featureset with 3 fields and only 1 row. This can be used in the Dashboard as data source.

var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
var layerID_CMpts = 0;
var itemID_join = 'xyz';
var layerID_join = 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 In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', etc)" );

var fs2 = FeatureSetByPortalItem(Portal(p), itemID_join, layerID_join, ['Point_Status_revisit', 'EQ_File_Number'], false);
var filterJoin = Filter(fs2, "Point_Status ='Unstable' AND EQ_File_Number In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', etc)");

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

// create data schema
var Dict = {
    'fields': [
        {'name': 'CntCMpts', 'type': 'esriFieldTypeInteger'},
        {'name': 'CntJoin', 'type': 'esriFieldTypeInteger'},
        {'name': 'CntCMptsMinusCntJoin', 'type': 'esriFieldTypeInteger'}],
    'geometryType': '',   
    'features': []};

// fill the data schema with the data
Dict.features[0] = {
            'attributes': {
                'CntCMpts': CntCMpts,
                'CntJoin': CntJoin,
                'CntCMptsMinusCntJoin': CntCMpts - CntJoin
            }}

// return the featureset
return FeatureSet(Text(Dict));

All comments

ScottFortman1

Hi there,

this solution looks promising for an expression I am working on where I am trying to use a dashboard indicator to display the result of a calculation. I have the calculation working and followed the expression above to populate the dictionary but I am getting a blank dictionary. Any ideas as to why it won't return 1 row with my value?

var tvwdportal = Portal('https://mapping.tvwd.org/portal')
var flush2023 = "LifeCycleStatus = 'ACTIVE' And OwnedBy = 'TVWD' And Diameter <= 12 And LastFlushDate >= date '2023-10-26'"
var main2023 = Filter(FeatureSetByPortalItem(tvwdportal,"b24772e427084f21b1f208a685d07fcc",48,['LastFlushDate','Shape__Length'],False), flush2023) ;

//split date/time with groupby function
var datesplit = GroupBy(
  main2023,
  [
    {name: 'theYear', expression: 'EXTRACT(YEAR FROM LastFlushDate)'},
    {name: 'theMonth', expression: 'EXTRACT(MONTH FROM LastFlushDate)'},
    {name: 'theDate', expression: 'EXTRACT(DAY FROM LastFlushDate)'},
  ],
  [
    {name: 'flushcount', expression: '1', statistic: 'COUNT'},
    {name: 'length', expression: 'Shape__Length', statistic: 'SUM'}
  ]
)

// calculate days remaining
var daysremaining = Round(Sum(1975006.4354237458/Average(datesplit,'length')),2)



//create dictionary to store calulated days remaining
var dict = {
    'fields': 
    [{'name':'Days_Remain', 'type': 'esriFieldTypeInteger'}],
    'geometryType': '',   
    'features': []}; 
    
//populate dictionary    
Dict.features[0] = {
            'attributes': {
                'Days_Remain': daysremaining
            }}
  
   
return FeatureSet(Text(Dict));

Thanks for any help!

AnnaritaMacri

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'),
    'xyz',
    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?

Boke

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

erica_poisson

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.

XanderBakker

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

Hi @erica_poisson ,

I have a better idea. Can you try this:

// settings
var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
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

@XanderBakker

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

Thank you!

XanderBakker

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

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"}}]}

XanderBakker

Hi @erica_poisson ,

In the returned json I notice that the geometry also contains the spatial reference for each feature. I am not sure if this may be causing any conflict.

			"spatialReference": {
				"latestWkid": 3857,
				"wkid": 102100
			}

We can change the expression and exclude that part and see what happens. I must admit that in the past I have had situations where a correctly formed JSON is created and an empty feature set is returned. Not sure if we are running into a restriction here.

One thing we can try is this:

// settings
var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
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',
'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': f["Inspection_Date"],
                'PtStatus': f["Point_Status"]
            }
        };
        i++;
    }
}

Console(Text(Dict));

return FeatureSet(Text(Dict));

Please share back what is returned in the console.

erica_poisson

@XanderBakker

I have tried figuring this out by browsing through GeoNet but can't find anything relating to this issue...I added the console function right before the return statement at the end, because this is still returning an empty dictionary. When I hit "Test" and view the output from the console, I see this:

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

The console returns the 5 results I am looking for/know should be returned. I was unsure if this was happening because of the included geometry, so I removed that and tested again with the same result (console showing 5 features, but empty dictionary.

XanderBakker

Hi @erica_poisson ,

The structure is a bit different. The schema is created once before the loop and you need to insert the results in the feature. When you need to include the geometry this is something you also need to include in the dictionary. I have not worked with returning geometries in data expression, but if I consult the syntax of the json it should be something like this:

// settings
var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
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',
'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': Geometry(f),
            'attributes': {
                'EQNum': f["EQ_File_Number"],
                'InspecDate': f["Inspection_Date"],
                'PtStatus': f["Point_Status"]
            }
        };
        i++;
    }
}

return FeatureSet(Text(Dict));

XanderBakker

Hi @erica_poisson ,

I am also wondering if it is enough to return the count as a number or that a featureset is expected. I think the latter. In that case, you could use something like the expression below.

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

var fs1 = Filter(FeatureSetByPortalItem(Portal(p), itemID_CMpts, layerID_CMpts, ['Point_Status', 'EQ_File_Number'], false), "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')");

var tot = 0;
for (var f in fs1) {
    var fs2 = FeatureSetByRelationshipName(f, relationshipName, ['Point_Status_revisit'], false);
    var cnt = Count(fs2);
    if (cnt == 0) {
        tot += 1;
    }
}

// return tot;

// create data schema
var Dict = {
    'fields': [
        {'name': 'CountNoAttach', 'type': 'esriFieldTypeInteger'}],
    'geometryType': '',   
    'features': []};

// fill the data schema with the data
Dict.features[0] = {
            'attributes': {
                'CountNoAttach': tot
            }}

// return the featureset
return FeatureSet(Text(Dict));

erica_poisson

@XanderBakker

Ah I see. I am perplexed by my data. This was created via Pro with a named relationship class based on GlobalID/GUID, however this is what I see via the REST endpoint. I would have expected to see the relationship class name that I defined in Pro before I published these data to ArcGIS Online.

Out of curiosity, I added this layer to a new map and went into the pop-up to add a new expression. I clicked through $feature like you mentioned, found related records at the bottom and then clicked FeatureSetByRelationshipName to add it into the builder window. This is what I see.

FeatureSetByRelationshipName($feature,"")

Here is the Arcade I attempted...I am now getting the "Unable to execute Arcade script" message again, even though it returns the correct number of records when I click "Test":

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

var fs1 = Filter(FeatureSetByPortalItem(Portal(p), itemID_CMpts, layerID_CMpts, ['Point_Status', 'EQ_File_Number'], false), "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')");

var tot = 0;
for (var f in fs1) {
    var fs2 = FeatureSetByRelationshipName(f, relationshipName, ['Point_Status_revisit'], false);
    var cnt = Count(fs2);
    if (cnt == 0) {
        tot += 1;
    }
}

return tot;

XanderBakker

Hi @erica_poisson ,

Good question... Normally, when you edit an Arcade expression you will have access to the properties and the easiest way is navigating the interface to use the relationship. Let me visualize this.

In the expression editor you would find the $feature on the right and you can enter and find all the fields of this $feature (click on ">" in blue rectangle):

Next, at the end of the list of attributes you would find "Related records" and you can see all the relationships (click on ">" in blue rectangle)

This will show the relationship and when you click on the "FeatureSetByRelationshipName" link it will be correctly inserted in the code.

However, since you access the data in the data expression using the "FeatureSetByPortalItem" you will not find those options in the interface.

You can go to the details page of the hosted feature layer and open the REST end point:

In the REST endpoint page at the end, you will find the relationships section with the name of the relationship:

erica_poisson

I will give this a try tomorrow morning. One question...the relationship name that I put in there, is it the name of the relationship class in the fGDB from pre-publishing to AGOL?

XanderBakker

Hi @erica_poisson ,

I just had a look in the list of functions available when creating an Arcade data expression. For instance, the Attachments function is not available, but FeatureSetByRelationshipName is. I assume you published your data and related data with a relationship class. If this is not the case, you can still use the filter function to get the same result. The performance will depend greatly on the number of features you have since a loop through all features is required to get this result.

Based on the sample expression you shared, have a look at the expression below:

var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
var layerID_CMpts = 0;
var relationshipName = "the name of the relationship between data and related data";

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

var tot = 0;
for (var f in fs1) {
    var fs2 = FeatureSetByRelationshipName(f, relationshipName, ['DummyField'], false);
    var cnt = Count(fs2);
    if (cnt == 0) {
        tot += 1;
    }
}

return tot;

In this example for each feature the related features are queried and if the count is 0, the total is incremented by one. At the end you should have the number of features that have no related records.

erica_poisson

Hi Xander,

Thank you for the example. I was able to get this to work using something I came up with that basically does what you showed in the example. Unfortunately, this is not quite what I need after reflecting on the results.

What I really want to be able to do is get the count of records in my point layer that do not have any related records associated with them. Is that possible using the FeatureSetByRelationshipName parameter? I've seen some other posts you have responded to around FeatureSetByRelationshipName, but none of the examples talk about doing something like what I am trying to accomplish.

Thank you,

XanderBakker

Below an example of the featureset that will be returned using some random values for the counts:

XanderBakker

Hi @erica_poisson and @KenBuja ,

You actually can return an in-memory featureset with the data that you want to graph. Since I am not sure what you are exactly after I will provide an example and you can adjust accordingly.

var p = 'https://www.arcgis.com';
var itemID_CMpts = 'xyz';
var layerID_CMpts = 0;
var itemID_join = 'xyz';
var layerID_join = 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 In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', etc)" );

var fs2 = FeatureSetByPortalItem(Portal(p), itemID_join, layerID_join, ['Point_Status_revisit', 'EQ_File_Number'], false);
var filterJoin = Filter(fs2, "Point_Status ='Unstable' AND EQ_File_Number In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', etc)");

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

// create data schema
var Dict = {
    'fields': [
        {'name': 'CntCMpts', 'type': 'esriFieldTypeInteger'},
        {'name': 'CntJoin', 'type': 'esriFieldTypeInteger'},
        {'name': 'CntCMptsMinusCntJoin', 'type': 'esriFieldTypeInteger'}],
    'geometryType': '',   
    'features': []};

// fill the data schema with the data
Dict.features[0] = {
            'attributes': {
                'CntCMpts': CntCMpts,
                'CntJoin': CntJoin,
                'CntCMptsMinusCntJoin': CntCMpts - CntJoin
            }}

// return the featureset
return FeatureSet(Text(Dict));

kenbuja

Data expressions generate a FeatureSet, so that's all you can return, not the count of your two FeatureSets.

A note about your SQL statement. You can make it much shorter using this format

"Point_Status ='Unstable' AND EQ_File_Number In ('EQ2015-078', 'Eq2013-026', 'EQ2018-082', etc)"

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.