Issues with Arcade... Dictionary to FeatureSet

[DJB]

I created an arcade expression that takes a feature service and calculates some stats and outputs the results into a FeatureSet.

The problem that I run into is some of my Dict is not writing to my FeatureSet.  When using the Console I can see that everything has written properly to my dictionary but seems to be missing from the FeatureSet.

I highlighted the two rows that are missing.

var Trap_Locations_Tab = FeatureSetByPortalItem(Portal('https://xxxx.arcgis.com/'), '99999999999999999999', 0, ['*'])

var Year_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year IS NOT NULL'), 'Year')

var TrapDict = {'fields': [{'name':'Pests', 'type':'esriFieldTypeString'},
{'name':'Year', 'type':'esriFieldTypeInteger'},
{'name':'Total_Num_Traps', 'type':'esriFieldTypeInteger'},
{'name':'Total_Trap_Checks', 'type':'esriFieldTypeInteger'},
{'name':'Traps_Checked_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Total_Moth_Count_Sum', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Prior_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Counts_Sum_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Average_Moth_Count_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Change_Trap_Counts', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Change', 'type':'esriFieldTypeInteger'}
], 'geometryType': '', 'features': []};

var dt_7 = DateAdd(Date(Max(Trap_Locations_Tab, 'Trap_Check_Date')), -7, 'days')

