nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I pull the most recent date with my Arcade expression

Anonymous User

One problem that I am having with my Arcade script is trying to pull the most recent spill date when clicking on the districts. This is what I have so far:

var intersectLayer =Intersects(FeatureSetByName($map,"Spill Service Requests"), $feature)

for (var f in intersectLayer){
return Max(f.SR_Date_Time)
}

I get a result of NaN and Type 'Number'. How could I get the result in the correct format and bring back the date, and possibly other fields for that record. Any help is greatly appreciated. Thanks

Najděte více příspěvků označených jako

Accepted answers

All comments

NaomiBegg2

Hi Xander Bakker‌

Can you help me take this one step further?

From my list of actions I want to only identify the actions that are "Completed", from this I want to find use the most recent completed actions date.

My below script currently works as we have only just started collecting data so we don't have multiple completed dates.

<SPAN class="keyword token">var</SPAN> a <SPAN class="operator token">=</SPAN> <SPAN class="token function">Top</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Intersects</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"All Actions"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="token function">Buffer</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">,</SPAN><SPAN class="number token">20</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'meters'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CreationDate DESC'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> action <SPAN class="operator token">=</SPAN> <SPAN class="string token">''</SPAN>
<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="string token">''</SPAN>
<SPAN class="keyword token">var</SPAN> ndate <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">1900</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> b <SPAN class="keyword token">in</SPAN> a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
 <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>Ranger_Action <SPAN class="operator token">==</SPAN> <SPAN class="string token">"Completed"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
 action <SPAN class="operator token">=</SPAN> b<SPAN class="punctuation token">.</SPAN>Ranger_Action <SPAN class="operator token">+</SPAN> b<SPAN class="punctuation token">.</SPAN>CreationDate
 ndate <SPAN class="operator token">=</SPAN> <SPAN class="token function">DateADD</SPAN><SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">21</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN> 
 result <SPAN class="operator token">=</SPAN> <SPAN class="string token">" Next mow due"</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN>ndate<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' Y/MM/DD '</SPAN><SPAN class="punctuation token">)</SPAN> 
 <SPAN class="punctuation token">}</SPAN><SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">{</SPAN>
 <SPAN class="comment token">// result = "Not regularly mowed"</SPAN>
 
 <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">return</SPAN> result‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I've tried adding in some if statements and a test date to look at if it is the most recent completed date. However this script isn't working yet and I assume there is a must simpler way of doing this.

<SPAN class="keyword token">var</SPAN> a <SPAN class="operator token">=</SPAN> <SPAN class="token function">Top</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Intersects</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"All Actions"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="token function">Buffer</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">,</SPAN><SPAN class="number token">20</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'meters'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CreationDate DESC'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> action <SPAN class="operator token">=</SPAN> <SPAN class="string token">''</SPAN>
<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="string token">''</SPAN>
<SPAN class="keyword token">var</SPAN> ndate <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">1900</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">var</SPAN> testdate <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">1900</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> b <SPAN class="keyword token">in</SPAN> a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
    <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>Ranger_Action <SPAN class="operator token">==</SPAN> <SPAN class="string token">"Completed"</SPAN> AND ndate <SPAN class="operator token">==</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">1900</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">01</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
        ndate <SPAN class="operator token">=</SPAN> <SPAN class="token function">DateADD</SPAN><SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">21</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN> 
        result <SPAN class="operator token">=</SPAN> <SPAN class="string token">" Next mow due"</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN>ndate<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' Y/MM/DD '</SPAN><SPAN class="punctuation token">)</SPAN>  
    <SPAN class="punctuation token">}</SPAN> <SPAN class="keyword token">else</SPAN> <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>Ranger_Action <SPAN class="operator token">==</SPAN> <SPAN class="string token">"Completed"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
                testdate <SPAN class="operator token">=</SPAN> b<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">;</SPAN>
                <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>testdate <SPAN class="operator token"><</SPAN> ndate<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
                action <SPAN class="operator token">=</SPAN> b<SPAN class="punctuation token">.</SPAN>Ranger_Action <SPAN class="operator token">+</SPAN> b<SPAN class="punctuation token">.</SPAN>CreationDate
                ndate <SPAN class="operator token">=</SPAN> <SPAN class="token function">DateADD</SPAN><SPAN class="punctuation token">(</SPAN>b<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">21</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN> 
                result <SPAN class="operator token">=</SPAN> <SPAN class="string token">" Next mow due"</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN>ndate<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' Y/MM/DD '</SPAN><SPAN class="punctuation token">)</SPAN>  
                <SPAN class="punctuation token">}</SPAN><SPAN class="keyword token">else</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>
        
    <SPAN class="punctuation token">}</SPAN><SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">{</SPAN>
       <SPAN class="comment token">// result = "Not regularly mowed"</SPAN>
        
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> result‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

