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

[Lisa_G]

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

 

[DougBrowning]

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.

[Lisa_G]

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

[Lisa_G]

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!

[abureaux]

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

[abureaux]

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.

[Lisa_G]

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

[DougBrowning]

Wild guess maybe try without the ' || ' ?

[abureaux]

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.

[Lisa_G]

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