Query Subsystem

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 invoke 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), . (period), or _ (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.)

See Also

Query Definition