# Problem with calculation when field is set to hidden or calculate

1091
8
07-21-2017 02:27 PM
New Contributor II

I'm working on a distance calculator that will allow Protected Species Observers (PSO) to enter the compass bearing and distance to an animal they sighted from their monitoring position.  They then enter the compass bearing and distance to the noise source from their monitoring position.  From each sighting bearing it converts it to Cartesian coordinate angles and those angles are then used to calculate the angle of the triangle at PSO's location between the legs of the Animal sighting distance and Noise source distance.

It then applies the law of Cosines to calculate the distance from the noise to the animal sighting, which enable the PSO to quickly tell if the animal is in a shutdown zone or not.  In the spreadsheet I attached, everything works as designed.  However, I want to hide the Animal Modulus and Noise Modulus Results fields so the PSOs aren't seeing numbers that they don't need.  The problem I'm experiencing is when I turn those Modulus fields types to Calculate or Hidden in Survey123 Connect it breaks the math.  I updated Survey123 connect to the current version v2.3.24 today, but I believe I the problem was there in the previous version as well

For example, if I enter an animal sighting bearing of 17 deg and a Noise bearing of 122 deg, I should get an angle of 105 degrees as my result, and I do when the ModResA and ModResnN fields are set as integers.  Turning them to Hidden or Calculate and my resulting calculated angle is 73032 in the bearingangle field.  I traced the problem down farther, if I leave on of the ModRes fields set as an integer (say ModResN for this example) and enter the animal sighting bearing of 17 deg and the noise source bearing of 122 deg I get the bad result of 73032 again.  However, if I flip the value and enter 122 deg for the animal sighting and 17 deg for the noise source I get the correct angle of 105 degrees.  I do have nested if statements in the bearingangle field to choose which math should be used to calculate the angle appropriately.  Either I've made an error somewhere or calculate/hidden fields can't handle nested if statements.

Does anyone have any insight for a solution?

8 Replies
Esri Esteemed Contributor

Hi Tyler,

By default, hidden or calculate questions are of type text.  You need to either:

1) explicitly change them to integer in the formulas

2) Create a column in your spreadsheet called bind::type and set the value of the column for the modulus questions to int (integer).  This will tell Survey123 to treat it as an integer

The current answers you are getting are the result of concatenating the numbers as if they were text.

New Contributor II

James,

Thanks, the bind::type suggestion worked great.  I did try setting the bind on the bind::esri:fieldType column and I was still getting the same error.  Am I correct in assuming that Esri field type is not applied until the survey is published?

Esri Esteemed Contributor

The bind::Esri:fieldType column sets what the value is written out as (by controlling the field type in the database and the type of value submitted by the app).  The bind::type column sets how the value behaves within the application.

Occasional Contributor

I was also having this same issue. The bind field suggestion for all my values in the formula made it work properly. Thank you James Tedrick!

New Contributor III

James -

Great information and helped me to resolve my Survey123 Connect XLSForm question. This forum is really helpful!

Best regards,

Colleen

New Contributor II

Howdy All,

I can figure out how to do addition in a calculate field with integers but have not had any success with decimals.  It keeps concatenating the two fields instead of adding them.  I've also tried using "decimal" and "dec" in the field and get an error when it gets converted to the xlsforms.  \${CASH_VALUE} + \${CASH_VALUE_MP} or dec(\${CASH_VALUE}) + dec(\${CASH_VALUE_MP}).  I also tried the bind::type method and put decimal in the field and that didn't work.  Any help would be greatly appreciated.

Cheers,

Mark

Esri Esteemed Contributor

Hi Mark,

As listed in Formulas—Survey123 for ArcGIS | ArcGIS , the correct conversion for decimal numbers is number() .

New Contributor II

Thanks James!  I looked at the page you referenced but was looking for decimal and not number.  Now that I see what I should have been looking for it makes perfect sense.  That whole reading thing eludes me at times.  Thanks again.  It is working like a charm now.