Consolidate Select by Attribute Expression Modes

10-30-2019 03:49 PM
Status: Under Consideration
Labels (1)
New Contributor III

When the ArcGIS Pro "Select Layer By Attribute" window is first presented, you must select how you want to proceed before you can start building an expression.  Depending on your choice the window either goes to a mode with an expression builder interface (but where the expression you are building is difficult to see) or to a SQL mode that lets you directly type a SQL expression, but has few tools for helping you build it correctly.

The ArcMap "Select By Attributes" window operates in a single mode that contains all of the functions of both modes of the ArcGIS Pro window.  My idea is to add the SQL expression pane to the ArcGIS Pro query builder window. This would eliminate the need to initially select the mode, make it easier to see the full expression in query builder mode, and make help from the query builder widgets availabe while you type in the SQL expression pane.

Also I think the "Select Layer By Attribute" window title should be changed to "Select By Attributes".  The tool is NOT for selecting a layer, it is for selecting features from a layer. 

The revised ArcGIS Pro window would look like this:

As you build an expression, the SQL box would remain below the clause-builder widgets.

Doing this would make the initial expression-building decision, shown below, unnecessary. The new initial window includes all of the functionality of the first two choices, and the "Add expression from file" option is available with the Load button.

The graphics below show what the window looks like in its two modes in ArcGIS Pro 2.4.2.  If you are looking at the widgets, you cannot see what the current expression is.  If you are looking at the SQL pane, it does have intellisense to help you pick fields for the expression, but the drop-down list for field values is not there to help you enter them with the correct syntax.  The required "timestamp" keyword and the single quotes in the SQL expression below right were provided by selecting the time value from the drop-down list on the left.  You probably wouldn't know how to enter it this way if you did not have help from the interface.


You can't switch to the expression builder for help from the SQL pane if it contains a partial expression:

For reference, the ArcMap tool for this is below.  Everything is available in one window. You can alternate between the SQL expression pane and the clause building widgets with impunity.  The ArcMap tool is less concerned about whether a valid SQL statement is being formed than the ArcGIS Pro window is.  The ArcGIS Pro window might also need a Verify button and to stop worrying about whether a valid statement exists while the widgets are being manipulated. 



The existing SQL expression builder in ArcGIS Pro is very ponderous to use, especially for people familiar with SQL. Building one expression takes multiple mouse clicks which slows down the process. Unfortunately the 'Edit SQL mode' does not list fields and functions as it did in previous versions of ArcGIS.


Ms suggestions for improvements are:


  1. create an SQL expression builder similar to that which exists for the Label Class expression builder (and existed in previous versions of ArcGIS). It shows the fields that are available and some functions but the user is still free to type in the expression directly.
  2. if a layer or table is selected in the contents window then make that the target of the expression when the tool launches.
  3. enable select by attributes in a context menu on table windows and on layers and tables in the contents window.

Thank you for submitting this (these) ideas, Marc Dyke‌.

3. Please go to Add Select by Attributes Tool to Attribute Table in ArcGIS Pro to add your vote and comments.


if a layer or table is selected in the contents window then make that the target of the expression when the tool launches.

Don't we already do this?

1. So you just want to be able to see a list of fields and some functions like

as opposed to typing directly in the SQL box and using the intellisense, right?


For number 3, upvoted, thanks.

Ok, you got me on number 2 (I don't know why that wasn't the behavior I was seeing before).

For number 1, yes that is correct. I find the intellisense distracting and it does not provide any visual clues for field names and the like until you have already started typing. If the intelisense was enabled along with the list of fields and functions that would be fine but I would prefer to have the option to turn it off altogether.




I agree the ArcPro approach does feel clunky and requires you to click more. That initial "New Expression" button did not exist in earlier versions of ArcPro it was straight into the build expression approach. So your are basically suggesting an idea that they (ESRI) have decided was not the way to do things and now it's in this very clicky approach.

