Query Subsystem 

Query Definition

 

Query contents are defined in the Query Definition window. This window is used to set up link files, define columns, edit the Query Header and set up selection criteria. After the query is defined, it can be tested and saved.

The Query Definition window launches after defining the Query Header for a new query or when selecting an existing query object in the Library Object Selection window. It can also be accessed when using the NOMADS Panel Designer to assign a query to a List Box, Drop Box, Multi-Line or Grid control. See Assigning a Query.

Query Definition

The Query Definition window consists of the following:

File Elements

A list of files (the main data file/table and any defined link files) and their elements.

Icons next to each element indicate whether the field is a string, numeric or date (i.e. DATE class) field. (Manual files have no elements listed.)

Array elements are displayed with their dimensions in braces; e.g. Month${1:12}.

Items can be selected from this list for addition to the Columns list for display in the query. Select an item by double-clicking on it, by highlighting it and clicking a selection button, or by selecting the Add option from the right-click popup menu associated with the item. Popup menus associated with the main file and cross-reference files also allow you to add fields from the respective files.

When an array is added as part of an "add all fields" or "add all fields in file" scenario, all the elements in the array are automatically added to the Columns list. If the array alone is selected from the File Elements list, the Select Array Element window is displayed to allow you to select all elements or select a single array element by specifying its subscripts.

    

This window consists of the following:

Select element range

Click the drop-down arrow for selections:

All Elements

(Default) All elements defined for the array will be selected.

Single Element

A single array element will be selected by specifying a valid range for each of its subscripts.

Array subscript 1
Array subscript 2
Array subscript 3

(Available when Single Element is selected)

Enter a valid range for each array subscript. The number of subscripts shown is based on whether the array was defined with one, two or three dimensions.

OK

Adds all elements or only the specified array element to the Columns list. If an invalid range was entered for an array subscript, a message will display.

Cancel

Cancels any changes and closes the Select Array Element window.

(Support for array elements in the query was added in PxPlus 2022.)

Columns

A list of columns selected from the File Elements list.

Columns can be a simple element (i.e. field/column name such as InvoiceNumber) or a formula (i.e. a PxPlus expression). When a file element is initially added to the Columns list, default characteristics such as title, column width, format, etc. are set. These can be modified later using the Column Options window or by double-clicking the column item.

Selection Buttons

The selection buttons between the lists are used to add all or only selected items from the File Elements list to the Columns list. They are also used to remove all or only selected items from the Columns list.

For manual files, select a file and click the selection button to invoke the Column Options window where you can manually define the column to add.

Select as 'Hidden'

When this check box option is selected, any items selected from the File Elements list are added to the Columns list as initially hidden; i.e. the column is initially hidden at run time until the user selects it to be shown.

This option can also be toggled for a specific element by right clicking on the element in the Columns list and selecting the Hide/Show option or by using the Column Options window.

When using Query+ Toolbar, Hybrid, Menu or Drop Query views (see Query View option), columns can be set to be initially hidden or shown by clicking the Test/Design toolbar button to display the query and selecting the Show/Hide/Reorder Columns item from the Columns run-time option to set the column visibility. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Choose initial sort column

(Query+, Not Applicable to Drop Tree Query)

Click the link to invoke the Select Column Sort Order window to specify a column to sort by (ascending or descending) after the query is loaded. This overrides the default sort sequence, which is determined by the key selection in the query header. The column sort affects the initial load only; subsequent reloads will maintain the last column sort used in the query.

    

This window consists of the following:

Select Column for Initial Sort

Click the drop-down arrow to select a column for the initial sort.

Sort Order

Specify the initial sort order. (Default: Ascending Order)

Note:
Setting an initial sort column requires that all the data must be loaded into the query before it can be sorted; therefore, performance enhancing features, such as Load on Demand and Background Loading, are not available.

(The Choose initial sort column option was added in PxPlus 2022.)

Move Up
Move Down

Buttons (below the Columns list) that are used to change the order of the items in the Columns list.

The order of the columns can also be set for Query+ Toolbar, Hybrid, Menu or Drop Query views (see Query View option) by clicking the Test/Design toolbar button to display the query and selecting the Show/Hide/Reorder Columns item from the Columns run-time option to reorder the columns. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Close

