SQL Keywords Blocking inclusion of certain fields in Form Builder

JosephHilliard94 · ‎06-16-2023

Hi there,

The new ArcGIS Online update (which clearly doesn't state this update) means you cannot use SQL keywords as field names in ArcGIS Online. Is there a definitive list of SQL keywords that we aren't allowed to use in ArcGIS Online? Going to mean quite a lot of re-configuration to existing feature services without prior warning.....

D_AnneHammond · ‎06-28-2024

Thanks Laura, our feature class hasn't changed and didn't throw an error previously. Also, this is an authoritative feature class with many dependencies so changing the base field name might be an issue, I'll have to check with our Coordinator. Why was it working previously and then all of a sudden it's not? Also, @SamiHammer noted above that GlobalID and FID are also reserved words and we can't publish field apps that have, or will collect, attachments or related tables without GlobalID.

TAMRAO_HALLORAN · ‎07-01-2024

Laura-

We were experiencing the same issue and nothing in our Offline maps has changed. Can you please share what changed and any updates to this issue?

Thanks!

JustinColville · ‎07-01-2024

Hello, can you please share more details of what you are seeing? What is the error message? Can you share the schema of the effected layer(s)? Are you able to take the map offline in Field Maps Mobile? If not what error message(s) are you seeing there (and if possible please share a troubleshooting log from mobile)?

Can you please share what changed and any updates to this issue?

On Friday evening, based on comments in this post and a couple other posts, we reduced the set of keywords used when showing this new message as we test the complete of SQLite keywords (https://sqlite.org/lang_keywords.html). Currently we are only showing this message for the following 10 keywords which will cause an error when taking a map offline:

select
commit
set
then
foreign
group
index
unique
intersect
join

As we test the SQLite keywords we plan to expand this list.

SamiHammer · ‎07-01-2024

The Friday evening update seems to have fixed the issue for our group. We have a couple layers with the keyword DO (for dissolved oxygen as a water quality parameter) and some with TEMP (for temperature). Just mentioning that in case those are common ones that may cause other people problems if you block them.

Justin, you indicated that "SQLite keywords as fields have never been supported when taking a map offline, the maps will fail when trying to take them offline in mobile or fail packaging in Online. This was not a new restriction, but we made changes in this release to error at authoring time so that we can more easily tell there is an issue with a layer and offline usage." But these are maps and layers my group and others in this thread taken offline for years with no problems that did contain some SQL keywords.

Thank you for at least getting the temporary fix going. And if there needs to be a broader fix, many people have layers that would be difficult to change - please figure out how to notify folks in advance, help them make changes, and try to do it at a time of year where fewer people are in the field.

JustinColville · ‎07-01-2024

@SamiHammer

Again sorry for the confusion that this has caused. It was not our intention to prevent existing layers that were working to stop working.

For the statement "SQLite keywords as fields have never been supported when taking a map offline, the maps will fail when trying to take them offline in mobile or fail packaging in Online. This was not a new restriction, but we made changes in this release to error at authoring time so that we can more easily tell there is an issue with a layer and offline usage.", there is a list of words that have always caused offline to fail. The issue last week was caused by us using a keywords list that was larger than it needed to be. We are retesting all of the keywords to refine the list, which will be around 60 words, that cause a map to fail to go offline if it is used as a field name in a feature layer.

SamiHammer · ‎07-01-2024

Thanks for that clarification Justin, that does make the situation make more sense.

TrevorDenson · ‎06-27-2024

This is my nightmare. I have 10+ maps and over 40 people not being able to collect data because of the updates. I can't find out what fields are throwing the error. They are unable to download offline areas now.

JustinColville · ‎06-27-2024

@TrevorDenson sorry to hear that you are having problems.

Can you tell me a little more about your workflow? What are the error message? Where are you seeing these error message, Field Maps Designer or Field Maps Mobile? When the users are trying to take the maps offline what errors are they seeing? Would you be able to send troubleshooting logs from mobile?

If you would like please submit an issue with support as well

SQLite keywords as fields have never been supported when taking a map offline, the maps will fail when trying to take them offline in mobile or fail packaging in Online. This was not a new restriction, but we made changes in this release to error at authoring time so that we can more easily tell there is an issue with a layer and offline usage.

TrevorDenson · ‎06-28-2024

@JustinColville

Hi. I am seeing this error message in the Offline tab of Field Maps Designer:

When I go to click on the layer, it does not give me any indication of what field(s) are causing the issue. It is preventing folks from downloading offline areas now. There was a statement earlier in this thread by @ColinLawrence that said:

"this wasnt an intentional change and will be fixed in the next couple of days. We should continue to ignore existing fields with reserved keywords created from another source (ie Pro) and loaded into Designer. There should only be an error when creating new fields in Designer with the restricted keywords."

My workflow is to publish from ArcGIS Pro because of my program's use of Subtypes/Templates. I have to have everything setup in Pro before I can publish in order for it to work. The ability to work with Subtypes in AGOL is rather challenging and any modification to field or list values breaks it. The fields haven't been giving me issues for the last several years and I wonder why it's a problem now.

My staff is unable to download offline areas all of a sudden and I will likely just have to republish each of the 10 maps if it continues to be an issue. Some folks that have had offline areas downloaded already are still able to sync, but it is not consistent across the board.

Do you have a list of SQL Keywords available that are prohibited? I have changed fields like "Year" to "Year_". I would doubt my use of Field Aliases/labels would cause this issue...

Thanks. Sorry if I am being dramatic... I am just confused why all of a sudden, in the middle of field season with remote workers, that it broke after working for the last three+ months.

JustinColville · ‎06-28-2024

@TrevorDenson Sorry for the confusion this is causing. Thank-you for the details.

A list of SQLite keywords is available here - https://sqlite.org/lang_keywords.html. If possible would you be able to send me the schema of the layer that is giving you the error message?

"I would doubt my use of Field Aliases/labels would cause this issue"

You are correct, this is for field names.

Colin's comment was from June 2023 and was in regards to a change to the Forms tab in Field Maps Designer and a fix was made shortly after Colin's comment around the end of June last year. His comment was not about the new messaging you are seeing in the Offline tab. The change that we added in this release was to add the new error messaging in the Offline tab. Prior to this release you would not have seen that error message, but SQLite keywords as fields have never been supported when taking a map offline. The maps will fail when trying to take them offline in mobile or fail when packaging map areas in Online/Field Maps Designer. This restriction with SQLite keywords is not new at this release, we have just made this more visible to help avoid packaging/offline download errors that would have been occurring.

"My staff is unable to download offline areas all of a sudden"

This change was just in Field Maps Designer and did not change anything in Field Maps mobile. If your staff are taking maps offline from the mobile device, this change would not come into play. There might be another issue happening as well. What errors are your staff seeing on the mobile devices? Would you be able to send a troubleshooting log from Field Maps mobile?

I would encourage you to reach out to support and submit an issue as well.