Product
Support
Everything Else
A Short Tutorial for the macOS Power Query
Introduction

The Power Query window (the Power Query Term Specifier) has been completely redesigned. The new interface takes advantage of many features available in macOS.

Because of the radical departure from the Power Query seen in Classic Helix, this technote provides detailed information on using the macOS Power Query. This technote replaces the information found in section 9.4.3 of The Helix Reference.

In the tutorial below, you can click each image to see it at full-size.

About Power Queries

A power query can be used to construct a complex search statement using one or more logical search expressions. Helix then retrieves just those records in which field and/or abacus values meet the criteria in the search statement. Unlike the Form Query, which is limited to joining multiple statements with and logic, the Power Query can use and, or and not logic, nesting groups within groups.

The macOS Power Query transparently displays queries that were created in Classic using the new interface.

Helix 6.x users: the Classic Power Query is limited to using only and and or operators with limited nesting options. A macOS Power Query that uses advanced logic should function correctly in Classic Helix, but the “Completed Search Statement” can not be viewed in the Classic Power Query window. Clicking the “Clear” button removes the Power Query created in macOS so one can be defined in Classic.

The Power Query Window

To the right is the new macOS Power Query window, as seen with an empty query. The dialog starts out with only a single line popup control and expands as needed as more information is required.

To use a power query, you do the following:

  1. Begin by selecting an item (field or abacus) to be searched from the Searchable Items popup menu.

    The Search Operators and Search Value sections do not appear until you choose an item from the Searchable Items popup. Once that selection is made, the Search Operators popup menu appears, containing operators appropriate for that data type. To its right a field for typing a Search Value also appears, and if the collection designer has created predefined search values, a Search Value popup menu from which a predefined value can be chosen is shown.

  2. Choose an appropriate operator from the Search Operators popup menu.

    The operators change depending on the data type of the Searchable Item selected. For example, when a date or flag item is being searched, the Starts With, Contains, and Ends With operators are not shown.

  3. Type a value to which the first item is to be compared into the Search Value field, or select a predefined value from the Search Value popup menu, if provided.

  4. If you want to define additional search criteria, click the button to the right of the search statement, immediately above the position into which you want the new statement inserted. To remove a statement from the query, click the button to its right. See “Nested Logical Operators” below for information on adding multiple Search Operators.

    The Power Query window grows as you add additional statements, and shrinks as you remove them. You can resize the window horizontally, but not vertically.

  5. When you have finished defining your search criteria, click the Apply button. Views to which this Power Query are attached now display only the records that meet your criteria.

    Clicking the Revert button discards all changes made since the Power Query window was opened, or since the last time the Apply button was clicked.

    You can close a Power Query window at any time. Views to which this Power Query are attached continue display only the records that meet the Power Query criteria whether the window is open or closed. To remove the restrictions, the Power Query must be cleared.

Invalid Search Statements

The Power Query window prevents invalid search statements (such as Date = yes) in two ways.

  1. The Apply button is disabled whenever the completed search statement is invalid or incomplete, and the invalid item is shaded using the invalid field shade color used on regular views. (This color can be changed by editing a preference.)
  2. If the user attempts to close the Power Query window while the completed search statement is invalid, a dialog appears, warning that the query is invalid. You can choose to Revert to the last saved state (when the Power Query window was opened or the Apply button last clicked) or Cancel and correct the invalid search statement.

Tooltips

The macOS Power Query window features extensive tooltips. Every control in the window has an explanatory tooltip that explains the item’s purpose. Since the & buttons allow you to add and remove both logical operators and search statements, these tooltips also describe the effects of option-clicking.

Nested Logical Operators

The Power Query displays nested query logic by indenting nested statements. The top level statements are flush to the left of the window, with each level of nesting indented slightly. When performing a search, Helix logically evaluates all expressions shown at the first level, followed by all expressions at the second level, and so on.

To remove a statement from the query, click the button to its right. Clicking on the button next to a logical operator removes that logical operator and moves all nested statements out one level. To remove a logical operator and its nested statements, option-click the button next to the logical operator.

Clicking the button next to a logical operator adds another search statement at the same level as the logical operator. Option-clicking the button adds a new logical operator inside that logical group.

Clicking the button next to a search statement adds another search statement at the same level as the search statement. Option-clicking the button adds a new logical operator at the same level, nested inside this search statement’s logical operator.

These options allow the user to create very complex, deeply nested logical operations in the Power Query window, far surpassing the capabilities of the Classic Power Query. Any query logic that can be constructed in an abacus in Design Mode can be created using the macOS Power Query.

A macOS Power Query that uses advanced nesting should function correctly in Classic Helix, but it can not be viewed in the Classic Power Query window. The “Completed Search Statement” area appears to be empty. Clicking the “Clear” button in the Classic Power Query window removes the macOS Power Query so a new one can be defined.

Natural Language Logical Operators

The Power Query foregoes the ‘boolean’ operators (and, or, and not) for their natural language equivalents: all, any, and none. User testing shows these to be less confusing for the average person.

Each logical operator is followed by the static text: ‘of the following are true’ which, when combined with the indenting that is used to indicate which lines are part of which logical groups, further enhances the natural readability of the query.

Permanent Power Query Criteria

A collection designer has the ability to attach permanent criteria to a Power Query. These criteria can not be changed in User Mode, and can not even be seen. The macOS Power Query window gives indication when permanent restrictions are attached by changing the label at the top of the window from Set Search Criteria to Add to Predetermined Search Criteria. Contrast this to the Classic Power Query, which gives no indication whatsoever that permanent criteria are attached, a potentially confusing situation.

Power Query is Always Cold

In Classic Helix a Power Query can be either hot or cold. (A hot query recalculates the attached views whenever any search criteria is changed.)

In this regard, the macOS Power Query is always cold, as it does not recalculate attached views until the window is closed or the Apply button is clicked.

(If you are pining over the loss of the ‘Hot’ Power Query, do not despair. All you have to do is hit the Enter key on the keyboard after making a change. That will trigger the Apply button, causing the query to refresh immediately.)

Demo Movie

Here is a short movie showing the new Power Query in action.

See this and other video tutorials on the QSA ToolWorks YouTube tutorial channel.