Having trouble calculating related calculations in Survey123 Web Developer, can someone help?

359
6
10-02-2022 05:52 PM
IsisHoward
New Contributor II

Hi there, 

I've created a form with related "sum" calculation fields in Survey123 Web Designer, but the calculations are not registering/showing up in the data table once the form is submitted despite the *pre-submitted* calculation values being visible. I am unsure what to do since the calculations clearly CAN function properly, but aren't... I've attached a video, and also tried to describe below.

There are 2 fields with calculations in the form:

  1. # Clustered Monarchs (total) --> Sum of "clustered" monarch butterflies 
    • this field is conditional and shows only if someone marks that they observed clustered monarchs, and all the fields referenced in the calculation are conditional.
  2. GRAND TOTAL: Monarch Butterflies -->Sum of all monarch butterflies to produce "grand total" (sum of clustered butterflies from calculation above + fliers + sunners + loners + grounders fields)
    • this field is NOT conditional, but all the fields referenced in the calculation are.

Both of these fields are set to "Read-only" and have default values of 0. The fields referenced in both calculations have default values of 0, and are all conditional questions.

When I am in the "Preview" view of "Design" mode and when I select "open in browser" to test out my form, I can see the correct auto-calculated values populating the two calculated fields prior to submitting. However, once I submit the form (successfully, no notes or warnings), the calculated field values do not show up in the "Data" table.

Additionally, when I am testing out my form in the Survey123 Field App, I cannot see the two auto-calculating values (they do not populate/function in the app) prior to submitting. And, as before, the calculated field values do not show up in the "Data" table.

Can anyone help? Is there an error I can correct or a setting I can change? Or, is this a glitch?

*VIDEO CORRECTION - at min 5:20 I accidentally highlighted the wrong field. I meant to say that the 2 calculated fields are showing up as blank in the field app (in the vid I had highlighted the 1st cluster total, rather than the "# Clustered Monarchs (total)"). 

 

0 Kudos
6 Replies
LaurenceTait
Occasional Contributor II

Can you upload the xlsx form so we can have a look at it. 

 

If you can't see the values in the app and you can't see the data upon upload, that suggests you have either a relevant field that is misfiring or a problem with the name of one of our fields.

0 Kudos
IsisHoward
New Contributor II

Hi Laurence, 

I'm not sure how to do this from Survey123 Web Designer... I do not have Survey123 Connect set up yet. Is there a way I can download the xlsx for you from Web Designer? I tried googling but no luck. Thank you for responding so quickly. 

 

I've attached the excel of the submitted response if it helps at all... 

0 Kudos
LaurenceTait
Occasional Contributor II

I don't use the web designer much, so I don't really know.

Probably easiest just to pull it off the device. 

Assuming you're using an Android, it will be located at 

This PC\{Device Name]\Tablet\Android\data\com.esri.survey123\files\ArcGIS\My Surveys.

You can then do a search within that folder to find the xlsx sheet. Not sure where it is located on Apples, but it will be there somewhere.

0 Kudos
IsisHoward
New Contributor II

Using my iphone/apple (for the field app test), and I've been looking for the xlsx but cannot find 😞

I decided to make a copy of my form, then download the example form in Survey123 Connect. I've attached the xlsx form from that (did not make any edits). 

0 Kudos
LaurenceTait
Occasional Contributor II

The first problem that needs fixing is that you have the calculate fields running off fields that have relevant conditions.

For example, your calculation for row 57 (clustered_monarchs) is: ${cluster_1_monarchs_in_cluster}+${cluster_2_monarchs_in_cluster}+${cluster_3_monarchs_in_cluster}.

But ${cluster_2_monarchs_in_cluster} and ${cluster_3_monarchs_in_cluster} both have relevant conditions.

When the relevant conditions for a field are not met, the value of the field is set to null. What that means for your form is that when an observer only records a single cluster, the calculation for  (clustered_monarchs) is:

${cluster_1_monarchs_in_cluster}+null+null.  

because both the latter two values are set to null due to being not relevant.

Of course ${cluster_1_monarchs_in_cluster}+null+null can't yield a valid integer result, so it returns a null - the empty field that you are seeing.

So the first thing to do is change the fields from being relevant under those condition to being visible under those conditions. In connect you just need to move all of those [relevant] calculations to the [bind::esri:fieldType] column. That way the fields are hidden under the same conditions, but not set to null.

Not sure how to do this is the web designer.

Once you have made that change, let us know how it goes. That will probably solve the problem.

FWIW, there are quite a few other things that have been done in this survey that I wouldn't recommend. 

The three cascading relevant/visible fields for the various observations seems to be essentially a way to avoid using a repeat. But unless there is a good reason not to use the repeat function, that would be far neater and less error prone. It would also allow any number of observations, rather than being restricted to three.

I'm also not a big fan of assigning "question" field types like 'integer' and then setting them to 'read only' as you have done for your Grand Total fields. Since they are effectively just notes, you're usually better off assigning them as the 'note' type notes, and then setting the [bind::esri:fieldType] or  [bind::type] to whatever format you want.

IsisHoward
New Contributor II

Really appreciate the assistance and tips! I'm pretty new to Survey123 so these are very helpful; I'm not aware of how to make those changes in Web Designer (and I think a lot of this functionality is not possible in Web Designer, such as Repeats). But, I'll see if I can learn enough about Survey123 Connect to make some of these improvements. Thank you!!

0 Kudos