Help with Arcade Expression

[TimothyMcKinney]

I am trying to create an arcade expression for a calculated field in my table. Any help with the proper syntax would be helpful. My table contains multiple entries with repeating values in field1 and different dates in field6. I want to find the the first entry (based on date) and perform a calculation with a field from that entry. This needs to be done per unique item in field 1.

Please see attached image for an idea of what I am trying to do. If I understand it correctly I believe I just need one more function to get the specific field value I need.

var list = Filter($featureset, 'Field1 = $feature.Field1')
var OriginalDate = Min(list, "Field6")
var OriginalNorthing = Filter(list, 'Field6 = OriginalDate')
return $feature.Field2 - OriginalNorthing

Thanks

[jcarlson]

First things first: click Expand Toolbar → Insert/Edit Code Sample for these sorts of things. It is much easier to troubleshoot someone's code when it can be copied and pasted.

On to the code itself. You're right, you're about one step away from figuring it out, though it's not a function.

When you filter a FeatureSet, the function returns another FeatureSet. To access the attributes of an individual feature, you have to pull the feature out somehow. In your case, it's very simple, since the OriginalNorthing FeatureSet only has a single item in it. We'll use First to grab the first item in the set, then use the usual notation to get a specific attribute.

Also, keep in mind that the expression in a Filter function has to be SQL-92 compatible, so the field notation from Arcade won't work. To bring an outside variable in, simply declare it elsewhere in your expression and reference it with an "@".

var filt_val = $feature.Field1

var list = Filter($layer, 'Field1 = @filt_val')

var OriginalDate = Min(list, 'Field6')

var OriginalNorthing = First(Filter(list, 'Field6 = @OriginalDate'))

return $feature.Field2 - OriginalNorthing.Field2

[TimothyMcKinney]

Thank you for the help. When adding arcade code snippets in since there is no arcade language listed is there a "best" one to select from the drop down for formatting purposes? For now I added it to my original post.

Second, I tried your code and get an object error:

Earlier I tried using $layer and was getting an object error so I changed it to $featureset as shown in my code above. If I do this in your code it ends up causing another error in in line 7. Any thoughts on what could be causing the object error?

Thanks again for your help so far.

[jcarlson]

Using javascript is usually good enough for syntax highlighting.

Why don't we try using a FeatureSetBy function, as they're a little more specific.

var filt_val = $feature.Field1
var fs = FeatureSetByName($datastore, 'your-layer-name-goes-here')

var list = Filter(fs, 'Field1 = @filt_val')
var OriginalDate = Min(list, 'Field6')
var OriginalNorthing = First(Filter(list, 'Field6 = @OriginalDate'))

return $feature.Field2 - OriginalNorthing.Field2

Note: the name you put into the function should match the table name as it's stored in the data store / file, not any alias that you've applied to it.

[TimothyMcKinney]

One step closer, now I am getting a general evaluation error for line 6. Is this a syntax issue?

var filt_val = $feature.Field1
var fs = FeatureSetByName($datastore, 'combined_points')

var list = Filter(fs, 'Field1 = @filt_val')
var OriginalDate = Min(list, 'Field6')
var OriginalNorthing = First(Filter(list, 'Field6 = @OriginalDate'))

return $feature.Field2 - OriginalNorthing.Field2

[jcarlson]

Strange. I think it would tell us if it was syntax related. Any chance this layer is accessible in a web map?

If you can utilize the AGOL / Portal expression builder, you can use the Console command to output intermediate things, like the contents of your list and OriginalDate objects, to make sure that the preceding steps are going as intended.

Barring that, you'll just have to figure out some other way of testing the individual components of this expression. Try putting the First function on its own line, perhaps, to see if that's the cause.

Or maybe it's related to how you're assembling the query? According to the docs, there are database-specific ways to indicate that you're supplying a datetime value.

[TimothyMcKinney · Accepted solution]

It was definitely an issues with the dates. After switching fields with the min function it worked so I assume the min function does not work with dates? Either way I tried a different solution to find the earliest date and OrderBy fit the bill.

First attempt:

var filt_val = $feature.Field1
var fs = FeatureSetByName($datastore, 'combined_points')

var list = Filter(fs, 'Field1 = @filt_val')
var OriginalEntry = First(Top(OrderBy(list, 'Field6 ASC'), 2))

var OriginalNorthing = First(Filter(list, 'Field6 = OriginalEntry.Field6'))
return $feature.Field2 - OriginalNorthing.Field2

This was valid however it wasn't the solution I expected as I was not getting the value from the earliest date. So either a problem with line 5 (my sorting) or 7 (the filter using the earliest date). I found out it wasn't my sorting, see below. Not sure what is causing the filtering from line 7 to not work as expected, maybe another day I will figure that one out.

After some trial and error this is working so far, will test some to make sure:

var filt_val = $feature.Field1
var fs = FeatureSetByName($datastore, 'combined_points')

var list = Filter(fs, 'Field1 = @filt_val')
var OriginalEntry = First(Top(OrderBy(list, 'Field6 ASC'), 2))
var OriginalNorthing = OriginalEntry.Field2

return $feature.Field2 - OriginalNorthing

Thank you again for all of the help. I will have to do some more research on the AGOL / Portal expression builder for the future. I am used to fumbling through python notebooks in Pro and being able to print intermediate results is a must!