Exits the Query Definition window. If any unsaved changes are detected, you will be prompted to save the changes.

(The Close button was added in PxPlus 2019 Update 1.)

Query Definition Toolbar

The Query Definition toolbar consists of the following functions:

Save

Saves the current query definition. (If the session terminates abnormally without saving, the unsaved changes can be recovered the next time you load the definition.)

Test or Test/Design

(Classic Query and Query+ Drop Tree Query)

Tests the current query definition.

(Query+ Toolbar, Hybrid, Menu and Drop Query)

When testing the query, many of the run-time features are disabled, such as Favorites, Filters, Formulas and Profiles. See Run-Time Query.

The Columns feature is enabled to allow the developer to use the Show/Hide/Reorder Columns option to initially show and hide and reorder the columns. Column widths can also be adjusted by dragging the edge of the column header to the desired width. The size of the query panel itself (except for the Drop Query) can also be resized by dragging the edges of the panel. These new settings can be saved and incorporated into the query definition by clicking the Save + Exit button when exiting the query. Be sure to save the current query definition to ensure that all changes are saved.

Example:

Below is an example of a query displayed in Test/Design mode:

    

(The Test/Design button was added in PxPlus 2023 Update 1.)

Header

Launches the Query Header Definition window for editing header information.

Filter

Launches the Query Selection Criteria window for setting up selection criteria.

Security

Launches the Object Security Definition window for setting up top-level security for the query. See Query Security.

(The Security button was added in PxPlus 2021.)

Print

Prints the details of the query definition.

Add Link
Update Link
Remove Link

Launches the Query File Link window for adding, modifying and removing a link to a cross-reference file.

Column Options

Launches the Columns Options window for defining the column title, width, format, etc., as well as the Field Definition for manually defined files.

Add Formula

Launches the Query Formula Definition window for adding or modifying a formula.

Define Chart

(Available when Show Query+ Options check box AND one or more Columns are selected)

Launches the Chart Wizard for defining simple charts based on the columns of a query. The results are public developer AutoChart definitions that can be used to generate and load data into a Smart Chart. See Smart Controls.

(The Define Chart button was added in PxPlus 2019.)

Show Query+ Options

When selected, only query options available to Query+ displays are enabled. In addition, the Test button will generate a Query+ panel.

If not selected, options and test display will be those of the Classic Query.

For information on the modes of query display, see Query Subsystem.

Note:
The Show Query+ Options check box is not shown for Query List definitions.

Query Definition Menu Bar

The Query Definition menu bar consists of a Projects menu with the following selections:

Projects

Lists options for projects:

Create New Project

Launches the Create Project dialogue for entering a new project for the current working directory. Click the Query button to select a different working directory.

Add to Project

Launches the Add to Project dialogue for adding the current task to an existing project that is selected from the Project drop box.

To manage all the tasks within a project, see Project Maintenance.

For information on adding tasks to a project from other locations, see Adding Tasks to Projects from Other Locations.

Quit

Closes the Query Definition window. If any unsaved changes are detected, you are prompted to save the changes.

Query File Link

Query File Link allows additional data to be retrieved from associated cross-reference data files or database tables. For example, if the Customer data file contains a classification code, but the associated class description has to be retrieved from a ClassInfo data file, you can define the ClassInfo file as a link file using the classification code from the Customer data file as its key.

To launch the Query File Link window, click the Add Link button on the Query Definition toolbar to define a new link. To modify or remove an existing link, highlight the link file in the File Elements list and then select either the Update Link button or the Remove Link button on the Query Definition toolbar.

The Query File Link window displays a list of all currently defined links, if any, for the selected query. Link definitions can be added, modified or removed.

A link definition consists of the following:

(Select a Link Definition)

Click this button (in the first column) to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.)

Alternatively, you can select the Pre-Defined File Links button in the bottom left corner.

Expr

Select this check box if the entry for Link To File/Table is an expression.

Link To File/Table

Name of the link file/table. This can be defined as a fixed value or an expression (see Expr above), which is entered or selected from the drop-down list of existing files/tables:

For PxPlus Data Files

You can select your file from a list of files defined in the NOMADS Data Dictionary, enter the name of a PxPlus View, or enter the name of a physical file path or prefix file reference.

