Arcade Data Expressions with Date Column Not Working

[AndrewRudin1]

I'm curious if anyone has had issues creating a data expression for Arcade in Dashboards that returns a date column?

I've noticed that if I include a date column in the FeatureSet I'm returning at the end of the script, the output contains no rows.  Based on my testing I believe this is a bug in the FeatureSet() function if the dictionary you pass to it contains a date column.  But since Arcade is pretty new to me maybe I'm doing something wrong

As an example, I put two samples below of the same code based on a public facing AGOL hosted feature layer.  The first sample has the date column removed so you can see it does return data.  The second sample the only difference is the date column is included, but you'll notice if you run it the results are blank.  I tried this with two AGOL feature layers, so it doesn't seem to be a fluke with just one layer.

I'm trying to follow along on the GitHub example on how to join tabular data to one of my layers and returns a feature set (Link).  Ultimately I want to slice and dice the layer in Arcade, but for this forum question I kept the script simple to show the issue.

Here's sample script 1 with the date column commented out.  If you run in the Arcade playground you will se it returns data.

var portal = Portal("https://www.arcgis.com/");

var features = [];
var feat;

//Define input layer to read
var fs = FeatureSetByPortalItem(
    portal,
    "a400f4711f9443a9855340ee7b66890a",
    0,
    ['DRAINAGE_ID','LOCATION','FME_DATE'],
    false
    );

//Loop over each  hosted layer feature
//, pass subset of attribute values to the feat variable
//, then Push the feat object into the feature dictionary
for (var f in fs) {
    feat = {
        attributes: {
            DRAINAGE_ID: f["DRAINAGE_ID"],
            LOCATION: f["LOCATION"],
            //FME_DATE: f["FME_DATE"],
        }
    }
    Push(features,feat)
}    

//Define schema for output dictionary
//and pass in the dictionary of output features
var joinedDict = {
    fields: [
        {name: "DRAINAGE_ID", type: "esriFieldTypeInteger"},
        {name: "LOCATION", type: "esriFieldTypeString"},
        //{name: "FME_DATE", type: "esriFieldTypeDate"}
        ],
        'geometryType': '',
        'features':features
};

return FeatureSet(Text(joinedDict));

Here is sample 2 that includes the date column, but when you run it the results will be blank

var portal = Portal("https://www.arcgis.com/");

var features = [];
var feat;

//Define input layer to read
var fs = FeatureSetByPortalItem(
    portal,
    "a400f4711f9443a9855340ee7b66890a",
    0,
    ['DRAINAGE_ID','LOCATION','FME_DATE'],
    false
    );

//Loop over each  hosted layer feature
//, pass subset of attribute values to the feat variable
//, then Push the feat object into the feature dictionary
for (var f in fs) {
    feat = {
        attributes: {
            DRAINAGE_ID: f["DRAINAGE_ID"],
            LOCATION: f["LOCATION"],
            FME_DATE: f["FME_DATE"],
        }
    }
    Push(features,feat)
}    

//Define schema for output dictionary
//and pass in the dictionary of output features
var joinedDict = {
    fields: [
        {name: "DRAINAGE_ID", type: "esriFieldTypeInteger"},
        {name: "LOCATION", type: "esriFieldTypeString"},
        {name: "FME_DATE", type: "esriFieldTypeDate"}
        ],
        'geometryType': '',
        'features':features
};

return FeatureSet(Text(joinedDict));
//If you instead return just the Text(joinedDict) you will see that the data looks valid
//, so it's just way the FeatureSet() function is transforming the data that seems to cause blank results
//return Text(joinedDict)

[jcarlson · Accepted solution]

In Data Expressions, date fields need to be wrapped in the Number function in order to work properly. Try this:

...
    FME_DATE: Number(f["FME_DATE"]),
...

[JohannesLindner]

Related Idea trying to get this fixed: Arcade: Allow Date() values in date fields (esriFi... - Esri Community

[AndrewRudin1]

Thanks Johannes.  I upvoted that idea. Agreed that this could be improved to be more seamless.

[AndrewRudin1]

Thank you for the responses.  @jcarlson 's response was the ticket.  Wrapping the reference to the input FeatureSet's date column translated the date into a unix timestamp integer, which then outputed correctly on the return statement.

Something else I noted was that null dates in the source data will get converted to a zero when the number function is called. On the output FeatureSet these zero's will appear as Jan 1, 1970.  So to fix that I also had to wrap the number function in an IIF function, and then return null if the incoming number() value is zero.

So the column request looks like this:

//If incoming date value is zero then return null, else return the date value as a unix timstamp integer

MY_OUT_DATE_COLUMN: iif (number(f["MY_IN_DATE_COLUMN"]) == 0

                                                        ,null

                                                        ,number(f["MY_IN_DATE_COLUMN"])

                                                        )

[DavidNyenhuis1]

With this week's update to Arcade and ArcGIS Dashboards, date fields in feature set constructors now just work. You no longer have to wrap dates with Number() if you pass the dictionary into the FeatureSet() function (which as of this release accepts a dictionary as opposed to only text based JSON).

Instead of:

return FeatureSet(Text(dict))

Do this:

return FeatureSet(dict)

Learn more in this blog post. 

NOTE: For Enterprise users, this update is targeted for 11.2