for (var Y in Year_Tab) {

  var Pest_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year = ' + Y.Year), 'Pest')

  for (var P in Pest_Tab) {

    var PSQL = "Pest = '" + P.Pest + "' And " +  'Year = ' + Y.Year 
    var PrevSQL = "Pest = '" + P.Pest + "' And " +  "Year = " + Y.Year + " And Trap_Check_Date > '" +  dt_7 + "'"
    var PriorSQL = "Pest = '" + P.Pest + "' And " +  "Year = " + Y.Year + " And Trap_Check_Date < '" +  dt_7 + "'"

    var Total_Trap_Cnt = Count(Distinct(Filter(Trap_Locations_Tab, PSQL), 'ParentGlobalID'))
    var Total_Trap_Checks = Count(Filter(Trap_Locations_Tab, PSQL))
    var Trap_Checks_Last_Week = Count(Filter(Trap_Locations_Tab, PrevSQL))

    var Moth_Count_Sum = Sum(Filter(Trap_Locations_Tab, PSQL), 'Counts')
    var Moth_Count_Sum_Prior_Last_Week = Sum(Filter(Trap_Locations_Tab, PriorSQL), 'Counts')
    var Moth_Count_Sum_Last_Week = Sum(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
    var Moth_Count_Mean_Last_Week = Mean(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')

    Push(TrapDict.features,{
      'attributes': {
        'Pests': Decode(P.Pest, 'WBC', 'Western Bean Cutworm', 'ECB_E', 'European Corn Borer - E (New York Strain)', 'ECB_Z', 'European Corn Borer - Z (Iowa Strain)', 'ECB_H', 'European Corn Borer Hybrid', 'CEW', 'Corn Earworm', 'BCW', 'Black Cutworm', 'TAW', 'True Armyworm', 'FAW', 'Fall Armyworm', 'Other'),//P.Pest,
        'Year': Y.Year,
        'Total_Num_Traps': Total_Trap_Cnt,
        'Total_Trap_Checks': Total_Trap_Checks,
        'Traps_Checked_Last_Week': Trap_Checks_Last_Week,
        'Total_Moth_Count_Sum': Moth_Count_Sum,
        'Moth_Count_Sum_Prior_Last_Week': Moth_Count_Sum_Prior_Last_Week,
        'Moth_Counts_Sum_Last_Week': Moth_Count_Sum_Last_Week,
        'Average_Moth_Count_Last_Week': Moth_Count_Mean_Last_Week}
        })
   
}}
Console(TrapDict)
return FeatureSet(Text(TrapDict)); 

And the value (CEW) for 2024 that is included falls between the two that are missing... alphabetically (BCW, CEW, TAW).

Any help would be greatly appreciated.

Cheers!

[DJB · Accepted solution]

Well... I think I answered my own question.  The issue was around the Date SQL.  I was doing my Date calculations from my service as opposed to using a new Date variable Today().

var dt_7 = DateAdd(Date(Max(Trap_Locations_Tab, 'Trap_Check_Date')), -7, 'days')

As opposed to...

var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')

I need this change detection for my dashboard.

Here's is my finished code just in case it may help someone else 😁

var Trap_Locations_Tab = FeatureSetByPortalItem(Portal('XXXXXXXx.arcgis.com/'), '9999999999999999999999999999', 0, ['*'])

var Year_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year IS NOT NULL'), 'Year')

var TrapDict = {'fields': [{'name':'Pests', 'type':'esriFieldTypeString'},
{'name':'Year', 'type':'esriFieldTypeInteger'},
{'name':'Total_Num_Traps', 'type':'esriFieldTypeInteger'},
{'name':'Total_Trap_Checks', 'type':'esriFieldTypeInteger'},
{'name':'Traps_Checked_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Total_Moth_Count_Sum', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Prior_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Counts_Sum_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Average_Moth_Count_Last_Week', 'type':'esriFieldTypeDouble'},
{'name':'Change_Trap_Counts', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Change', 'type':'esriFieldTypeInteger'}
], 'geometryType': '', 'features': []};

var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')

for (var Y in Year_Tab) {

  var Pest_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year = ' + Y.Year), 'Pest')

  for (var P in Pest_Tab) {

    var PSQL = "Pest = '" + P.Pest + "' And " +  'Year = ' + Y.Year 
    var PrevSQL = "Pest = '" + P.Pest + "' And " +  "Year = " + Y.Year + " And Trap_Check_Date > '" +  dt_7 + "'"
    var PriorSQL = "Pest = '" + P.Pest + "' And " +  "Year = " + Y.Year + " And Trap_Check_Date < '" +  dt_7 + "'"

    var Total_Trap_Cnt = Count(Distinct(Filter(Trap_Locations_Tab, PSQL), 'ParentGlobalID'))
    var Total_Trap_Checks = Count(Filter(Trap_Locations_Tab, PSQL))
    var Trap_Checks_Last_Week = Count(Filter(Trap_Locations_Tab, PrevSQL))

    var Moth_Count_Sum = Sum(Filter(Trap_Locations_Tab, PSQL), 'Counts')
    var Moth_Count_Sum_Prior_Last_Week = Sum(Filter(Trap_Locations_Tab, PriorSQL), 'Counts')
    var Moth_Count_Sum_Last_Week = Sum(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
    var Moth_Count_Mean_Last_Week = Mean(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')

    Push(TrapDict.features,{
      'attributes': {
        'Pests': Decode(P.Pest, 'WBC', 'Western Bean Cutworm', 'ECB_E', 'European Corn Borer - E (New York Strain)', 'ECB_Z', 'European Corn Borer - Z (Iowa Strain)', 'ECB_H', 'European Corn Borer Hybrid', 'CEW', 'Corn Earworm', 'BCW', 'Black Cutworm', 'TAW', 'True Armyworm', 'FAW', 'Fall Armyworm', 'Other'),//P.Pest,
        'Year': Y.Year,
        'Total_Num_Traps': Total_Trap_Cnt,
        'Total_Trap_Checks': Total_Trap_Checks,
        'Traps_Checked_Last_Week': Trap_Checks_Last_Week,
        'Total_Moth_Count_Sum': Moth_Count_Sum,
        'Moth_Count_Sum_Prior_Last_Week': Moth_Count_Sum_Prior_Last_Week,
        'Moth_Counts_Sum_Last_Week': Moth_Count_Sum_Last_Week,
        'Average_Moth_Count_Last_Week': Moth_Count_Mean_Last_Week}
        })
}}

return FeatureSet(Text(TrapDict)); 

~Dan