CS

Working with variables in scripts

Home / Documentation / Administrator guide / Script designer /

Working with variables in scripts

CS

What a script variable is

  • A script variable is a temporary storage used during script execution.
  • Variables are written in the form “#A#”, “#B#”, “#C#”, etc.
  • Script variables are not database columns – they exist only for the duration of the script execution.
  • Each variable always has exactly one data type.

Supported variable data types:

  • String – text string
  • Integer – integer number
  • Long – large integer number
  • Double – decimal number
  • Date – date

Database null value

  • The script uses the database value “null”.
  • The value “null” means that the variable has no defined value.
  • A newly declared variable that has not been assigned a value always has the value “null”.
  • The value “null” can be assigned explicitly using the variable “#null#”.

Variable declaration

A variable must always be declared before it is used. Declaration defines the variable’s data type.

A variable can be declared in several ways:

  • Using a script command for variable declaration.
  • By loading a value from a database query – the variable’s data type is automatically taken from the database column type.
  • Using expressions:
    • String(text)
    • Integer(number)
    • Long(number)
    • Double(number)
    • Date(date)
  • By assigning another variable or a database control to a variable that does not have a data type yet – the variable takes over the type of the source.

Important behavior of declaration:

  • A new declaration always resets the value of the variable to “null”, even with the same data type, and removes the link of the variable to the database query it was loaded by.
  • The variable that controls a running cycle and the variables loaded together with it cannot be declared, and a nested cycle cannot be run over them – the script ends with an error. Inside the cycle, another variable, the result of the “String(...)”, “Integer(...)” and similar expressions, a manually created array of values or the result of a database query cannot be assigned to them either. Assigning a single value written into the value field (a constant or an expression) or the value of a database control is allowed; a value that is an array of values (a “MultiListBox” control or the “GROUP(...)” expression) ends the script with an error.
  • An undeclared variable used in a condition, a cycle or a calculation raises an error. Substituted into text as “#A#”, it raises no error and is replaced with an empty string.

Single value vs. array of values

  • A variable loaded from a database query automatically becomes an array of values.
  • Each row in the query result corresponds to one element of the array. The exception for the “MultiListBox” control is described in the “Assigning values” chapter.
  • If the database query returns no records, the result is an array with zero elements.
  • A variable with an array of values substituted into text as “#A#” is replaced with all elements of the array separated by a semicolon – this way, for example, a list of IDs can be passed to a function parameter. A date is written including the time with seconds; zero seconds are omitted, and at the time 00:00:00 only the date is written. A decimal number is written with the decimal separator of the user's language (a comma in Czech). An empty value – “null”, an array with no elements and an empty element of the array – is written as 0 for the Integer, Long and Double types and as an empty string for the other types.

Arrays of values can also be created manually using server functions (for the StringArray, LongArray, DoubleArray and DateArray functions, the values are separated by a semicolon or a tab):

  • StringArray(value1;value2;...)
  • IntegerArray(number_of_elements) – an array of the numbers 1 to number_of_elements
  • LongArray(value1;value2;...)
  • DoubleArray(value1;value2;...)
  • DateArray(value1;value2;...)
  • EmailArray(address1;address2;...) – an array of text values in which each element is one e-mail address from the given list; the addresses can be separated by a semicolon or a comma, empty items are left out, and an address given with the contact name stays in the array element including the name (a name containing a comma or a semicolon must be enclosed in quotation marks, and a semicolon in it is replaced by a comma)

