Inconsistencies in Sum calculation from repeat table

Question

Hi I have a survey with planting locations and a repeat table with the plants per location.

I have nested in the repeat the ${PlantNumbers} question and outside of it the ${TotalPlants} question with the calculation: sum(${PlantNumbers}) so as to get the total planted numbers per location.

I get inconsistencies between the two tables, especially when the user edits the record by adding rows to the repeat via the inbox.

The only thing that is left for me to do is to change the ${PlantNumbers} field type from short to long integer, as the two questions differ. But could this be the case?

I am trying to avoid changing the schema cause I have a joined view of the feature layer that is involved in a dashboard and prevents me from schema changes. I will have to delete the joined view etc.

Any ideas are welcomed.

Thanks in advance.

Cyprus_Forest_Department · Answer

Thanks for the reply.

So:

* This one is ok I believe, since the question ${PlantNumbers} can't exceed the Short Integer limit (i.e., > 32,767). The ${TotalPlants} question  can exceed that limit but it is set as Long Integer. I thought the field type mismatch could alter the calculation.
* The bind::esri:fieldType is already set in the form for the two aforementioned questions.
* This might be the solution, i.e to change the question type from integer to calculate (the calculation mode is already set). I found a tip at the third bullet of the response of IsmaelChivite on this post

I will tinker around, do some tests and see if that resolves the issue. I opened also a ticket request but to no avail.

Georgios-NektariosTselos · Answer

Hello  @Cyprus_Forest_Department ,

I would suggest you follow one of the 3 suggestions below. I believe that either the first or the second suggestion will solve the issue you were facing, otherwise head to the 3rd suggestion.

* Check Data Type (Short vs Long Integer)
Changing the ${PlantNumbers} field from Short Integer to Long Integer may resolve the issue if there is indeed a data type mismatch. However, this usually only causes errors if the numbers exceed the Short Integer limit (i.e., > 32,767). If your data does not approach this limit, then the issue may not be related to this.
* Use ‘bind::esri:fieldType’ for a Fixed Data Type
If you want to avoid changing the database schema, you can add esriFieldTypeInteger to the bind::esri:fieldType column in the XLSForm. This ensures that Survey123 correctly handles the number type.  https://support.esri.com/en-us/knowledge-base/faq-how-does-specifying-the-bind-esri-fieldtype-column--000032671  
* Alternative Calculation (sum() in a Calculated Field)
One possible cause of the issue is that sum(${PlantNumbers}) does not update correctly when the user adds records via the Inbox. You can try moving this calculation to a calculated field and setting calculationMode=always.

Best

Georgios-Nektarios Tselos