Filter Issue with Underlying Query CHAR_LENGTH

[RyanCoodey]

Hi, we are running an 11.1 Portal and no issues with the built in ExB 1.10. But using the latest 1.12 external from the Portal with an app registration and having troubles.

The Filter widget is not loading the unique value list and seeing a handful of errors in the console. The main one being it cannot execute a query against the AGS endpoint.

Example Endpoint: https://x.domain.com/arcgis/rest/services/Global/Task/MapServer/0/query 

Example WHERE: (FIELD_NAME IS NOT NULL AND CHAR_LENGTH(FIELD_NAME) > 0)

With some trial and error testing, see the issue is with the CHAR_LENGTH function. The error in Server Manager is: "'CHAR_LENGTH' is not a recognized built-in function name.]". The underlying geodatabase is on SQL Server 2016.

Any way to bypass this CHAR_LENGTH() check? Or have it use LEN() instead? The seems to be a bug depending on the underlying database that was introduced between 1.10 and 1.12.

Thanks for any help!

[JeffreyThompson2]

I have seen an issue where the filter widget will not load values after a change to the database schema. I think I cleared the error by deleting and re-adding the filter.

[RyanCoodey]

Thanks for the tip @JeffreyThompson2 , I tried re-adding the filter, tried a brand new ExB app, and tried connecting to a completely different geodatabase (Oracle instead of SQL Server). They all unfortunately error on the filter query. Seemingly because they try to use the CHAR_LENGTH() function. Only option that works for me currently is with the Portal built in 1.10 ExB filter.

Thanks again though for the idea to try!

[RyanCoodey · Accepted solution]

I am no ExB expert and also new to the standalone version but found a fix. In the file "\ArcGISExperienceBuilder\client\dist\jimu-core\index.js", search for "CHAR_LENGTH" and replace with "LEN".

Not sure what the ramifications are for this change when deploying, but for the Builder and Published Item it seems to work. It likely will break when using other flavors of databases though. Hopefully this gets a proper fix in an update.

[TonghuiMing]

@RyanCoodey Glad that you've found a workaround, and we will log this in for the details. Thanks for catching this.