For External Database Files

Your selection must be a table. If the query's main file is a prefix file reference to an external table, then you can use prefix file references instead.

Access Key

Key to be used to read a link file. Select a key from the drop-down list.

Key Expression

PxPlus expression consisting of field(s) from the main file (or other link files) and literals to build the key value to read the link file. A key expression may be string or numeric.

Examples:

     TRN_CUSTID$

     "D"+FLD#2$(1,3)

     PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+STR(EmployeeNum)

     KEY(__fileFN,KNO=0,KEY=Company$:Department$:EmployeeNum)

Multi-Segment Keys

When dealing with multi-segmented keys (i.e. keys made up of more than one field), you can use a + (plus sign) to concatenate multiple string segments of a key when entering a free-form expression. Be sure to add the correct padding logic to the initial key segments. By default, the initial segments of native PxPlus keys are padded with $00$ characters, and the final segment is not padded. Such a key definition might look like this:

     PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+STR(EmployeeNum)

Individual applications can pad key segments with other characters, such as spaces; therefore, knowledge of the key architecture of individual files is a must. Knowledge of segment characteristics is not necessary; however, if the key expression uses the KEY(__linkFN,KNO=n,KEY=FLD1$:FLD2$) format where __linkFN is a special channel variable used by the system when evaluating the key, n is the key number used by the file, and FLD1$:FLD2$ are the key segment variables. The latter format is also more flexible as it can handle changes in key segment length if keys are updated for a file. In addition, this format allows a mixture of string and numeric segments.

Key Expression Builder

An easy way to build a key expression is to use the key builder utility. This is invoked by clicking the dotted button in the cell associated with this option.

    

The key expression is built by specifying key segments to match the target key definition. These segments can consist of fields from the parent file, literal values and variables. Partial items can be defined, and segments can be padded, have characters stripped, or be converted to upper/lower case.

In the case of multi-segment keys, a Generate a KEY=F1$:F2$ expression check box option is available:

 • 

If it is not selected, the key segments will be concatenated, and individual key segments would have to be padded or manipulated appropriately.

 • 

If it is selected, then a key expression in the format KEY(__linkFN,KNO=n,KEY=FLD1$:FLD2$) will be generated where the segments need not be manipulated. (For an explanation of the contents of the generated key, see Multi-Segment Keys.)

Simple numeric keys can be built using a numeric field. Multi-segment numeric keys should be built using the Generate a KEY=F1$:F2$ expression option.

(The Generate a KEY=F1$:F2$ expression check box was added in PxPlus 2023.)
(Support for numeric key definitions when defining query links was added in PxPlus 2023 Update 1.)

Record Prefix

Prefix to affix to field names to identify the file to which they belong.

Example:

If the CUSTOMER file is given a Record Prefix of CST, then the ID and NAME elements of the CUSTOMER file would be identified as CST.ID$ and CST.NAME$.

Read Record

(Valid Only for Native Files with No Embedded Dictionary)

Select this check box if the file is to be read as an unparsed record.

Connect Options

Connection options for opening an external database table.

Security

Click the dotted button associated with this option to launch the Object Security Definition window for setting up link file level security for the query. See Query Security.

(The Security column was added in PxPlus 2021.)

Pre-Defined File Links

Click this button to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.)

Alternatively, you can select the link button in the first column of the grid.

(The Pre-Defined File Links button was added in PxPlus 2021 Update 1.)

The sequence of link files/tables in the list defines the sequence in which they are to be read. If you have multiple levels of link files, for example, the main file links to File_A, which links to File_B, then File_A should be read before File_B and thus precede it in the list. To rearrange the order, use the Move Up/Move Down buttons.

Changes that are applied in this window will be applied to the File Elements and Columns lists on the main Query Definition window. The exception to this is that columns belonging to deleted links are not removed from formulas.

Column Options

For a Data Dictionary/View Definition/Database Table

When you select an item from the File Elements list in the Query Definition window, it is placed in the Columns list with default values for its title, format width and query derived from the data dictionary, view or table definition.

To change the values for a selected element, launch the Column Options window by using one of the following methods:

   •  Click the Column Options button on the Query Definition toolbar.

   •  Double-click on the element.

   •  Right click on the element and then select Update column properties from the popup menu.

