1

I am creating an ESRI dashboard. I am pulling data in from a table in AGOL via the "New Data Expression" option. I need to pull in the data table, plus create a new field, DATE_FILTER (type = Date). I wrote the following script in Arcade to try and do that. The headers load but none of the data loads. Just calling the table itself works fine, so I know it's something with my script. Any idea what I did wrong?

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz

// Reference layer using the FeatureSetByPortalItem() method.
var portal = Portal('https://phl.maps.arcgis.com')
var fs = FeatureSetByPortalItem(
    portal,
    '4de29dcdbea84087842717a6143a68fd', 
    0,
    ['*'],
    false
);

// Create array for holding features, feat object for populating array
var features = [];
var feat;
          
// Populate feature array
for (var feature in fs) {
    feat = {
        'attributes': {
            'FILE_DATE': feature['FILE_DATE'],
            'LOAD_DATE': feature['LOAD_DATE'],
            'DATE_FILTER': Date(feature['CALENDAR_YEAR'], (feature['MONTH'] - 1), 01),
            'YEAR_MONTH': feature['YEAR_MONTH'],
            'MONTH': feature['MONTH'],
            'CALENDAR_YEAR': feature['CALENDAR_YEAR'],
            'FISCAL_YEAR': feature['FISCAL_YEAR'],
            'POSITION_TYPE': feature['POSITION_TYPE'],
            'SEX': feature['SEX'],
            'RACE_ETHNICITY': feature['RACE_ETHNICITY'],
            'SALARY_RANGE': feature['SALARY_RANGE'],
            'SALARY_SORT': feature['SALARY_SORT'],
            'HIRING_DEPARTMENT': feature['HIRING_DEPARTMENT'],
            'DEPARTMENT_NAME': feature['DEPARTMENT_NAME'],
            'TOTAL_COUNT': feature['TOTAL_COUNT'],
            'MAYOR_CABINET_COUNT': feature['MAYOR_CABINET_COUNT'],
            'SENIOR_LEADERSHIP_COUNT': feature['SENIOR_LEADERSHIP_COUNT']
        },
        
    };
    Push(features, feat);
}

var empDict = {
    'fields': [
        {'name': 'FILE_DATE', 'type': 'esriFieldTypeDate'},
        {'name': 'LOAD_DATE', 'type': 'esriFieldTypeDate' },
        {'name': 'DATE_FILTER', 'type': 'esriFieldTypeDate'},
        {'name': 'YEAR_MONTH', 'type': 'esriFieldTypeDouble'},
        {'name': 'MONTH', 'type': 'esriFieldTypeInteger'},
        {'name': 'CALENDAR_YEAR', 'type': 'esriFieldTypeInteger'},
        {'name': 'FISCAL_YEAR', 'type': 'esriFieldTypeInteger'},
        {'name': 'POSITION_TYPE', 'type': 'esriFieldTypeString'},
        {'name': 'SEX', 'type': 'esriFieldTypeString'},
        {'name': 'RACE_ETHNICITY', 'type': 'esriFieldTypeString'},
        {'name': 'SALARY_RANGE', 'type': 'esriFieldTypeString'},
        {'name': 'SALARY_SORT', 'type': 'esriFieldTypeInteger'},
        {'name': 'HIRING_DEPARTMENT', 'type': 'esriFieldTypeString'},
        {'name': 'DEPARTMENT_NAME', 'type': 'esriFieldTypeString'},
        {'name': 'TOTAL_COUNT', 'type': 'esriFieldTypeInteger'},
        {'name': 'MAYOR_CABINET_COUNT', 'type': 'esriFieldTypeInteger'},
        {'name': 'SENIOR_LEADERSHIP_COUNT', 'type': 'esriFieldTypeInteger'}
    ],
    'geometryType': '', 
    'features': features
};

return FeatureSet(Text(empDict)); 

1 Answer 1

2

I was looking for this answer myself and it's pretty straight forward, if a bit annoying that it isn't a built-in solution for Arcade.

ArcGIS Online stores dates in ISO 8601 format by default, but the esriFieldTypeDate is looking for a UNIX timestamp. To accomplish this, you subtract the end date (stored date) from 00:00:00 UTC on Thursday, 1 January 1970 (UNIX's 'origin time'). This is taken from Peter Klingman's answer on the esri community forums: https://community.esri.com/t5/developers-questions/arcade-dictionary-to-featureset/td-p/1047117

In your case, I believe this should accomplish what you're looking for:

for (var feature in fs) {
    var UNIX_Origin = ToLocal(Date(1970, 0, 01, 0, 0, 0, 0))
    feat = {
        'attributes': {
            'FILE_DATE': DateDiff(feature['FILE_DATE'], UNIX_Origin),
            'LOAD_DATE': DateDiff(feature['LOAD_DATE'], UNIX_Origin),
            'DATE_FILTER': DateDiff(Date(feature['CALENDAR_YEAR'], (feature['MONTH'] - 1), 01)), UNIX_Origin),
            'YEAR_MONTH': feature['YEAR_MONTH'],
            'MONTH': feature['MONTH'],
            'CALENDAR_YEAR': feature['CALENDAR_YEAR'],
            'FISCAL_YEAR': feature['FISCAL_YEAR'],
            'POSITION_TYPE': feature['POSITION_TYPE'],
            'SEX': feature['SEX'],
            'RACE_ETHNICITY': feature['RACE_ETHNICITY'],
            'SALARY_RANGE': feature['SALARY_RANGE'],
            'SALARY_SORT': feature['SALARY_SORT'],
            'HIRING_DEPARTMENT': feature['HIRING_DEPARTMENT'],
            'DEPARTMENT_NAME': feature['DEPARTMENT_NAME'],
            'TOTAL_COUNT': feature['TOTAL_COUNT'],
            'MAYOR_CABINET_COUNT': feature['MAYOR_CABINET_COUNT'],
            'SENIOR_LEADERSHIP_COUNT': feature['SENIOR_LEADERSHIP_COUNT']
        },
        
    };
    Push(features, feat);
}

The FILE_DATE and LOAD_DATE should populate fine in theory, but I'm not sure if DATE_FILTER will as well. You may have to do some playing around with the calculation, but if it gave an ISO 8601 date before, the conversion should work fine.

Hope this helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.