Problem with calculation when field is set to hidden or calculate

[TylerGraham2]

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?  

[JamesTedrick]

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.

[TylerGraham2]

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?

[JamesTedrick]

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.

[deleted-user-jxpbpbjlZInj]

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!

[deleted-user-sNUdtFrn2yEY]

James -

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

Best regards,

Colleen 

[Anonymous User]

Hi James,

I am having this same with the latest version of Survey123 Connect (3.12) and the Survey123 app. My calculations work fine on the web but are NaN on the app. I have tried the bind field, as well as the solutions listed here Problem: The total value is not calculated if any input field in a Survey123 for ArcGIS survey form ....

Any ideas why it works in the web version but not the app? Staff are in remote areas and cannot rely on having service to use the web version. Data collection is beginning now. Any help will be much appreciated.

[Anonymous User]

IT support found that it was a syntax error. The fields used for calculations (including input fields) cannot have relevant statements. This was my work-around.

[NourSalam]

@Anonymous User your reply to this post was a life saver. I had the same exact problem regarding relevant conditions and calculations. Thank you so much!

[MarkRankin1]

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