Error message of 'Arithmetic overflow error converting expression to data type int' but for only 3 of 70 field staff
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
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.
- 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.
- 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:
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).
The newer Connect apps can update XLSX for you. Tools > Update XLSForm template
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:
- Starting page: What are the ways to recover ArcGIS Survey123 data?
- The above link leads to: Recover data using desktop field app
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.
