Consolidate Select by Attribute Expression Modes
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 SQL builder found when importing locations to a network analyst solver also appears in need of TLC (this is the default window size):
Field mapping also is restrained to a small list of 5 properties even when resized, and the scroll wheel is disabled unless your cursor is perfectly over the scrollbar:
Now that I am working with students again (and teaching with Pro for the first time), I am finding that it's very difficult to teach and learn SQL without the ArcMap SQL builder. It was very intuitive, and students could work off of an instruction like this with little to no knowledge of scripting:
In this case, students are working with a classified layer that is normalized by another variable (x/y), and therefore cannot use the dropdowns that the Pro expression builder gives you in its "easy" mode (unless they make and calculate a new field, which they are just now learning to do). They must use SQL, and this was pretty easy to figure out in ArcMap - it's one simple interface, no toggling, you can see what each button does to the expression, which is fantastic for learning: you get a taste of SQL even if you're using the buttons instead of typing. Not only does Pro entirely hide the SQL expression from the user in "easy" mode, but when you switch to the SQL editor, it offers zero help with building an expression for the very first time. It's literally a blank box, and students don't know what to do with this much less that it's there. Interfaces like those in ArcMap or SPSS (just a few examples) are immediately intuitive and helpful for everyone, especially students who may not know where to start with the empty text box that Pro gives you. There was nothing wrong with them; I don't know why they had to be messed with. They were purpose-built, functionality-focused, and fine-tuned. I cannot say the same of Pro.
I feel that now I have to provide a whole tutorial on SQL, since syntax errors are much easier to commit in the Pro editor vs. ArcMap. When I learned GIS (in ArcMap) and first started TAing (also ArcMap), this wasn't really necessary. The only thing we really had to worry about was making sure students had a space between operators and repeated their field clauses. Now, we have to teach the language from scratch since little help is provided by the interface. (I'm actually going to have them use ArcMap in the next class so I can at least get them some practice.) I am begging Esri to make this more usable by reverting to the ArcMap expression builder.
Pro's expression builder has just been a horrible step backwards all around; the opening lines of this suggestion (thank you @MarcDyke2) hits the nail on the head:
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.
As did this comment:
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.
This idea is 5 years old now; here's to hoping something can be done about this horrible yet common interface before another 5 pass.
