idea Consolidate Select by Attribute Expression Modes in ArcGIS Pro Ideas

Improve SQL Expression Builder

MarcDyke2 — Fri, 19 Feb 2021 18:24:41 GMT

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:

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.
if a layer or table is selected in the contents window then make that the target of the expression when the tool launches.
enable select by attributes in a context menu on table windows and on layers and tables in the contents window.

Re: Improve SQL Expression Builder

KoryKramer — Wed, 21 Nov 2018 16:35:27 GMT

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?

Re: Improve SQL Expression Builder

MarcDyke2 — Wed, 21 Nov 2018 17:19:41 GMT

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.

Re: Improve SQL Expression Builder

KoryKramer — Wed, 21 Nov 2018 17:21:31 GMT

Thanks

Consolidate Select by Attribute Expression Modes

Gerald_Daumiller — Wed, 30 Oct 2019 22:49:18 GMT

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.

Re: Consolidate Select by Attribute Expression Modes

DuncanHornby — Fri, 01 Nov 2019 12:52:59 GMT

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.

Definition Query Buttons

MariePichlerGalarneau — Thu, 11 Aug 2022 13:46:25 GMT

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.

Re: Definition Query Buttons

KoryKramer — Sat, 13 Aug 2022 21:05:25 GMT

It sounds like your idea is the same as https://community.esri.com/t5/arcgis-pro-ideas/improve-sql-expression-builder/idi-p/931226 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...

Re: Improve SQL Expression Builder

wayfaringrob — Mon, 25 Sep 2023 21:30:04 GMT

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.

Re: Improve SQL Expression Builder

AlfredBaldenweck — Fri, 21 Oct 2022 16:37:26 GMT

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.

Re: Improve SQL Expression Builder

wayfaringrob — Tue, 29 Nov 2022 17:26:53 GMT

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.

Re: Improve SQL Expression Builder

wayfaringrob — Tue, 10 Jan 2023 21:19:26 GMT

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:

Re: Improve SQL Expression Builder

Bud — Sun, 26 Mar 2023 18:20:59 GMT

@AlfredBaldenweck and @wayfaringrob

Some specific ideas related to your comments:

Definition Query window: Show full comparison operator by default
Disable autocomplete in SQL expression builder
- ENH-000156091: Add the option to disable autocomplete when writing SQL queries in ArcGIS Pro
Definition Query SQL shouldn't autocomplete “when “ to “WHENEVER”.
SQL Expression: Typing a period shouldn't autocomplete a word
Definition query should show the descriptive error message from the database, not a generic error message

Re: Consolidate Select by Attribute Expression Modes - Status changed to: Under Consideration

TanuHoque — Tue, 13 Jun 2023 20:29:09 GMT

Thanks for the suggestion @Gerald_Daumiller. My apologies as somehow I missed this idea. There are two main issues as I interpret.

the initial landing page --> I'm pretty sure you know that it is removed a few releases ago. I'm just adding this for any future references.
consolidate both design mode and sql mode. I personally thing that is a very good suggestion. I've moved the idea to 'under consideration'. We will keep monitoring this idea for any future inputs/suggestions.

Thanks

Tanu

Re: Consolidate Select by Attribute Expression Modes

MatthewProctor-Brown — Fri, 23 Jun 2023 00:05:43 GMT

While implementing this it would be nice if it included support for the BETWEEN operator, so the Expression Builder doesn't turn off when you use it.

Re: Consolidate Select by Attribute Expression Modes

wayfaringrob — Wed, 28 Jun 2023 19:20:21 GMT

I've never been a fan of the dropdowns that Pro uses here, especially replacing operators with text that you can't even read. I'd rather see a complete reversion.

Re: Consolidate Select by Attribute Expression Modes

TanuHoque — Wed, 28 Jun 2023 19:39:41 GMT

hi @wayfaringrob

would you mind let us know what doesn't work for you? And please give us suggestions that would live to see and improve the user experience..

thanks

Re: Consolidate Select by Attribute Expression Modes

wayfaringrob — Wed, 28 Jun 2023 19:41:26 GMT

@TanuHoque thanks for reaching out. I've authored several individual ideas related to this on here.

Re: Consolidate Select by Attribute Expression Modes

KoryKramer — Thu, 29 Jun 2023 13:06:56 GMT

@TanuHoque

Here are a couple from rburke:

https://community.esri.com/t5/arcgis-pro-ideas/expression-builder-keep-expression-field-active/idi-p/1221615

https://community.esri.com/t5/arcgis-pro-ideas/enlarge-expression-box-in-select-by-attributes/idi-p/1177722

Re: Improve SQL Expression Builder

wayfaringrob — Mon, 25 Sep 2023 21:33:28 GMT

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.