Assigning values

  • A variable value can be assigned to another variable or to a database control.
  • Assignment can be:
    • simple (a single value), or
    • based on a mathematical expression (addition, subtraction, multiplication, division).
      • A number written in the value field with the unit “d” (days), “m” (months), “y” (years) or “h” (hours), for example “1m”, can be added to or subtracted from a date; a number without a unit means days. For months, years and hours, the decimal part of the number is truncated.
      • A last character other than “d”, “m”, “y” or “h” is not treated as a unit, and the whole value is read as a number of days. The number is read from the value as an arithmetic expression, but unlike the conversion of text to a number, ignored text after the number is not written into the “Errors.log” log, and a value that cannot be read as an arithmetic expression does not interrupt the script and leaves the date unchanged.
  • For the String type, the “+” operator joins the texts, and the other operators end with an error. A second operand given by a variable or a database control must have the same data type as the first one, otherwise the script ends with an error; the exceptions are the combination of the Integer and Long types and adding a value of the Integer type to a date or subtracting it from a date when the target is a date.
  • If a variable is an array of values, arithmetic operations are applied to all elements of the array. If the second operand is an array too, the calculation is done element by element only when both arrays have the same number of elements; otherwise the first element of the second operand is used, and an empty array leaves the result unchanged.
  • A “null” value counts as 0 in a calculation, and as an empty string for the String type. A date with the “null” value stays “null” after adding or subtracting.
  • The Integer and Long types divide as integers (the decimal part of the result is truncated); division by zero and a result outside the range of the type interrupt the script with an error; the Double type divides with decimals, and division by zero raises no error – the result is infinity, or an invalid number when zero is divided by zero.
  • The difference of two dates assigned to a variable or a control of type Integer is the number of whole days between the calendar days of both dates (the time is not taken into account); if one of the dates is “null”, the result is “null”. The “Assign more/multiple values from query” command loads the value into a variable with the data type of the query column, so this command cannot calculate the difference of two dates into a variable; into a control of type Integer it calculates it.
  • When assigning an array of values to a database control, only the first value of the array is used. The exception is the “MultiListBox” control, to which all elements of the array are written as its individual items.
  • The value of a “MultiListBox” control loaded into a variable by the “Assign value from query” command is an array of the individual items from all returned records, without duplicates. The “Assign more/multiple values from query” command returns one array element per record even for the “MultiListBox” control – the stored value with the items separated by a tab.
  • The value of a “MultiListBox” control of the edit form assigned to a variable is also an array of its individual items.
  • If the array contains no elements or the value is “null”, the database value “null” is assigned to the control.

Cycle

  • A cycle is used to sequentially process elements of an array of values.
  • The number of cycle iterations corresponds to the number of elements in the controlling variable.
  • If the array contains no elements, the cycle is not executed at all.

Variable behavior inside a cycle:

  • During a single iteration, the variable behaves as a single-value variable.
  • After the cycle finishes, the variable again behaves as an array of values.
  • Elements with the value “null” are automatically skipped in the cycle.
  • If multiple variables were loaded by one script line (the line type “Assign more/multiple values from query”), they are iterated synchronously in the cycle. Variables loaded by two lines are not iterated synchronously, even if they use the same query. If one of them has fewer elements than the controlling variable, the cycle ends with an error.
  • It is recommended to use the primary key “id” as the controlling variable of the cycle.