Again I agree that being able to see the expression build up, as is the way it is done in ArcMap does seem much more intuitive. As you point out swapping in/out of SQL mode you loose the ability to select values and if you don't complete a valid expression it irritatingly refuses to go back to expression builder mode.

I would prefer an ArcMap interface, it's faster, flexible and more intuitive but I suspect we won't get it. I don't know who they designed this UI for; no power user would have chosen this approach. I have never understood why they have abandoned a decade of tried and tested UI's for what it is now.



I would love if inside the SQL definition query window included buttons and "Get Unique Values" like it did in ArcMap. I prefer writing queries in SQL, instead of using the dropdowns. But I feel that the SQL option is not user friendly. I just need to remember all the field names and their values perfectly in order to type them in, which is basically impossible. 


It sounds like your idea is the same as and adds the request for a Get Unique Values button. 

I'll consult with the team on our side to see the best way to handle this. We may merge these...


I'll echo others' disappointment with Pro's SQL interface, well-documented here and other ideas. It sounds like some well-warranted reversions & revisions are in the works. ArcMap's SQL builder is rock solid. You get the same reliable, intuitive interface no matter where you are, and it gives one-click access to all fields, field values, and operators. Pro's dropdowns and "raw SQL" mode as I call it are really inefficient in comparison.

Another comment I'll make is that the textual operators (such as "is equal to" and "does not contain the text") are really slowing me down. Not only does it take a lot longer to read them, it takes longer for your brain to identify the differences and decide which one is appropriate -- not to mention, two clicks to get there.

Meanwhile, we all learned what '=' and '<' and so on mean in elementary math class. And in ArcMap's SQL builder, those symbols were there for us ready to be clicked and added into your expression.

Further, in Pro, Esri gives these newly verbose operators about a centimeter of horizontal space in most views, not modifiable unless you expand the whole pane:


That 'does,' for example, could be any of 4 options -- you don't know what your operator is without clicking on the dropdown or sitting there and waiting for a tooltip. I don't know who that's useful for. It takes a lot longer to figure out what's going on with an expression. I appreciate the attempt to make things easier on less SQL-fluent users (if that's what this is?), but this is just a step backwards in actual ease of use. I would rather just see the appropriate symbol included in the operator drop down, and for any operators that actually just slip a symbol into the search string, those % or () signs could just be included with the value (such as "%EXT" in my example,). The dropdown may still need to be expanded a bit to fit something like NOT LIKE. But that'd still be preferable to squeezing in longer phrases to a tiny box. OR, get rid of the set widths on the dropdowns. Keep each line a separate AND/OR but make the dropdowns as wide as their contents so that they read clearly as a sentence without clicking on any dropdowns (for there are cases where field title & value are longer than the box, too -- it's all stuck in a narrow pane, after all).

Like I said, we all know what '=, <, >' and all those symbols mean; for a new user to learn NOT LIKE and a few others is not too high of an expectation. I would love to have this revert to the way it was in ArcMap so I can get back to mapping with ease.


Adding in some things I'd like to see, as well.

I'd like there to be better validation for SQL when writing queries in Pro (e.g. Definition Queries, Labelling Classes, etc.).

I'm trying to set up a complex query, and all that the editor tells me is "The SQL expression has invalid syntax".

I would like it to tell me where in the expression I have invalid syntax: at least the line number, if not the actual part of the expression throwing the error.


I'd also like to echo this sentiment:

I find the intellisense distracting and it does not provide any visual clues for field names and the like until you have already started typing. If the intelisense was enabled along with the list of fields and functions that would be fine but I would prefer to have the option to turn it off altogether.

If you're in SQL mode and have started typing, there is no way to back out of it and get to a list of fields without cancelling out and starting over. The definition query builder is in a modal box; without clickable fields, you must have an encyclopedic knowledge of your dataset to work this way. I've resorted to taking a screenshot of my attributes pane and keeping it open while I write which is a hugely inefficient step backwards compared to ArcMap.