Emojis - SQL in ICM InfoWorks - 14 Prompts 🖊️

 14 Prompts 🖊️

To allow the user 👤 to enter parameters used in queries, the statements

PROMPT LINE 📜

PROMPT TITLE 🏷️

PROMPT DISPLAY 🖥️

are used.

The fundamental way this works is as follows:

A number of PROMPT LINE statements define the list of values to be input or displayed and their formats. There will be one line displayed in a grid 📊 for each value.

The variables must be scalars. The variable can either have been defined and set before the PROMPT LINE statement or mentioned for the first time in the prompt line statement.

Valid values used in the prompt line statement are as follows, they will be explained in detail below:

PROMPT LINE <variablename> 📋

PROMPT LINE <variablename> '<description>' 📝

PROMPT LINE <variablename> '<description>' STRING 🧵

PROMPT LINE <variablename> '<description>' DATE 📅

PROMPT LINE <variablename> '<description>' DP <numberofplaces> 🔢

PROMPT LINE <variablename> '<description>' BOOLEAN ☑️

PROMPT LINE <variablename> <variablename> 🔄

PROMPT LINE <variablename> <variablename> STRING 🧵

PROMPT LINE <variablename> <variablename> DATE 📅

PROMPT LINE <variablename> <variablename> DP <numberofplaces> 🔢

All of these but the first may optionally be followed by

LIST <listvariablename> 📜

FILE 📂

FOLDER 📁

MONTH 🗓️

RANGE <startvalue> <endvalue> 📏

In general PROMPT LINE is followed by the variable name and the description. If the description is omitted as in the first case, or if the description is the blank string '', the description will be the variable name. The description may be a scalar variable which must have been defined before the PROMPT LINE clause. This is evaluated when the dialog is displayed.

Examples of valid prompt lines:

PROMPT LINE $x 📋

This defines a number variable which will have the default of 2 decimal places in the grid and the description '$x'. This takes advantage of the special case where numeric variables can be defined by simply saying PROMPT LINE $x without having to give a description.

PROMPT LINE $s '' STRING 🧵

This defines a string variable. Note that because we want to use more than 3 parameters we have to add a description field for the variable, but we can take advantage of the special case where using the blank string '' will cause the variable name to be used as the description.

PROMPT LINE $d '' BOOLEAN ☑️

This defines a boolean variable. The value will be displayed as a check box; checked for true, unchecked for false.

PROMPT TITLE '<title>' may be used to change the title of the row displayed from the default, which is 'SQL Prompt Dialog'. The title may also be a scalar variable, which will be evaluated at the point the dialog is displayed.

If you wish the entire grid to be read-only so that the values can be looked at by the user but not altered, use the keyword READONLY following PROMPT DISPLAY i.e. say

PROMPT DISPLAY READONLY 🔒

This would typically be used to display the results of SELECT queries storing values in scalars and scalar expressions (see below).

Error messages that may occur with the prompt functionality are as follows:

PROMPT LINE clause too short - the clause is too short e.g. PROMPT LINE ❌

PROMPT LINE clause requires a variable after the LINE keyword e.g. PROMPT LINE 23 - self explanatory ❓

PROMPT LINE clause variable not a scalar - the variable has already been used in another context (currently list or array) e.g. SET $x = x; PROMPT LINE $x; ⚠️

PROMPT LINE clause description not a string - the description field in the 4th parameter is not a string e.g. PROMPT LINE $x STRING (in this case perhaps you have forgotten to add a description after the variable name). 🚫