For a Manually-Defined File

When you select a manually-defined file from the File Elements list in the Query Definition window and click the selection button, the Column Options window is presented for defining the data element in terms of field number, offset, size and data type. See Field Definition and Manual Field Definition.

The Column Options window consists of the following:

Field Definition

(Only Available for Manually-Defined Files)

Field No

Ordinal position of the field to be displayed; i.e. range 1 to n. Use Fld#0 (zero) to refer to an external key to a manual file.

Offset and Size

Define sub-strings using the field Offset (integer, range 1 to n) and the Size of the sub-string (integer, number of characters/bytes). NOMADS displays manual fields in the Columns list as Fld#n (where n is the Field No) or as Fld#n(o,z) for sub-strings (where o is the offset and z is the size).

Type

Toggle String or Numeric as your data type.

Title

Heading that users will see at the top of the column at run time (either a Fixed value, string Expression or Message Library Reference).

Width

Column width in columns. This can be adjusted as necessary, depending on the selected font.

Column widths can also be adjusted in Query+ Toolbar, Hybrid, Menu and Drop Query views (see Query View option) by clicking the Test/Design toolbar button to display the query and dragging the column header to the desired size. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Alignment

(Query+ and Drop Query)

Alignment of the value within the column. Settings include Default, Left, Center and Right justification. The Default setting for text values is Left justification and Right justification for numeric values.

Note:
Alignment affects text displays only so that just the column title of columns displaying images, percent bars, etc. are affected.

(The Alignment option was added in PxPlus 2018.)

Format

Can be either Mask or Program.

Mask

PxPlus format mask for the column's data output (fixed value or string expression); e.g. a phone number mask such as 000-000-0000.

Program

Name of the program called to format the display of the string data in this column; e.g. *win/date;display. On the ENTER statement of the sub-program, include one argument (value of the column is passed by your query.) Fixed text or an Expression can define the mask or name the program.

Display Option

Click the Define button to launch the Query Column Display window where you can assign visual attributes, such as bold text, text color, highlight color, or an image, to a column. See Query Column Display below.

Sort Options

(String Only, Query+, Drop Query and Classic Query with Sort by Column Option)

The following sort options can be assigned to a column: Sort As Is; Use Sort Algorithm; Date Sort.

Sort Algorithm

(Available when Use Sort Algorithm option is selected) Enter a PxPlus expression to be used as an alternate algorithm for sorting the column.

Example:

A column containing a date stored in a MMDDYYYY format can be displayed as MM/DD/YYYY using a "XX/XX/XXXX" format mask. However, to sort the column chronologically by year, month, day, you can set up a sort algorithm such as DT$(5,4)+DT$(1,2)+DT$(3,2).

Note:
While this would be necessary for proper sorting in a Classic Query, Query+ and Drop Query can use the Date Sort to accomplish this as well.

Sort Key Length

(Available when Use Sort Algorithm option is selected) Number of characters to be used to sort the column when using a sort algorithm. Default value is the length of the field to a maximum of 128.

Date Sort

(Query+, Drop Query) (Available when Date Sort option is selected) Enter or select a string consisting of D, M and/or Y (day, month year) to describe the order in which they occur in the data.

Example:

A date in the format MMDDYY would be MDY.

'Tip' Values

(Numeric Only, Query+, Drop Query and Classic Query with Sort by Column Option)

(Optional) Tip values to be displayed when the mouse is placed over the column heading. Values include Total, Average, Minimum and Maximum.

Hotlink Logic

(Query+ and Drop Query)

Logic to execute when the column is double-clicked. Click the drop-down arrow for a list of event actions: Ignore, Link, Call and Execute. See Actions and Parameters.

The specified logic overrides selecting the query record. Columns with a hotlink are displayed in the color defined by the 'OPTION'("StdLvueHotlinkClr",color$) setting and are underlined while hovering over the column. See OPTION mnemonic.

Webster Hotlink

Specify a hotlink for the column to be used in a Webster+ environment. Links may specify URLs, pages or events, starting with http://, https://, page: or event:. Additional URL parameters can follow the page/program name separated by an & (ampersand); e.g. &fieldname$=.

