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