Symbology with Arcade, fields from another feature

JGardner_Verina

I have a point feature class representing tanks on a site. I want to symbolize the tanks according to the date when last they were filled, which is recorded in a Survey123 form collected by the field workers. I have a table of estimated drop rates (gallons per day) for each tank, so theoretically it's possible to calculate a percentage of how full the tanks are, using the date of last fill, the drop rate, and the tanks' known capacity.

I have code that I think would work, but when writing expressions for symbology the functions FeatureSetbyPortalItem, etc are not allowed. I could put this calculated percentage into the pop-up without issue, but I would really like to symbolize the values on the map if possible.

Is there a workaround that would allow me to symbolize using values pulled from other features?

For reference, here is an expression (pardon my amateur coding) that successfully returns a percentage value which I would like to use for symbology:

 

//returns a percentage of safe fill capacity based on drop rate
var layerID = $feature.tankID

//query the drop rate table for matching tank ID
var dropRateTable = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'drop rate table portal id');

var filterStatement = "tankID = @layerID"

// must select First record from filtered records, otherwise cannot call values
var tankDropTable = First(filter(dropRateTable, filterStatement))
var tankDropRate = tankDropTable.dropRate

// get date of last fill from form
var formID = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'tank fill form portal id', 1, ['*'], false);
var tank = filter(formID, filterStatement)

if (Count(tank) == 0){
    continue
} else {
    // Order records by objectid; you can set this to some date field, too
    var ordered_tanks = OrderBy(tank, "CreationDate DESC")
    var tank = First(ordered_tanks)
    var fillDate = tank.CreationDate
}

// calculate days since last fill
var daysSince = Round(DateDiff(Date(), fillDate, 'days'))

// gallons dropped to date
var gallonsDropped = tankDropRate * daysSince

// percent full of safe fill level
var pctFull = Round((($feature.safeFill - gallonsDropped) / $feature.safeFill) * 100)

return pctFull

 

JohannesLindner

You have a few problems in your code:

• line 10: after this, you need to check if you found a drop table record, else line 11 could fail with "unexpected null")
• line 18: continue is used in for loops to jump to the next iteration and skip the rest of the for block for the current iteration. What you want to do is return null, for the same reason as in line 10.
• line 27: Date() constructs a date from the arguments you give it. I suppose you want Today(), which returns the current date.

//returns a percentage of safe fill capacity based on drop rate
var layerID = $feature.tankID

//query the drop rate table for matching tank ID
var dropRateTable = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'drop rate table portal id');

var filterStatement = "tankID = @layerID"

// must select First record from filtered records, otherwise cannot call values
var tankDropTable = First(filter(dropRateTable, filterStatement))
// no drop rate found? -> return null
if(tankDropTable == null) {
    return null
}
var tankDropRate = tankDropTable.dropRate

// get date of last fill from form
var formID = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'tank fill form portal id', 1, ['*'], false);
var tank = filter(formID, filterStatement)

if (Count(tank) == 0){
    return null
}
// Order records by objectid; you can set this to some date field, too
var ordered_tanks = OrderBy(tank, "CreationDate DESC")
var tank = First(ordered_tanks)
var fillDate = tank.CreationDate


// calculate days since last fill
var daysSince = Round(DateDiff(Today(), fillDate, 'days'))

// gallons dropped to date
var gallonsDropped = tankDropRate * daysSince

// percent full of safe fill level
var pctFull = Round((($feature.safeFill - gallonsDropped) / $feature.safeFill) * 100)

return pctFull

 

I don't know if that helps with the error you got, but it's worth a try.

 

Using the FeatureSetBy* functions in visualization and labeling isn't supported (and there are no plans to do so) for performance reasons.

 

Possible solutions:

Join table / database view

Join the two tables (only the newest record of the fill table) to the tank fc. You should be able to use those values in the visualization profile.

 

Store the attributes in the tank fc

I'm guessing the drop rate is fixed, so you can store that as attribute of the tanks.

The date when the tank was last filled could be either edited manually by your field workers or with an Attribute Rule on the Survey Form.

You can then use these attributes in the visualization profile:

var gallonsDropped = DateDiff(Today(), $feature.LastFillDate, "days") * $feature.DropRate
return Round((($feature.safeFill - gallonsDropped) / $feature.safeFill) * 100)

 

Store just the PctFull

If you don't want / can't store drop rate and last fill date in the tank fc, you have to store the fill state. In that case, you can't calculate it dynamically with Arcade, but have to do it manually or automatically (like Josh said, schedule a python script that calculates the field to run every few minutes / hour / day).