Comparing values

  • Script values of two variables can be compared.
  • If one of the variables is an array of values, a simple condition (“If (expression) ...”) compares only its first value.
  • A composed condition (“If (composed expression) ...”) compares a variable with more elements as a single text in which all elements of the array are separated by a comma and a space; only the “contains”, “begins with” and “ends with” operators test the individual elements of the array. However, it is compared as text only with text: the given value is converted according to the type of the variable, so a variable of the Integer, Long, Double or Date type with more elements compared with a given number or date ends with an error in evaluating the condition. Comparing a variable with more elements (of any type) with a single-element variable or with a database control also ends with an error if it is of the Integer, Long, Double or Date type. The “equal to” operator with a list of numbers (for example “1;2;3”) does not raise an error for a variable of the Integer or Long type, but it is not satisfied. It is therefore advisable to use a variable with an array of values in a composed condition only inside a cycle in which it is the controlling variable or was loaded from a database query by the same command as the controlling variable – only there does it behave as a single-value variable.
  • In a composed condition, a variable or a database control of the Integer or Long type can be compared with a list of numbers separated by a semicolon written into the value field (for example “1;2;3”, or “#A#” with a variable containing an array of values). The “equal to” operator is then satisfied if the value equals any of the numbers, and the “is not equal to” operator if it equals none of them.
  • Both a simple and a composed condition compare text values regardless of upper and lower case; for a composed condition this applies regardless of the database type. The “bigger”, “bigger or equal to”, “less” and “less or equal to” operators are not supported by a simple condition for values of the String type, and the script ends with an error; they are evaluated without an error only if one of the compared values is “null”. A composed condition evaluates these operators for text values by comparing the texts.
  • A simple condition compares a “MultiListBox” control of the edit form item by item: the “contains”, “begins with” and “ends with” operators and their negations go through all items, the “is defined” and “is not defined” operators test only the “null” value (the empty string that the form stores for a control without a selected item is therefore defined), and the other operators compare only the first item. The “equal to” operator with an empty value field is satisfied only by an empty string, not by the “null” value, and the “is not equal to” operator with an empty value field is satisfied only by a filled-in value. For the other operators, a control without a selected item behaves as the “null” value, so for example a comparison with the “#null#” variable using the “equal to” operator is satisfied even by an empty string.

The “contains”, “begins with” and “ends with” operators:

  • When used on an array of values, they test whether the array contains a specific value, or whether any of its elements begins or ends with the given value. In both a simple and a composed condition, the given value is converted according to the type of the variable, and a variable or database control the array is compared with must have the same data type as the array (the Integer and Long types are considered the same); an invalid value as well as a different type end the script with an error.
  • When used on a text string, they test whether the string contains a given substring, or whether it begins or ends with it.
  • For a value of a data type other than String that is not an array of values (a control, a variable inside a cycle or a variable with a single assigned value), a composed condition evaluates these operators as “equal to” and their negations as “is not equal to”. A simple condition ends with an error for such a value.

Testing for an empty (unfilled) value

An empty (unfilled) value exists in the database in two forms – as the database value “null” and, for text columns, also as a zero-length text string (empty string). The “is not defined” operator alone catches only the “null” value – it does not catch an empty string. A condition testing whether a value is (not) filled in must therefore take both variants into account.

The condition “value is not filled in” by data type or control type:

  • String (TextBox, TextArea, RichTextBox, ComboBox, ListBox, MultiListBox, Radio, CheckBox) – a composed condition: value equals “” or value is not defined. In SQL: ng_tb = '' OR ng_tb IS NULL.
  • ForeignKey, File, Image – a composed condition: value equals “0” or value is not defined. In SQL: ng_idfk = 0 OR ng_idfk IS NULL.
  • Integer, Long, Double, Date – the “is not defined” operator is sufficient. These data types cannot contain an empty string; an unfilled value is always “null”.

In a script, the composed condition is written using a line of type “If (composed expression) ...” with two conditions joined by the logical operator “or”. Example of interrupting a script when the user has no e-mail address filled in – the variable “#A#” of type String contains the user’s e-mail address loaded by a database query, and a line of type “14. If (composed expression) throw exception” contains two conditions:

  • First condition: “#A#” | operator “equal to” | the value field is intentionally left empty (the comparison is made against an empty string) | joined by “or”.
  • Second condition: “#A#” | operator “is not defined”.
  • Exception text: “The user has no e-mail address filled in.”

A simple condition (“If (expression) ...”) and a composed condition (“If (composed expression) ...”) treat the “null” value differently:

  • A simple condition is evaluated directly on the server and takes the “null” value into account:
    • The “equal to” operator evaluates two “null” values as equal – a test for “null” can be written by comparing with the “#null#” variable.
    • The “is not equal to” operator evaluates a “null” value as satisfied against any filled-in value, including an empty string. The exception is a “MultiListBox” control of the edit form compared with an empty value field (see the “Comparing values” chapter).
    • The “bigger or equal to” and “less or equal to” operators evaluate two “null” values as satisfied and a single “null” value as not satisfied. The “bigger” and “less” operators are never satisfied with a “null” value.
  • A composed condition is evaluated as an SQL condition with three-valued logic:
    • A “null” value satisfies neither the “equal to” nor the “is not equal to” operator – regardless of the compared value.
    • A “null” value is caught by the “is not defined” operator or by comparing with the “#null#” variable using the “equal to” operator.

This has a practical consequence for the opposite test – “the value is filled in” – for the String data type:

  • In a composed condition, a single “is not equal to” condition with an empty value field is sufficient – under SQL logic, a “null” value does not pass the condition.
  • In a simple condition, “is not equal to” with an empty value field lets the “null” value through (null satisfies the condition) – the “is filled in” test therefore cannot be written as a single simple condition. The exception is a “MultiListBox” control of the edit form, for which “is not equal to” with an empty value field does not let the “null” value through.

The two conditions also treat dates differently:

  • A simple condition always compares dates including the time.
  • A composed condition compares the value of a database control with a date entered in the condition as a value (including one substituted by the “#...#” notation) without time (with the time 00:00) only by the date – the time stored in the control is not taken into account. If there is a variable on the left side, or a variable or another control on the right side, the comparison includes the time.
  • A date written into a composed condition as a variable (in the “#...#” form) that is not a valid date after substitution (typically an empty value) is replaced with the database value “null”, and this comparison is not satisfied. The exception is comparing a variable with an array of values using the “contains”, “begins with” or “ends with” operator, where an invalid date ends the script with an error.

The same rule also applies to query filtering conditions. A detailed description of conditions is provided in the separate guide Database query designer.

Data type conversion

  • Assigning a variable to a variable of a different data type ends with an error; the exceptions are the pair Integer and Long and the difference of two Date values assigned to a variable of type Integer.
  • The conversion is performed by writing the variable into the value field, for example “#A#” – the value is first substituted as text and only then converted to the data type of the target variable. The same applies to the expressions “String(#A#)”, “Integer(#A#)” etc.
  • The conversion via “#A#” does not preserve the “null” value: an empty numeric variable is substituted as 0 (a String target gets the text “0”) and an empty text variable as an empty string.
  • Converting text to a number evaluates the text as an arithmetic expression: an empty text gives 0, leading zeros are ignored (for example “05” is converted to 5), text after the number is ignored and for the Integer type the decimal part is truncated. Ignored text after the number (for example “1 000” is converted to 1) does not interrupt the script, but it is written into the error log “Errors.log”. An error is raised by text that cannot be read as an arithmetic expression – text starting with a letter (except a call of a mathematical function, for example “abs(-5)”), text containing only spaces, an expression ending with an operator (for example “12+”) or with an unclosed parenthesis, division by zero, when converting to the Integer or Long type a number outside the range of this type, and text in which the number is followed by the letter “e” or “E” (for example “12eur”), because it is read as an exponent.
  • An empty text converted to a date ends with an error, because it cannot be recognized as a valid date. The value of a variable or a control of any data type can be cleared with the “#null#” variable written as the whole value.
  • The “New record” command does not convert an empty value for a column of a data type other than String and leaves the column with the database value “null”.
  • Converting a Long number to Integer ends with an error if the number is outside the range of the Integer type (-2147483648 to 2147483647).

Example of converting a number to text:

  • the value “#A#” is assigned to a variable of type String, where “A” is a variable of type Integer

Example of converting text to a number:

  • the value “#A#” is assigned to a variable of type Integer, where “A” is a variable of type String

Typical mistakes when working with variables

  • A variable is used without being declared.
  • The script author expects a single value, but the variable is actually an array of values.
  • The value “null” is not handled in subsequent processing.
  • A cycle uses a controlling variable that contains “null” values.