SQL statement to compare dates within Field Maps Arcade expression

HollyTorpey_LSA

Hi all,

I am working on a form to add a record to a related table and I'm having trouble creating a SQL statement to use to filter the records in another layer to only those collected today. To do this, the statement needs to convert the datetime value in the layer's records to dateonly and then compare it today's date (also in dateonly form).

My expression works in the map viewer, but not in Field Maps. I've narrowed down the issue to the end of the SQL statement where it compares the date of the record to today's date. Does anyone know how to write this so that it works in Field Maps too?

// get parent feature
var sites = FeatureSetByRelationshipName($feature, "ToSiteBoundaries");

// get site name from parent feature
var siteName = "";
if (!isEmpty(first(sites))) {
  var site = first(sites)
  if (!isEmpty(site)) {
    siteName = site.label
  } else {
    SiteName = null
  }
}
// hard-coding the site name here for testing purposes:
sitename = 'Turtle Ridge'

// Get today's date and convert it to dateonly
var todaydate = dateOnly(today())

// Construct a SQL statement to use to filter the points layer, returning only trash points within this site that were resolved today and have a value in the MI_LbsRemoved field
var SQL = "(SiteName = @siteName) AND (MI_IssueType = 'Trash') AND (MI_LbsRemoved IS NOT NULL) AND (cast(ResolutionDate as date) = @todaydate)";

// Get all trash points resolved within the site today
var points = Filter(FeatureSetById($map, "1974cb0925b-layer-7", ['SiteName', 'ObservationDate', 'MI_IssueType', 'Resolved', 'ResolutionDate', 'MI_LbsRemoved'], false), SQL)

// Iterate through the featureset and add up the pounds of trash removed
if (!isEmpty(first(points))) {
  var lbsTrash = 0
  for (var p in points) {
    if (!IsEmpty(p)) {
      lbsTrash += p.MI_LbsRemoved
    }
  }
} else {
  lbsTrash = null
}

Return lbsTrash

- Holly

ChristopherCounsell

Date only() isn't supported in the current version of field maps. Probably coming in the next major release later in year that's currently in beta.

See here for a longer write up where I explained to another user:

https://community.esri.com/t5/arcgis-field-maps-questions/arcgis-field-maps-arcade-quot-dateonly-quo...

You'll need to use other ways to generate the desired input to your query. I think something like this might work:

var today = Text(Now(), "Y-MM-DD")

Cheers, Chris

HollyTorpey_LSA

Thank you!! I will try that. I knew I couldn't have a dateonly field in a layer in a Field Map, but it never occurred to me that I couldn't even use the Arcade function in this way. I'll try your idea and report back.

- Holly

HollyTorpey_LSA

Thanks again for your help! Your suggestion was definitely a step in the right direction.

I ended up having to move the date comparison out of the SQL statement because I could never get it to work. Instead, I compared the dates as I looped through the featureset.

This is my final code that does work in both Field Maps and the Map Viewer:

var sites = FeatureSetByRelationshipName($feature, "ToSiteBoundaries");

var siteName = "";
if (!isEmpty(first(sites))) {
  var site = first(sites)
  if (!isEmpty(site)) {
    siteName = site.label
  } else {
    SiteName = null
  }
}

var todaydate = Text(Now(), 'Y-MM-DD');

var SQL = "(SiteName = @siteName) AND (MI_IssueType = 'Trash') AND (MI_LbsRemoved IS NOT NULL)";

var points = Filter(FeatureSetById($map, "1974cb0925b-layer-7", ['SiteName', 'ObservationDate', 'MI_IssueType', 'Resolved', 'ResolutionDate', 'MI_LbsRemoved'], false), SQL);

if (!isEmpty(first(points))) {
  var lbsTrash = 0
  for (var p in points) {
    if (!IsEmpty(p)) {
      if (left(p.ResolutionDate, 10) == todayDate) {
        lbsTrash += p.MI_LbsRemoved
      }
    }
  }
} else {
  lbsTrash = null
}

Return lbsTrash

- Holly