Need help with Arcade expression for symbolizing room vacancy

[JDenham]

I'm working on a sleeping room web application, and I need help creating a symbology expression to draw attention to rooms with vacant beds available.  My data is polygon data, representing the individual rooms.  I'm planning to use the stock "capacity" field to note how many beds each sleeping room has.    I then have 4 occupant fields (occupant_1, occupant_2 etc.). 

I need an expression that will compare the quantity of non-Null occupant fields with the value in the 'capacity' field.  I'd would like to be able to symbolize the output with Gray for Capacity = 0, Green for Vacancy (the number of non-Null occupant fields is less than 'capacity' value), and Red for Full (number of non-Null occupant fields is equal to 'capacity' value).

I am not familiar with how to assemble Arcade expressions, so any help would be much appreciated.

*Edit to add-  I own the data table, so if it is best to use an arcade expression to calculate a new field as an intermediate before symbolizing, that is a possibility as well.

[MVieng]

Hi @AmandaStanko @ or @JenniferBell , would you be able to provide some helpful pointers for Jeremy?

[KenBuja · Accepted solution]

Give this a try. This loops through the array of field names to count how many aren't empty. Then it returns the result of the When function, which checks if the capacity is empty and if the capacity is greater than the number of occupants or not. And it's good practice to use the Expects function when getting fields from an array like this.

Expects($feature,'capacity', 'occupant_1', 'occupant_2', 'occupant_3', 'occupant_4');
var fields = ['occupant_1', 'occupant_2', 'occupant_3', 'occupant_4'];
var occupants = 0

for(var index in fields) {
  if (!IsEmpty($feature[fields[index]])) occupants += 1;
}

return when ($feature.capacity == 0, 'Gray',
             $feature.capacity > occupants, 'Green',
             'Red');

[JDenham]

Thank you for an incredibly fast response.  That seems to work perfectly.  I'll continue working with this to see if it needs to be tweaked at all, but it's doing exactly what I needed.

I did change the outputs to "N/A", "Vacancy", and "Full" and then applied the appropriate color in symbology.  I'll do further testing, and may use the expression to populate a vacancy_status field and symbolize from that field so that any exported table data will capture the status also.

Much appreciated.  🙂

[JDenham]

I realized that using this expression to populate a new field wouldn't provide the 'live' functionality that I need, since I don't know how to continuously re-calculate a field in order for it to reflect immediate changes.  It is functioning perfectly as a symbology expression, and I was able to adapt it for use as a labeling expression to display the number of vacant beds per room by swapping this in at the end:

return When($feature.capacity == 0, "",
Number($feature.capacity - occupants))

Thank you again.  👍