Query Wizard… Little Things Add Up

By: Gavin Gross

With all the changes made to the Query Wizard in version 22, it is almost certain you will miss at least one change that could make your life a little easier. So let’s take a look at the different tabs and some of the smaller enhancements on the tabs that may be just what you have been waiting for.

Query Tab

We added a Goto Field function for when you have trouble finding a field in your query. To access this, simply right-click any field on any table, and select Goto Field. This will take you directly to where that field is being used! If the field is used more than once, you can continue to cycle through the fields with Ctrl+Enter.

The responsiveness of this tab has been addressed in many different areas. Checkmarks now also appear next to fields that are used in expressions. Joins update immediately with the scroll wheel. Right-clicking anywhere in the grid puts focus on the cell you are hovering over. All of the right-click menus do a better job loading their content based on exactly where you are clicking. Example: Add Lookup Table or Add DDCombo Expression only appear when you right-click a field with the corresponding binoculars.

Finally, we added “update all” functions in the Header column of the grid for Show & Filter. Right-clicking in the Show cell and making any selection will update every field’s Show value. When right-clicking any of the Filter rows in the header column, you will be presented with the option to Copy\Paste\Clear the entire row. Be forewarned, pasting a filter row will not clear cells, unless you are pasting a new filter into that cell. For example, I have 3 fields in my query: House_Number, Address, & City. Filter 1 has a filter on House_Number, and Filter 2 has a filter on Address. If I copy the Filter 1 row and paste it into the Filter 2 row, I will now have a filter on House_Number and Address for Filter 2.

Value Editor – Filter Tab

To get to the next value field on the Filter tab, you can now use the Enter button. Combo boxes on both the Value and Filter tabs have been updated to match the show/sort rows of the query grid, which allows keyboard-centric users to maintain focus. The Lookup form also received attention when we added the ability to select multiple rows by shift-clicking and the ability to add count for a selected item.

Value Editor – Expression Tab

On the Expression tab, when adding a function containing Table.Field, you no longer have to highlight the whole string to replace it with the field you find. You can now just click anywhere in the string. When you select the field to use, the program will make the change for you. For anyone wanting to know if a new expression is valid, you can check it immediately with the Validate button (instead of waiting until you switch to Preview and realize something is invalid).

Layout Tab

There have also been some really useful tweaks to the layout page. One improvement I wanted to talk about is a little helper for clicking on those tiny section separator lines. We couldn’t do the most obvious solution of making the lines bigger, because that would bump the spacing on every saved query that is loaded. Our solution was to utilize the section title divider lines highlighted in the picture. Just click anywhere around one of those lines, and your cursor will perfectly position on the corresponding section line.

Another little addition extended the functionality of @Date/@Time and also added @Year. If you have a query where you have to update the dates in a label every time you run it, this might be a very welcomed addition. Previously @Date and @Time would always display the current date or time. We added a plus or minus feature to those fields, which allows you to add or subtract years/months/weeks/days/hours/minutes/seconds from that current date or time. So, if you wanted to have a label that said last year to this year, you could write a label that says “@Year-1y to @Year” To use this function, simply add a plus (+) or minus(-) after Date/@Time/@Year, followed by numbers to indicate the amount of time and a letter to indicate the measurement of time.

Preview Tab

There are two updates to the Preview tab that I would like to point out. First, we updated exports to Excel to allow result sets with more than 65,535 rows. The only caveat is that after every 65,535 rows, we put it on a new sheet within the Excel file. Second, we added search functionality to the grid. Pressing Ctrl+F on the grid will open a search bar on the top-right corner of Query Wizard.