Arcade expression return nearest future date from FeatureSet

RingwayJacobs

Hi

I am working on some code to bring across two date fields, 'lastinspdate' and 'nextinspdate' from another table.

I've adapted some really useful code (below) to get me started (thanks @JohannesLindner) but I'm struggling to do two things:

1/ limit the response to just show the two date fields

2/ to add in a filter that returns the response to the record with the 'nextinspdate' nearest in the future.

Many thanks in advance. Not a natural coder here so be kind 🙂

var ref_id = $feature["CENTRAL_AS"]

var fs = FeatureSetByName($map, "InspInspectionDates", ['central_asset_id', 'lastinspdate', 'nextinspdate'], false)

var filt = "central_asset_id = @ref_id"

var cou = First(Filter(fs, filt))

if(cou == null) { return "No inspection found" }

var attributes = Dictionary(Text(cou))["attributes"]

var popup_lines = []

for(var a in attributes) {

    var line = `${a}: ${attributes[a]}`

    Push(popup_lines, line)

}

return Concatenate(popup_lines, TextFormatting.NewLine)

jcarlson

If you're building a popup, you really ought to consider using an Arcade element, rather than returning a string. You are essentially building a fields list, so why not doing it directly? Under add content in the popup menu, choose an Arcade element:

The output of that code block will be a bit different, but it's not too hard. For the null situation, we can return a simple line of text, but we can bring your two desired attributes into a field list otherwise.

What happens when there are more than one items in the inspection dates table? The default sort is by objectid, so you may want to apply an OrderBy function before grabbing the first record, to ensure that you're really getting the latest item. An in response to one of your questions, would you have a situation in which there are multiple future inspection dates, and one is further into the future? Without seeing the data, it's hard to know exactly how to approach the situation.

The first option would be to reverse the sort so that newer records are first:

fs = OrderBy(fs, 'lastinspdate DESC')

The other would be to filter out records where the "nextinspdate" field is in the past, and order by that field ascending, such that the first record will be the nearest future inspection date.

fs = Filter(OrderBy(fs, 'nextinspdate ASC'), 'nextinspdate > CURRENT_DATE()')

 But once we have that figured out, the rest isn't too hard. When we have a feature returned by the filter, we can populate the popup item with a simple loop. Calling a loop on a Feature directly will iterate over its attributes, so there's no need to convert it to a dictionary first.

for (var attr in cou) {
  // do something per attribute
}

Anyway, we put this all together, and it might look like this:

// Get inspection date records in Wo
var fs = FeatureSetByName(
  $map,
  "InspInspectionDates", 
  ['central_asset_id', 'lastinspdate', 'nextinspdate'],
  false
)

// Get future inspections, order for nearest to present
fs = Filter(OrderBy(fs, 'nextinspdate ASC'), 'nextinspdate > CURRENT_DATE()')

// Get the first
var cou = First(Filter(
  fs,
  `central_asset_id = '${$feature['CENTRAL_AS']}'`
))

// Return simple text if no inspection
if(cou == null) {
  return {
    type: 'text',
    text: 'No inspection found'
   }
}

// empty objects for output
var fieldInfos = []
var attributes = {}

for (var att in cou) {
  // push field name
  Push(fieldInfos, {fieldName: att})

  // add attribute
  attributes[att] = cou[att]
}

return {
  type: 'fields',
  title: 'Inspection Dates',
  fieldInfos: fieldInfos,
  attributes : attributes
}

Now, if you wanted to go in and make those look a little nicer with field aliases, there's more you can do with the Schema function, but the script above ought to be enough to get you a nice looking fields list in your popup.