topic Re: Error Converting XLS Form in ArcGIS Survey123 Questions

Error Converting XLS Form

CEMPAdminPOC — Mon, 23 Jan 2023 19:14:33 GMT

I have a Survey created in 123Connect that I have an email link. I am now getting a message that says: b'ODK Validate Errors:\n>>Something broke the parser. See above for a hint.\norg.javarosa.xpath.XPathUnsupportedException: XPath evaluation: unsupported construct[filter expression]\n\nResult: Invalid"

My calculation is as follows:

"Hello," + "\n" + "This is a request to determine the availability for deployment of the following apparatus:" + "\n"+ "\n" + "TYPE: " + ${Type_Group} + "\n" + "COMMON NAME: " + ${Common_Name } + "\n" + "YEAR: " + ${Apparatus_Year} + "\n" + "MAKE: " + ${Make} + "\n" + "Please respond ASAP, using REPLY ALL to advise if you are able to provide apparatus support." + "\n" + "Thank you for your consideration."

I am assuming the error is in this text due to the \n in the error message.

Can anyone identify where I have made this error?

Re: Error Converting XLS Form

DougBrowning — Mon, 23 Jan 2023 20:10:26 GMT

I have used \n ok in a calculation with a field of type note.

"Bottom of the reach (1)\nF-transect (1)\nTop of the reach (1)\nOverview (1)\nMonument (3)\nCritical concepts (4:2 annotated and 2 copies unannotated)\nDepositional bank (1 if depositional banks are present)\nErosional bank (1)\nBank erosion feature (1)\nOther (not required)\nFlood Prone Width (Taken on the flood prone width form NOT on this form)"

I have also done join("\n", ${PhotoType})

My guess is the issue is using a \n inside a URL. Totally different deal. I would test that. This link may help https://stackoverflow.com/questions/3871729/transmitting-newline-character-n

Another idea is maybe you are over the 255 char limit on that field.

hope that helps

Re: Error Converting XLS Form

CEMPAdminPOC — Mon, 23 Jan 2023 21:26:40 GMT

Thanks for the reply. I still get an error message. It was working previously without issue, but now I can't update the sheet, which I will need to as staff changes.

I would like help to identify where on my sheet the error message is pointing to.

Re: Error Converting XLS Form

abureaux — Mon, 23 Jan 2023 22:02:13 GMT

I dumped your text above into a test, and everything worked.

I replaced all of your tags with "FFFFFF" because I obviously didn't have those fields.

The /n is contained within quotes. I doubt that would be causing an error for you during parsing here.

I did notice that you have extra spaces in some fields. E.g., ${Common_Name }

Also, you should avoid capitals for names in S123. E.g., change Common_Name to common_name

Re: Error Converting XLS Form

CEMPAdminPOC — Mon, 23 Jan 2023 22:17:07 GMT

Thank you for trying. I still can't get mine to resolve.

Re: Error Converting XLS Form

DougBrowning — Mon, 23 Jan 2023 22:18:03 GMT

Good catch on the space.

But why would you say this?

"Also, you should avoid capitals for names in S123. E.g., change Common_Name to common_name"

I always use CamelCase for years with no issues. Curious here.

Re: Error Converting XLS Form

DougBrowning — Mon, 23 Jan 2023 22:23:03 GMT

You have a space in the field Respiratory_Mask

Notice the green box is telling you its a problem.

Sorry missed that the first time. Not sure that is it though.

Re: Error Converting XLS Form

abureaux — Mon, 23 Jan 2023 22:52:09 GMT

The parsing error is in the calculation on line 56. The one one line 55 is fine.

Looks like a comma was out of place:

Change body=,'${Email to body=',${Email

Not related to your error, but you have a few syntax issues (High-Moderate-Low is the level of importance, IMO):

(Moderate) Many random spaces in things. For example, in your calculation on line 56, there are several spaces after the calculation
(High) You have capitals in almost all of you name fields. As stated above, these should all be lower case (lower case, can contain but not start with a number, no special characters other than underscore)
(High) As @DougBrowning pointed out below, you have a space at the end of the Respiratory_Mask name
(Low) Numerous groups are without a label. I see you are using notes with an empty <font> tag for spacing. You can do the same with groups. to avoid an error
(Low) The group starting on line 3 (and missing a label) doesn't appear to do anything
(Low) In many spots, you have the old bold start tag without an end tag (In HTML, all tags need to be closed). E.g., <b></b>

Re: Error Converting XLS Form

DougBrowning — Mon, 23 Jan 2023 22:56:46 GMT

@abureaux

I do not get this one

(High) You have capitals in almost all of you name fields. As stated above, these should all be lower case (lower case, can contain but not start with a number, no special characters other than underscore

There is no reason you cannot use caps that I know of. I have used it thousands of times.

Re: Error Converting XLS Form

abureaux — Tue, 24 Jan 2023 15:29:02 GMT

Perhaps there is a setting for this, but this is the default state/result if capitals are used:

When a feature service is created in ArcPro with capitals and then used in S123, it seems to skip this check. Otherwise, this is my reasoning.

Edit. I guess marking it as "high" is misleading in this case. For this particular case, the form is obviously working with the capitals in names, so it's not critical. I tend to always assume that someone else will have a similar problem in the future and will find random threads via Google, so I try to address things that may cause confusion. Sometimes I'm successful! Sometimes not.

Re: Error Converting XLS Form

CEMPAdminPOC — Tue, 24 Jan 2023 20:23:25 GMT

Hello All.

I made the changes as recommended, but the error message is still the same. Interestingly, I copied this to a new survey and edited the sheet by adding a new row, and it accepted the changes. For some reason, it appears that this particular survey is not working, but the input works in a new environment. I added the geo point later, but that shouldn't impact the survey.

Re: Error Converting XLS Form

DougBrowning — Tue, 24 Jan 2023 20:37:44 GMT

Ok I think you have a secret newline in excel. Happens to me on the reg.

Turn on word wrap for each column then look to see if anything goes to a double size row. That is your clue to find them. This can happen from a copy paste and would explain this.

Re: Error Converting XLS Form

CEMPAdminPOC — Tue, 24 Jan 2023 20:50:41 GMT

So I am better to turn off wrap text in the form?

Re: Error Converting XLS Form

DougBrowning — Tue, 24 Jan 2023 20:59:53 GMT

Sorry I mean turn it on. Look for any double lines. This means a \n got slipped in there. Delete from the end and you will see the double line disappear. Once fixed you can flip it back. But you got to get those outta there. You will see them.

Re: Error Converting XLS Form

CEMPAdminPOC — Tue, 24 Jan 2023 21:11:06 GMT

Guess I didn't hide any lines either.

Re: Error Converting XLS Form

DougBrowning — Tue, 24 Jan 2023 22:27:59 GMT

I found them on

Equipment_Prov_Guidelines

Maintenance_Power_Equip

Training_Records

Training_Members

There are actually a ton. This sheet is a bit of a mess. You need to be careful with the pasting.

I suggest rebuilding this form by moving to a new template. I have a write up somewhere. But basically start a new form in connect. Move each column from the old to the new making sure to Paste Values! Then again look for the extra lines above I guess since that will not fix it.

Then just rename and replace the xlxs in the file system on the old form and it will take it.

Good luck