When using the ArcGIS for Excel plugin, imported AGOL layer tables with long domain lists cause errors in the Excel file.

[EMorgan]

I have a .xlsx file with ArcGIS for Excel plugin, bringing a hosted feature layer table that has a domain driven field.

When I save and close the Excel file and later re-open it, I receive this Microsoft Excel error message: 

"We found a problem with some content in 'TEST1234.xlsx'. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes."

When I click "Yes" and the file re-opens, I get a list of repairs to the file. I close list of repairs pop-up window and go to one of the domain driven cells. The domain is no longer active (there is no longer a drop-down like in Data Validation). I then go to ArcGIS for Excel and refresh the layer, this makes the drop-down work again. 

The domain driven field of the layer table has a very long domain list of 41 items totaling 314 characters, the longest item being only 17 characters long. I found a Microsoft Community Forum post regarding data validation in Excel saying that data validation has a 256 character count limit for all of the items in the data validation list. I decided to test this out. I shortened the AGOL layer's domain list descriptions to reduce the character count below this limit. Doing so got rid of the error when I reopened the Excel file and everything works fine.

When I manually build a 314 character data validation list in Excel (no data input from ArcGIS for Excel), the data validation has no problems, it saves and reopens perfectly fine. It appears the 256 character limit does not apply to data validation lists built entirely in Excel.

However, whatever means the ArcGIS for Excel plugin is emulating the data validation based on the AGOL layer domain lists seems to be affected by the 256 character limit.

Can the ArcGIS for Excel plugin be updated so that it can handle large domain lists?

[JaromHlebasko]

I am having the exact same problem. It appears that there is probably a bug that needs to be brought up to Esri. I will try submitting a case using your excellent description above.

[ShannonCardoza]

Thank you so much @EMorgan for reporting this. We can definitely take a look at the limits we currently support for the domain list. 

@JaromHlebasko If you were able to log the bug with Esri Support Services, do you have the bug number?

If not, I can log this for tracking and post updates here as well. 

Thank you kindly, 

SC

[JaromHlebasko]

I created a case and have been on the phone with Esri about this issue. Unfortunately, this is a Microsoft Excel issue and not a defect/bug with the ArcGIS Excel plug-in. Esri  provided me a workaround to create a list in a separate sheet and reference that list in the Data Validation formula window (ex: "=NameofList").  This does work; however, if you need to refresh your map with the newest data, it will wipe out this formula every time. If there is another workaround, I will post it once Esri gets back with me. Otherwise, Esri has suggested submitting an enhancement for the plug-in for them to somehow work around the data validation issue in Excel.

[EMorgan]

Two things:

1) We opened an ESRI support case about the plug-in, and had a call with a tech. During that call, we demonstrated that the issue is in the plug-in and not in Excel. We did this by manually building a Data Validation list in Excel that exceeded the supposed 256 character limit mentioned in the Microsoft Community Forum post (see link in original post above) and it worked without a problem, so the fault is in how the plug-in emulates Data Validation, and not Data Validation itself. 

2) Until a proper solution is found, this is the workaround we are using: We are abbreviating the Domain descriptions to come in below 256 characters. Remember to include delimiters in the character count of the list. For example, we have a domain of 40 entries which means there are 39 delimiters, leaving us with 217 (256-39=217) characters for the descriptions. So the average character count for each description is 5 (217/40=5.425, rounded down). So our abbreviated descriptions are mostly 5 letter abbreviations, a few 4's and a few 6's. We know this is contrary to the purpose of the descriptions in a domain list in ESRI feature classes, but it works and it continues to work even when we refresh the data in the ArcGIS for Excel plug-in. We will continue to use this method until this issue with the plug-in is corrected.

[ShannonCardoza]

@EMorgan Thank you for the further details you also provided. Can you share the case number you were provided?

-SC

[EMorgan]

Sorry for the late reply, here it is:

Bug #: ENH-000170225

[ShannonCardoza]

Good afternoon @JaromHlebasko, thank you for the further details. Do you have the case number for your call?

-SC

[JaromHlebasko]

Yes, Esri Case #03701614

[ShannonCardoza]

Good morning! To those who posted here and logged cases with Support services, we are happy to report this bug is fixed in the current release. 

Best, 

SC