Example:

   page:pagename&fieldname$=$1

The Webster+ hotlink is translated into a Webster+ [link] short code:

   [link page:pagename&fieldname$=$1]

This results in a hyperlink to the specified page name with fieldname$ as the variable to be passed to the page/program and $1 being substituted with the value in the column.

Target

(Optional) Specify a Webster+ target browser window where the results of a link or event will be displayed. Selections are null (Default), Same, New or Popup.

Same

To have the output replace the current page.

New

To create a new tab/window.

Popup

To create a popup overlaid window. When selected, an input control with a % sign is displayed for entering the percentage of the screen the popup window will occupy.

(The Hotlink Logic option was added in PxPlus 2018.)
(The Webster Hotlink option was added in PxPlus 2021 Update 1.)

Query

(Classic Query Only) Optional sub-query assigned to the column.

Library

Select a library name from the drop-down list, click the Browse button to look through the directory structure to find the library or type the library path. An expression can also be entered by preceding the expression with an = (equals sign); e.g. =libname$ or ="mylib.en".

Note:
The Library name may be a specific or generic reference. See Cascading Language Suffixes.

Panel

Identify the query object to be used as a sub-query by selecting it from the drop-down list of available query panel names.

At run time, users can invoke the sub-query by highlighting the column title and clicking the enabled query button on the toolbar. The current contents of the query column are passed as the Start At Value for the sub-query.

(Support for entering an expression for a Library name was added in PxPlus 2019.)

Initially Hide Column

Select this option to initially hide the column at run time until the user selects it to be shown.

When using Query+ Toolbar, Hybrid, Menu or Drop Query views (see Query View option), columns can be set to be initially hidden or shown by clicking the Test/Design toolbar button to display the query and selecting the Show/Hide/Reorder Columns item from the Columns run-time option to set the visibility. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Security

Launches the Object Security Definition window for setting up column level security for the query. See Query Security.

(The Security button was added in PxPlus 2021.)

Query Formula Definition

A formula is a PxPlus expression (numeric or string) consisting of any combination of variables, constants and functions, whose resulting value is displayed in a query column. Formulas that have been assigned a name can be used within other formulas, noting that the order of evaluation of formulas corresponds to the order in which they are defined in the query definition.

To launch the Query Formula Definition window, click the Add Formula button on the Query Definition toolbar.

This window consists of the following:

Formula Name

(Optional) Enter a formula name to easily identify the formula throughout the Query Subsystem:

   •  For new formulas, the formula name will be pre-loaded with FML_QueryName_N (where QueryName is the name of the query and N is a numeric identifier), which can be changed or deleted.

   •  For existing formulas, the formula name will be blank until it is filled in.

All formula names will be prefixed with FML_ and must adhere to the rules for naming variables. The first character must be alphabetic (A-Z or a-z), the remaining characters can be alphabetic, numeric (0-9), . (a period), or _ (an underscore).

Naming a formula allows you to:

   •  Use the evaluated result of the formula in another formula.
   •  Use the formula as a parameter in a Display Option.
   •  Reference the formula by name in a Report Writer definition.
   •  Specify a column name for use with the *QUERY* interface.

Note:
Formula names are not backwards compatible.

(Formula Name was added in PxPlus 2020.)

Column Title

Column heading that users will see at the top of the formula column at run time; e.g. Customer Name. Can be a Fixed value, string Expression, or Message Library Reference.

Formula

PxPlus expression (numeric or string) consisting of any combination of variables, constants and functions. In formulas, you can refer to an external key as PRIME_KEY$.

