Skip to main content

Convert Autocomplete Box drop-down list to UPPER

Jeff Kapellas

I have an Autocomplete Box that queries a database table that does not enforce case. As such, the autocomplete box where clause of "UPPER(STREAM_NAME) LIKE UPPER('{0}%')"  will return separate entries in the box's drop-down list:

 

  RUSSIAN RIVER Russian River Is there a way to combine those two entries into one (i.e. similar to the SQL result of "SELECT UPPER(STREAM_NAME) FROM TABLE WHERE UPPER(STREAM_NAME) LIKE UPPER('RUS%')")? I've tried a RuntimeModification of form.Find(Of AutoCompleteBoxFormItem)("AutoCompleteBox1").QueryDisplayOutputField="UPPER(STREAM_NAME)" but that still returns two separate entries.

 

Any help would be greatly appreciated!
0

Comments

3 comments

Date Votes
  • Permanently deleted user
    Hi Jeff, 

     

    This feels like a workflow question, but I believe it's actually ArcGIS Server behaviour that's at the root of it.  Autocomplete will try to fire off a request to the ArcGIS Server REST endpoint, with "Return Distinct Values" property set to true, and "Out Fields" set to match your Display Field in the autocomplete settings.  The result is something that looks like this (https://aws-dmags2.geocortex.com/arcgis/rest/services/LosAngelesCounty/LA_Editing/FeatureServer/0/query?f=json&where=UPPER(comments)%20LIKE%20UPPER(%27te%25%27)&returnGeometry=false&spatialRel=esriSpatialRelIntersects&outFields=COMMENTS&returnDistinctValues=true) .  The autocomplete form then simply displays all of the display field values that were returned by that query.

     

    For my example service, if there are duplicate values "TEST" and "test", the autocomplete will only show one of them when using the same type of query that you specified. But I believe it's the underlying database setting that determines how ArcGIS Server addresses case sensitivity.  For this service, everything is case-insensitive and even a query like "comments = 'TEST'" will return both "TEST" and "test".  It's also worth noting that, "Return Distinct Values" requires ArcGIS Server 10.1.1+ AND only applies if supportsAdvancedQueries property of the layer is true.

     

    Unfortunately, the out-of-the-box autocomplete doesn't provide options to handle and modify the query results before displaying them.  So the only way to remove these case-sensitive duplicates is to be able to construct a query on ArcGIS Server that gets the desired result right away.  I would test on the layer's REST endpoint there rather than trying to work from the RuntimeModifications in the workflow.  Then I would move to confirming your ArcGIS Server version/settings, looking into database settings to change how case sensitivity is handled by ArcGIS Server, and/or updating the underlying data so that the casing is consistent.

     

    Hope that helps,

     

    -Amanda
    0
  • Jeff Kapellas
    Thanks for your response, Amanda. That's kind of what I expected. I was hoping not to have to alter the underlying data, but it looks like that might be the easiest course of action. Thanks again!
    0
  • Permanently deleted user
    it would be better practice to have clean data - e.g there shoulld not be both RUSSIAN RIVER and russian river in your data
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post