USING MAINTAINABLE QUERIES (BETA)
This document will be superseded by the User Guide after all functions are final.
Maintainable Queries requires tenant level 3.578.0
Maintainable Queries
OVERVIEW
Maintainable queries integrate many of the layout features of queries with the maintenance features of details. They do not change your existing queries. However, maintainable queries will introduce some new features to existing queries, features which will be described in this document.
The essence of a maintainable query is that you can update data in the query without using an action or opening the details. You might think of this as “spreadsheet entry” only it’s not a spreadsheet. Some call it “tabular entry” because you can tab from column to column typing data as you go.
Maintainable queries cannot stand alone; they must be inside a dashboard. A new dashboard layout called Maintainable Queries has been added for the purpose. This means that to launch a maintainable query you must launch a dashboard. To navigate to a maintainable query (such as with an action) you must navigate to a dashboard. Maintainable queries are inseparable from dashboards.
DASHBOARDS
A dashboard is a container for queries. Until now it has had the same layout options as details, but now it has a new one called Maintainable Query.
SECTIONS
The maintainable query layout is one single column of query sections. You can optionally remove the space in-between so they look and act like one big query. There are three kinds of query sections:
- Header section – This is an optional section. You may have more than one. Header sections are not maintainable and they will always appear first in the query stack. Headers will stay visible at the top of the browser when you scroll the data section up or down.
A header query allows you to have soft-coded column names. This is useful when the column headings must be based on something variable, such as time. The data displayed in a header query could come from a source that allows calculations such as logical properties or an SQL view.
- Data section – This is the one you can maintain. There can only be one. It will always below the header section(s) and above the footer section(s), if any.
- Footer section – This is an optional section. You may have more than one. Footer sections are not maintainable and they will always appear last in the query stack. Footers will stay visible at the bottom of the browser when you scroll the data section up or down.
The most common use for a footer section is column totals. Multiple footer rows means you could have multiple rows for different totals. For example, you might want to have total amounts in different currencies, or one row for total amounts and another row for total hours.
When you define a dashboard section you will see three new features for maintainable queries:
- Section type
- Suppress headings
- Spacer lines
These all appear at the bottom of the section definition:
MAINTAINABLE SECTION TYPE
As you would imagine from the foregoing, there are three section types: Header, Data and Footer. The one you will always have is Data. The other two are optional.
The data section is the one you can maintain, and you can only have one. For many dashboards it will be the only section.
SUPPRESS HEADING
When you only have a data section you will keep the heading. When you use a header section you will generally suppress the data section’s heading row.
SPACER LINES
You can use spacer lines to cause the different sections to appear separate (with spacer lines) or joined (without spacer lines). For example, by removing the spacer line you can make the heading section look like it’s part of the data section – like it’s all one query.
In the illustration below shows a maintainable query with header, data and footer sections. On the left you see that all the queries have their headings and they are all separated by a spacer row. On the right you see the same dashboard with headings suppressed and spaces removed. The effect is to make all three sections appear as one.
SPECIAL DASHBOARD BEHAVIOR
When you use the Maintainable Query dashboard layout, certain special behaviors are added:
- Horizontal scrolling – The entire query stack will scroll together horizontally, regardless of suppressed headings or spacer lines. When you scroll left or right everything moves together.
- Column alignment – The columns of all the query sections will be aligned vertically. When you adjust a column width it will affect all query sections in order to maintain alignment.
Header and footer queries must be designed for the intended data query. That is, they should have the same number of columns, and the same pattern of data and spacer columns. (Spacer columns are described below.)
QUERY ENHANCEMENTS
As mentioned earlier, some maintainable query features have been added to regular queries.
SPACER COLUMNS
The View Properties toolbar has a new tool to add a spacer column. A spacer column separates the query vertically. It has no header and no data – no rows at all – it is just space. In the below illustration you see that clicking Add Maintainable Query Spacer Column puts a <spacer column> indicator in the column list. 
In runtime the spacer column looks like this:
Spacer columns apply to the query in which they are defined. That means you will need to add spacer columns to any headers and footers. This is what we mean when we say that headers and footers must be designed for the intended query. They should have the same number of columns with spacer columns in the same place so they will align.
MAINTAINABLE QUERY SETTINGS
When you define a query view property, such as to change the default heading, you will see some new settings for maintainable queries:
- Heading Groups
- Initial Display Option
- Maintainable
HEADING GROUPS
In the illustration below you see two heading groups, 2021 and 2022. These appear above the normal headings
To create a heading group you assign the same value for contiguous properties. In the illustration below you can see that we specified the heading group 2020 for twelve contiguous properties, and the group 2021 for twelve more, etc.
You may choose to group certain headings together and not others. You do not have to define a heading group for every property in the query.
INITIAL DISPLAY OPTION
In a normal query it is assumed that you will want to show all the properties starting with the leftmost column. In a maintainable query this is not assumed. Instead, you will be presented with the following options:
Default – Columns with this value will behave like a normal query.
Pinned – Maintainable queries do not allow the user to pin and unpin columns. Instead, the application developer chooses which column(s) to pin. They behave exactly as pinned columns in a regular query, i.e., they appear at the far left and don’t move when the user scrolls left and right.
First Visible – This allows you to specify how far to the right you want the query to be scrolled when the user first sees it. There can only be one first visible column. The following two illustrations show how this works.
Here Jan 2021 is defined as the first visible column.
This is what it looks like when it is first presented. Jan 2021 is the first column (after the pinned column):
Hidden – In a maintainable query the end user can choose to hide or show columns. This setting determines which columns start out hidden.
In the following query the current year is 2021. We allow the end user to see what last years numbers were by hiding the columns. Each month has last year’s month hidden next to it:
At runtime the visible columns are shown. Each column has a hide/show tool:
The hide/show tool lets the user check boxes to show or uncheck them to hide. In the illustration the Name column is lighter because it is pinned. Pinned columns cannot be hidden.
Below you see the result when the Jan 2020 column is shown:
MAINTAINABLE
This is a rather important feature, because it determines which columns in your maintainable query will actually be maintainable. For this first release we have only tested strings and numbers. The default is No, so you will need to change it to Yes for those columns you want to maintain.
USING MAINTAINABLE QUERIES
A maintainable query is a dashboard, so you must put your dashboard launcher on a workbench or navigate to is from an action. Once it is displayed you can interact with it as described below.
TABULAR ENTRY
The most obvious benefit of maintainable queries is in the name: the user can maintain them directly, without using an action and without navigating to details.
To do this simply double-click the cell you would like to update. You will see it change to edit mode:
- The data is left-justified
- The blue background shows it has focus
- There is a blue line along the bottom of the cell
Once you are in edit mode you can use your keyboard to enter data. You can tab to another cell and type data there, too. When you press Enter the data is saved.
Data is saved row-by-row. Typing and tabbing does not save the data. The data is saved when you do one of these:
- Press Enter
- Tab out of the last maintainable cell in the row
- Paste data into a cell or group of cells
It may be helpful to think of a row as another way of editing details. When you edit details you can tab from property to property but nothing is saved until you press Save. So it is with a row in a maintainable query. One difference is implied saves, such as when you tab out of the last maintainable cell or paste data into a cell.
When you edit details and press Save you perform a standard edit action. When you type data into a maintainable query and press Enter you also perform a standard edit action. If you override the standard Edit action in order to add your own function – such as to validate data – the same function will execute from a maintainable query.
If you double-click a cell to go into edit mode and then change your mind, simply single-click a different cell. The cell you click will turn blue but it will remain in display mode.
COPY/PASTE WITHIN THE MAINTAINABLE QUERY
You can select cells to copy from a maintainable query. To select one cell simply click it. You do not have to be in edit mode.
- To select multiple cells click and drag. You can select one or more contiguous cells from any section including a header or footer.
- You can select multiple rows from the same section. You cannot select multiple rows across sections.
Once you have selected your cell(s) use Ctrl + C to copy.
You can use Ctrl + V to paste cells into the maintainable query.
- You paste what you copy.
- If you copy one cell you will paste one cell.
- If you copy three cells in a row you will paste three cells in a row.
- If you copy two lines you will paste two lines
- You do not have to select the paste region. One cell is enough.
Data is saved when you paste. If you have added validation to the standard edit you will get the same error messages you would get if you typed bad data and pressed Enter.
COPY/PASTE TO EXCEL
It is possible to copy from a maintainable query into Excel using the same keystrokes:
- Ctrl + C to copy from the maintainable query
- Ctrl + V to paste into Excel
However, it is not possible to copy from Excel and paste into the maintainable query.