Example:

   CVS(CST_NAME$,256)
   SURNAME$+","+NAME$(1,1)+","+STR(XXX.FLD#2*100) 

See Query Variables.

Width

Column width in columns. This can be adjusted as necessary, depending on the selected font.

Column widths can also be adjusted in Query+ Toolbar, Hybrid, Menu and Drop Query views (see Query View option) by clicking the Test/Design toolbar button to display the query and dragging the column header to the desired size. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Alignment

(Query+ and Drop Query)

Alignment of the value within the column. Settings include Default, Left, Center and Right justification. The Default setting for text values is Left justification and Right justification for numeric values.

Note:
Alignment affects text displays only so that just the column title of columns displaying images, percent bars, etc. are affected.

(The Alignment option was added in PxPlus 2018.)

Format

Can be either Mask or Program.

Mask

PxPlus format mask for the column's data output (fixed value or string expression); e.g. a phone number mask such as 000-000-0000.

Program

Name of the program called to format the display of the string data in this column; e.g. *win/date;display. On the ENTER statement of the sub-program, include one argument (value of the column is passed by your query.) Fixed text or an Expression can define the mask or name the program.

Note:
To use a Program, the formula must have a Formula Name.

Display Option

Click the Define button to launch the Query Column Display window where you can assign visual attributes, such as bold text, text color, highlight color, or an image, to the column. See Query Column Display below.

Sort Options

(String Only, Query+, Drop Query and Classic Query with Sort by Column Option)

The following sort options can be assigned to a column: Sort As Is, Use Sort Algorithm, Date Sort.

Sort Algorithm

(Available when Use Sort Algorithm option is selected) Enter a PxPlus expression to be used as an alternate algorithm for sorting the column.

Example:

A column containing a date stored in a YYYYMMDD format can be displayed as MM/DD/YYYY using the formula DT$(5,4)+DT$(1,2)+DT$(3,2) and a format string such as "XX/XX/XXXX". However, to sort the column chronologically by year, month day, you can use the original value, DT$, as the sort algorithm.

Note:
While this would be necessary for proper sorting in a Classic Query, Query+ and Drop Query can use the Date Sort to accomplish this as well.

Sort Key Length

(Available when Use Sort Algorithm option is selected) Number of characters to be used to sort the column when using a sort algorithm. Default value is the length of the field to a maximum of 128 characters.

Date Sort

(Query+, Drop Query) (Available when Date Sort option is selected) Enter or select a string consisting of D, M and/or Y (day, month, year) to describe the order in which they occur in the data.

Example:

A date in the format MMDDYY would be MDY.

'Tip' Values

(Numeric Only, Query+, Drop Query and Classic Query with Sort by Column Option)

(Optional) Tip values to be displayed when the mouse is placed over the column heading. Values include Total, Average, Minimum and Maximum.

Hotlink Logic

(Query+ and Drop Query)

Logic to execute when the column is double-clicked. Click the drop-down arrow for a list of event actions: Ignore, Link, Call and Execute. See Actions and Parameters.

The specified logic overrides selecting the query record. Columns with a hotlink are displayed in the color defined by the 'OPTION'("StdLvueHotlinkClr",color$) setting and are underlined while hovering over the column. See OPTION mnemonic.

Webster Hotlink

Specify a hotlink for the column to be used in a Webster+ environment. Links may specify URLs, pages or events, starting with http://, https://, page: or event:. Additional URL parameters can follow the page/program name separated by an & (ampersand); e.g. &fieldname$=.

Example:

   page:pagename&fieldname$=$1

The Webster+ hotlink is translated into a Webster+ [link] short code:

   [link page:pagename&fieldname$=$1]

This results in a hyperlink to the specified page name with fieldname$ as the variable to be passed to the page/program and $1 being substituted with the value in the column.

Target

(Optional) Specify a Webster+ target browser window where the results of a link or event will be displayed. Selections are null (Default), Same, New or Popup.

Same

To have the output replace the current page.

New

To create a new tab/window.

Popup

To create a popup overlaid window. When selected, an input control with a % sign is displayed for entering the percentage of the screen the popup window will occupy.

(The Hotlink Logic option was added in PxPlus 2018.)
(The Webster Hotlink option was added in PxPlus 2021 Update 1.)

Initially Hide Column

Selecting this check box initially hides the formula column at run time until the user selects it to be shown.

When using Query+ Toolbar, Hybrid, Menu or Drop Query views, columns can be set to be initially hidden or shown by clicking the Test/Design toolbar button to display the query and selecting the Show/Hide/Reorder Columns item from the Columns run-time option to set the column visibility. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Security

Launches the Object Security Definition window for setting up column level security for the query. See Query Security.

(The Security button was added in PxPlus 2021.)

Query Column Display

The Query Column Display window is used to assign visual attributes, such as bold text, text color, highlight color, percent bars, or an image, to a query column. The various visual attributes can be assigned based on specified conditions being met.

To assign visual attributes to a row in the query data list, see Row Display Option.

Note:
Display settings on individual columns will override Row Display settings.

Invoking Query Column Display

Follow these steps:

 

1.

In the Query Definition window, click on a column in the Columns list and then select the Column Options toolbar button.

 

2.

In the Column Options window, click the Define button (next to Method under the Display Option heading).

Alternatively, invoke the Query Column Display window by clicking the Define button in the Query Formula Definition window when adding or modifying a formula.

This window consists of the following:

Display Value

Value that will be displayed in the column. (The exception is if an image is to be displayed instead.)

Display Method

The following methods are available to assign various visual attributes to a column:

None

No display method. (Default)

BoldText

Display text in bold font.

Colors

Set text and background colors.

HiLiteColor

Set background colors.

Image

Display images. (See Note below.)

PercentBar

Display a horizontal bar that represents a percentage of the column size based on a numeric column value.

Example:

A value of 100 causes PercentBar to fill the entire width of the column, and a value of 50 fills half of the column width, and so on. A maximum value can also be specified such that the column is filled when the column value is the maximum value rather than 100.

PercentBarWithLabel

Same as PercentBar but with the column value displayed (using the column format) following the horizontal bar. The maximum value fills three-quarters of the column width with the remaining one-quarter of the width for the label.

TextColor

Set text colors.

User

Specify your own display strings.

Note:
When displaying an image, the image size will be dependent upon the height of the row and the width of the column containing the image:

   •  If the original image is larger than the allotted space, it will be scaled down to fit, maintaining its aspect ratio and centered in the space.
   •  If the original image is smaller than the allotted space, its original size will be maintained and it will be centered.

All images in the query will be rendered using the same dimensions, derived from the row height and the width of the narrowest column to contain an image. To increase the row height, adjust the Display Line Height option on the Query Header Display tab.

(Support for Image Scaling was added in PxPlus 2017.)

(The PercentBar option was added in PxPlus 2017.)
(The PercentBarWithLabel option was added in PxPlus 2017.)

Parameter Values

In addition to the Display Value, which is the default parameter value, you can specify other query fields as parameters if they are to be used as part of a condition or display string.

To Add a Parameter:

Click the Add button (next to Parameter Values) and then click the drop-down arrow at the right of the newly-allotted parameter to select a field from the list, or define field characteristics (i.e. field number, offset, length) for manual fields.

Formulas must be named to be included as additional parameters. See Query Formula Definition.

Each parameter is assigned an ID (such as %1, %2, etc.) that is to be used in a condition and display string expressions rather than the actual name of the field.

To Remove a Parameter:

Select the parameter entry to be deleted and then click the Remove button (next to Parameter Values). To select multiple entries, use Shift-Click (consecutive selections) or Ctrl-Click (random selections).

Prior to deleting, a message will display.

Condition

Set conditions for the assigned visual attribute(s). Conditions consist of PxPlus expressions that can be evaluated to zero/non-zero, but using the ID placeholder in the place of a field name; e.g. %1="Y".

To Add a Condition:

Click the Add button (below Condition grid) and then enter the condition.

Multiple conditions can be defined, each with its own assigned display setting (i.e. color, image, etc.).

Conditions are evaluated in the order in which they appear until a condition evaluates as true (i.e. non-zero). This means that if two conditions are set, %1<10 and %1<100, then values less than 10 will be assigned the first display setting, and values from 10 to less than 100 will be assigned the second display setting.

Values that fall outside of the range of the specified conditions may or may not have an assigned display setting. To assign a setting to these values, simply add a blank condition at the end and assign a display setting. Otherwise, no display setting will be assigned, and default text and color will be used for these values.

Use the Move Up/Move Down buttons (below Condition grid) to change the order of the conditions.

To Remove a Condition:

Select the condition entry to be deleted and then click the Remove button (below Condition grid). To select multiple entries, use Shift-Click (consecutive selections) or Ctrl-Click (random selections).

Prior to deleting, a message will display.

Color

(Display Method is Colors, HiLiteColor, PercentBar, PercentBarWithLabel or TextColor)

To assign a color to a condition, click the dotted button at the right of the Color cell to launch the Color Selections window.

In the case of the PercentBar and PercentBarWithLabel methods, the assigned Bar Color will be the color of the top of the bar with an optional Gradient Color for the bottom. (If a gradient color is omitted, the bottom of the bar will be shaded.)

Note:
Selecting Default (i.e. no color) is a valid selection.

(Support for the PercentBar and PercentBarWithLabel methods was added in PxPlus 2017.)

Max Value

(Display Method is PercentBar or PercentBarWithLabel)

By default, a column value of 100 causes PercentBar to fill the entire width of the column, a column value of 50 fills half of the column width, and a column value of 0 (zero) displays no bar.

You can also specify a different Max Value, which is used as the value to determine when the entire column width is to be filled.

Example:

   A Max Value of 150 causes PercentBar to fill the entire width of the column when the column value is 150. When the value is 100, two-thirds of the column width is filled, and so on.

Values that are greater than the Max Value are truncated.

When using a PercentBarWithLabel display, the Max Value fills only three-quarters of the column width with the remaining one-quarter of the width for the label.

(Max Value was added in PxPlus 2017.)

Image

(Display Method is Image)

To assign an image to a condition, click the dotted button at the right of the Image cell to launch the Bitmaps window to select a particular bitmap. You can also specify an expression by selecting the Exp check box and entering an expression using placeholder IDs.

Display String

(Display Method is User)

Display strings allow greater flexibility when assigning display attributes for particular conditions; e.g. you can mix bold text and background color.

Display strings are PxPlus expressions; therefore, select the Exp check box and enter the expression to use.

Example:

   '_COLOR'("Light Yellow")+'BB'

Note:
Be sure to use placeholder IDs rather than field name variables in your expression, if required.

You can also create your own display methods by creating your own column display class. The existing methods are in the *obj/qrycoldsp class. Create your own class and inherit the *obj/qrycoldsp class in yours by using LIKE "*obj/qrycoldsp" in its definition. Then incorporate your new methods. Set %NOMADS'Query_ColumnDisplay$ to the name of your class, and your new methods will become available.

Column Display Examples

Below are examples of setting up column display scenarios:

Description

Method

Example 1:

For the BALANCE column, you want to display negative values using Red text and high values (10000 or greater) using Green text.

The rest are to display using the default text color.

To do this:

Assign TextColor as the Display Method to the BALANCE column.

BALANCE is the default Parameter. It is assigned an ID of %1.

Then create two Conditions:

   %1<0

   %1>=10000

For the first Condition, assign Light Red as the Color, and Dark Green for the second Condition.

Example 2:

Your query has the following columns:

   CUSTOMER_NAME$
   CUSTOMER_CLASS$

You want to display the CUSTOMER_NAME$ value in bold text if CUSTOMER_CLASS$ contains the value "VAR".

To do this:

Assign BoldText as the Display Method to the CUSTOMER_NAME$ column.

The condition is based on the CUSTOMER_CLASS$ column; therefore, add CUSTOMER_CLASS$ to the list of Parameter Values. It is assigned an ID of %2.

Then create the Condition:

   %2="VAR"

Example 3:

You want to highlight the entire CLIENTID$ column with a Light Green custom background color.

To do this:

Assign HiLiteColor as the Display Method to the CLIENTID$ column.

Leave the Condition blank. Select your custom color in the Color Selections window.

Example 4:

Given a salesperson's yearly sales and sales target, you want to display a PercentBarWithLabel showing the percentage of the target that was achieved by each salesperson.

In addition, you want to display a Gold bar if the salesperson outperformed their target.

To do this:

Create a formula to calculate the percentage of the sales target the salesperson achieved:

   100*SMN_YR_SALES/SMN_YR_TARGET

Set a format for the formula such as ##0.0%.

Click the Define button to select a Display Option. For the Display Method, select PercentBarWithLabel.

To create a Gold bar for the salespersons achieving more than their sales target, enter %1>100 as the Condition. For the Bar Color, assign a color such as RGB:225 210 30.

For the remaining salespersons, add a second blank Condition with a Bar Color of your choice.

(Example 4 using the PercentBarWithLabel method was added in PxPlus 2017.)