topic Re: Error message of 'Arithmetic overflow error converting expression to data type int' but for only 3 of 70 field staff in ArcGIS Survey123 Questions

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

Lisa_G — Wed, 17 Apr 2024 15:23:53 GMT

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

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

DougBrowning — Wed, 17 Apr 2024 16:27:45 GMT

Is there a reason you are nesting all those concats and not just using one? I see no reason for all of those just do one big one. I would try that.

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

Lisa_G — Wed, 17 Apr 2024 16:32:48 GMT

Hi Doug,

Thanks for your email. I had problems getting the syntax correct and this seemed to be the only way I could get it to work. You're right, nested concats aren't really needed. I will try out one concat for the instance name. Do you think this would have caused the error? Many thanks, Lisa

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

Lisa_G — Wed, 17 Apr 2024 20:05:18 GMT

Update: I did restructure the instance name and removed the nested concat. Published the form, and no change on the stuck record in the Outbox. The original error is still happening. I am open to any ideas to find a solution to this. Thank you!

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

abureaux — Wed, 17 Apr 2024 20:47:09 GMT

Another minor thing, but try to not mix and match quotes. Use either all single or all double quotes. Generally it doesn't matter which you use for operations like this (I tend to go with single quotes because I'm lazy).

What does your new equation look like? I got this after normalizing quotes and removing the nesting:

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

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

abureaux — Wed, 17 Apr 2024 20:54:11 GMT

I tried out the equation I had above and it worked:

I was wondering if there was a string length issue (e.g., 255) that you may be running into, so I tested that as well and got the standard "database error" (see below). There is a good change you will run into this issue since you are combining so many fields. Either way though, I wasn't able to recreate your error.

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

Raul — Thu, 18 Apr 2024 13:20:22 GMT

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.

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

Lisa_G — Thu, 18 Apr 2024 13:39:07 GMT

Hi, I did update the instance name and it looks like this now. I removed the city, state, zip field to save length. I checked each of the fields in the instance name.

Review_Approval - field type string, Length 500 No bind type ECI_ResubmissionReview - field type string, Length is blank (I will update to 255) No bind type Case_Number - field type string, Length 500 bind type is string This field has a calculation: once(concat(${Lead_ECI_EID}, format-date(${Operation_Date}, '%Y%m%d%H%M))) Lead_ECI_Name field type is null Length 500 No bind type (I used calc statement pulldata('@property', 'username') Incident ST Address - field type string, Length 500 No Bind type Operation Date - field type Date No length Bind Type dateTime

I am new at writing this statements and I am trying to get better at it. Thank you for looking at my problem.

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

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

Lisa_G — Thu, 18 Apr 2024 13:43:48 GMT

Hi Raul,

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

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

DougBrowning — Thu, 18 Apr 2024 13:55:04 GMT

Wild guess maybe try without the ' || ' ?

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

abureaux — Thu, 18 Apr 2024 14:26:01 GMT

Can you please provide your XLSX. I see no reason for this to not work since my test (above) seemed to function as expected with all fields and text.

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

Lisa_G — Thu, 18 Apr 2024 14:31:52 GMT

Hi, I am happy to share my xls sheet. Thank you so much for any help.

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

abureaux — Thu, 18 Apr 2024 15:06:55 GMT

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).

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

Lisa_G — Thu, 18 Apr 2024 16:40:17 GMT

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!

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

Lisa_G — Mon, 22 Apr 2024 12:53:22 GMT

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.

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

abureaux — Mon, 22 Apr 2024 14:52:35 GMT

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.

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

Lisa_G — Mon, 22 Apr 2024 16:04:39 GMT

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.

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

abureaux — Mon, 22 Apr 2024 16:13:36 GMT

This is actually good news!

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