Error message of 'Arithmetic overflow error converting expression to data type int' but for only 3 of 70 field staff

508
17
04-17-2024 08:23 AM
Lisa_G
by
New Contributor III

Hello,

I have a complex form that is used for field data collection. There is a team of about 70 users. 3 of those users are experiencing problems when sending a report. The error message is the same for all 3 users. I researched this error and it seems to be a text question with an int return, or something like that. I checked over the xls and can't find a text question with an int field type, or an int question with a text field type.  But why are there only 3 users are being affected by this, and not all field staff? Also, those 3 users can send other reports without any issue. All 3 of them have a record stuck in the Outbox with this error message.

It is a large form, so maybe I missed something in the xls, I have reviewed multiple times. The most recent update was to the instance_name. I used concat when constructing the statement. Could this instance_name update cause this error? The error references the instance_name. Here is the instance_name statement:

concat(${Review_Approval}, " || ", ${ECI_ResubmissionReview}, " || ", concat('CaseNo: ', ${Case_Number}, " || ", concat('ECI: ' , ${Lead_ECI_Name}, " || ", ${Incident_ST_Address}, ", ", ${City_State_Zip}, " || ", concat('CD: ', ${CouncilDistrict}, " || ", concat('OpsDate: ', format-date(${Operation_Date}, '%m-%d-%Y'))))))

 

Thank you for any advice/help on this!

LisaG

 Arithmetic error.JPG

0 Kudos
17 Replies
abureaux
MVP Regular Contributor

Okay, there are a few things here:

  • You are specifying esri:fieldAlias, but there are invalid characters (e.g., spaces). For my test, I cleared these fields.
    abureaux_0-1713451425970.pngabureaux_1-1713451438409.png
  • This is a limitation of publishing from Enterprise (which I use), but you have capital letters in your "name" column, which isn't typically allowed. You can bypass this limitation by either publishing to AGO or referencing a pre-existing Feature Layer (such as one published from ArcPro). For my test, I reduced the form to just the fields used in the concat() and made them all lower case. This shouldn't be an issue for you, but it worth pointing out.
  • You have multiple field types specified. Unless you get an error leaving these empty, I'd leave these empty. For my test, I actually left these as-is. But, I still recommend removing these if you can. Especially where the duplicates exist in bind:type and esri:fieldType columns.
    abureaux_2-1713451689900.png
  • Your default for ${review_approval} doesn't match your choices. It should be "pending", to match what is in the "name" column of the choices tab. For my test, I changed this to match the choices tab. This won't result in any errors to have left it alone, but the field was set to "read only", so I needed a value in it.
  • Very general, but your XLSX is out of date and using the deprecated "theme-grid" appearance. I'd recommend updating your XLSX and swapping to a supported style (e.g., dynamic-grid). For my test, I didn't change these.

Unfortunately, I wasn't able to recreate the issue. My form submitted successfully:

abureaux_3-1713452183483.png

Worth asking, but is your Connect up to date? Current version is 3.19.116. If not, reinstall Connect from the Microsoft Store (this version will auto-update).

Lisa_G
by
New Contributor III

Hi,

Thank you for the detailed response. I will follow your advice and update my Connect version and the xls sheet. I will report back if I can get it to work.  Thank you/Merci!

 

0 Kudos
Lisa_G
by
New Contributor III

Hello,

I read through your list, and I did the same thing you had indicated in your message. I corrected all the alias names to be the same as the field name. I updated the default for the field Review_Approval to lower case 'pending'. I also updated my Connect version to 3.19   I just published the form and tried to send one of the stuck records in the Outbox. Nothing changed in the 2 tablets with the stuck Outbox records. The only thing I did not update was the xls sheet. I will have to research to find a way to update a working xls sheet.  I did try and change the style, under the settings tab, to dynamic-grid. It made Survey123 Connect freeze up when I tried to save that change.  So I am back where I was, with 3 field users still having a stuck record in their Outbox with the 'arthimetic error'. I really need some help here. Thank you.

0 Kudos
abureaux
MVP Regular Contributor

The newer Connect apps can update XLSX for you. Tools > Update XLSForm template

abureaux_0-1713796180422.png

Is it just the old forms not working, or do new submissions not work either?

If new submissions work properly and it's just the surveys stuck in the Field App giving you trouble, I'd lean towards data recovery now. If neither new nor old surveys are sending, we will have to look at other solutions.

Honestly, it seems odd that it isn't working since my local copy appeared to function as intended. A potential solution could be to start removing content until it does work and see if you can narrow down the culprit that way. You can do this either in Connect by stripping out content, or in the Field App by clearing fields (though I'd recommend making a copy of the *.sqlite file before clearing fields in the Field App, assuming you actually need that data).

More info on recovering surveys:

I tend to use DB Browser for SQLite for modifying S123 database files. SQLiteStudio also works well. Depending on what you want to do, you may not need either.

0 Kudos
Lisa_G
by
New Contributor III

Hi,

New submissions do work, even for the 2 tablets with stuck Outbox records. Those 2 field staff can send up reports without a problem. They still have the stuck records in their Outbox, so yes just these surveys have issues. I have signed out/in for the Survey123 app, downloaded the newest update to the form.  I did this in one of the tablets that has a stuck record and nothing changed. My next step is copy the sqlite and the MySurveyAttachments folders and try and use Survey123 Desktop to upload those records. Thank you for the links on recovering surveys, I will keep at this until I can get those stuck records to send up.

0 Kudos
abureaux
MVP Regular Contributor

This is actually good news!

Yes, I'd move on to attempting to recover the data with the desktop app, as you mentioned.

0 Kudos
Raul
by
New Contributor III

What data type is the 'Case_Number' field? I'm thinking that if you're storing values i that field as an integer, (esriFieldTypeInteger) the value for your particular case is larger than the amount the datatype can store.

According to this thread, the maximum number esriFIeldTypeInteger can store is 2,147,483,647.

0 Kudos
Lisa_G
by
New Contributor III

Hi Raul,

the Case_Number field is field type string, Length 500  bind type is string  and it is a question type of text

0 Kudos