NaomiBegg2

Awesome, that has worked.

I had started working on a for loop yesterday but I was getting confused by the date so its great to see how you converted it to Text. My alternative I was working on was using the YEAR(),MONTH(),DAY() functions however I found it very weird that they decided to go with IT speak rather than common language and put January as month 0 and December as month 11 (today is 05/02/2020 not 05/01/2020!!).

XanderBakker

Hi Naomi Begg ,

In your screenshot (which unfortunately only shows part of the expression used) you notice that the return type is a featureset and a featureset cannot be visualized in the pop-up. To create a readable result, you will have to loop through the featureset and create a multiline string with the information of the features that you want to show.

Something like this:

<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Top</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Intersects</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>

<SPAN class="keyword token">var</SPAN> result <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> f <SPAN class="keyword token">in</SPAN> fs<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
    <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>result <SPAN class="operator token">==</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        result <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' - (Y/MM/DD) '</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">": "</SPAN> <SPAN class="operator token">+</SPAN> f<SPAN class="punctuation token">.</SPAN>Activity<SPAN class="punctuation token">;</SPAN>
    <SPAN class="punctuation token">}</SPAN> <SPAN class="keyword token">else</SPAN> <SPAN class="punctuation token">{</SPAN>
        result <SPAN class="operator token">+</SPAN><SPAN class="operator token">=</SPAN> TextFormatting<SPAN class="punctuation token">.</SPAN>NewLine <SPAN class="operator token">+</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>CreationDate<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' - (Y/MM/DD) '</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">": "</SPAN> <SPAN class="operator token">+</SPAN> f<SPAN class="punctuation token">.</SPAN>Activity<SPAN class="punctuation token">;</SPAN>
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> result<SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

NaomiBegg2

Hi Uri Gilad‌

I've tried your script.

I had to add in layer = Intersect(.....) as I'm interested in events that have happened within an area.

However my date field is the default CreationDate and I have an error saying that it cannot call member property on this type.

I've successfully managed to use the first suggested solution comparing max_date. I am now interested in being able to show Top 5 results using the top function. However I am yet to get this to work in a pretty manner of inserting the whole table into the popup or being able to put only selected fields in (and potentially later filtering by a field). I have managed to list the top 5 activities and dates, but I did not get these to be nicely beside each other (date: activity, new line ...) rather a list of all the activities and a list of all the dates.

UriGilad_EsriAu

Hi Collin & Naomi,

Try this solution to get latest date from a layer:

var LatestDate = Max($layer, "Your_Date_Field")
var FormatDate = Date(LatestDate)
return FormatDate

And to get the latest date from all features intersecting a polygon:

var intersectLayer = Intersects(FeatureSetByName($map,"you_point_layer"), $feature);
var LatestDate = Max(intersectLayer, "Your_Date_field")
var FormatDate = Date(LatestDate)
return FormatDate

NaomiBegg2

Hi Xander Bakker‌

I've tried this Top function for a popup.

I get results within the attribute expression builder test.

I have the displaying a description from one field (the expression).

However the popup is showing no information available. Because it is returning as a table, does this need to be inputted into the popup in a different way? Or do I need to be calling out the couple of fields I am actually interested in from the table?

XanderBakker

Taking into account the recommendations made by PBarker-esristaff here: What’s new with Arcade: Taking a stroll through FeatureSets Part 2

Key things to remember
I happen to like lists, so here's a list to help summarize what we’ve learned into a handy set of tips:
Define the minimum needed FeatureSets required to do the job
Always reduce your FeatureSets when you can
Filtering by attributes will always be faster than filtering by geometry
Chain functions together when possible to reduce queries
Use stats and sorting to avoid unnecessary loops
Review Part 1 of this blog series

I wonder if this expression should be changed to:

<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Top</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">OrderBy</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Intersects</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Spill Service Requests"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> $feature<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SR_Date_Time DESC'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

XanderBakker

Did you try what I answered here: https://community.esri.com/docs/DOC-12773-using-featuresetby-functions-in-arcade-to-drill-down-to-other-layers-and-table… ?

<SPAN class="keyword token">var</SPAN> intersectLayer <SPAN class="operator token">=</SPAN><SPAN class="token function">Intersects</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">FeatureSetByName</SPAN><SPAN class="punctuation token">(</SPAN>$map<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Spill Service Requests"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> $feature<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> max_date <SPAN class="operator token">=</SPAN> <SPAN class="token function">Date</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1900</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">for</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">var</SPAN> f <SPAN class="keyword token">in</SPAN> intersectLayer<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>SR_Date_Time <SPAN class="operator token">></SPAN> max_date<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; max_date <SPAN class="operator token">=</SPAN> f<SPAN class="punctuation token">.</SPAN>SR_Date_Time<SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">return</SPAN> max_date<SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.