# Calculate Field Does Not Work With Empty Values

324
3
05-31-2021 08:32 AM
New Contributor II

Please see attached screenshot from my survey XLSForm for our damage assessment survey. I have a multiple-choice question (select_multiple victimtype). For each selection made in this multiple-choice question, an integer question opens up to enter the victim count (greenvictimcount, yellowvictimcount, etc.). I also have a calculate field (totalvictims) that is supposed to add up all four integer fields to get the total number of victims, across all four victim types. This 'totalvictims' field has been blank in all the test surveys I took and I just finally figured out the reason - if ANY of the integer fields are blank in the individual survey, the totavictims field will not calculate, reasons explained at this link, under "Empty Values" heading: https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_DB5A29A...

Is there any way to make this field calculate if some of the integer questions are blank (it would rarely be the case that all four victim types are selected in the same survey)?? Is there a way to have the blank integer questions assigned a zero for the purpose of the calculation?

I also attached the screenshot of the data table that shows the totalvictims field blank across the board.

Thank you!

Tags (1)
1 Solution

Accepted Solutions
New Contributor II

OK, I figured it out thanks to this post: https://community.esri.com/t5/arcgis-survey123-questions/survey123-calculations-not-calculating/m-p/...

The solution was to use the coalesce() function to catch null values and substitute a neutral (0) value.

3 Replies
Occasional Contributor II

I would set the default value to 0, then none of your values would be blank.

New Contributor II

Thank you for your response! I just tried this and it does pre-populate the integer fields with zeros in the survey itself, but the fields still show up as blank in the data table and it still does not  calculate the totalvictims field.

New Contributor II

OK, I figured it out thanks to this post: https://community.esri.com/t5/arcgis-survey123-questions/survey123-calculations-not-calculating/m-p/...

The solution was to use the coalesce() function to catch null values and substitute a neutral (0) value.