Variables
A variable is a specific field within a referenced order or study. They are represented by column IDs, COLID. The list of COLIDs are displayed on the Customize Labels pages and in the Calculated Fields User Manual.
Variables types
Each Variable has a type depending on the information the field conveys. Some macros and operators work only if the arguments are of the required type. The table below lists the supported variable types.
|
DateTime |
All fields that contain a Date, Time or Date+Time value. |
|
Interval |
Expresses an interval of time between DateTime values. The display format of this type variable is defined in the Date Format section of the Settings page. |
|
Number |
Binary number fields. |
|
String |
All alphanumeric strings. |
Constants
Constants are literal values entered in the calculated field rule. Their format is defined as follows:
|
DateTime |
Date and time expressed in the format YYYYMMDD_HHMMSS. To express time only, the format is 00000000_HHMMSS. Date-only constants are formatted YYYYMMDD_000000 |
|
Interval |
Interval constants, expressed in seconds, using the format <number>S. For example, 1200S for 1,200 seconds. Used to add time to DateTime variables. |
|
Number |
Integer constants expressed as a string of numbers. Negative values must be expressed as an operation. For example, for -5, use 0-5. |
|
State |
Study state constants, expressed as the state label, without any quotes. For example, Ordered, Unviewed, and Final. |
|
String |
String constants wrapped on either end with double quotes. |
Macros and Operations
Arithmetic macros and operations
|
add(a,b[,c...]) |
Add the operands. If the Type of all operands is Number or Interval, the result is an arithmetic sum of the values. If the Type of all operands is String, the result is the concatenation of the strings. If the operands are a DateTime and one or more Interval variables, the Interval variables are added to the DateTime variables and the result is a DateTime variable. All other combinations of variable types is unsupported and returns NULL. |
|
div(a,b) |
Divide the operands. If the Type of all operands is Number of Interval, the result is the arithmetic dividend a/b. All other combinations of variable types is unsupported and returns NULL. Division by zero constants is checked, but zero-value variables may fail and product undefined results. |
|
mul(a,b[,c...]) |
Multiple the operands. If the Type of all operands is Number or Interval, the result is the arithmetic product of the values. All other combinations of variable types is unsupported and returns NULL. |
|
sub(a,b) |
Subtract the operands. If the Type of all operands is Number, Interval or DateTime, the result is the arithmetic difference a-b. If operand a's Type is DateTime and b's Type is Interval, the result is a DateTime variable a-b. All other combinations of variable types is unsupported and returns NULL. |
|
+ |
Same as add() with a limit of two operands, in the format a+b. |
|
- |
Same as sub() using the format a-b. |
|
* |
Same as mul(), with a limit of two operands, in the format a*b. |
|
/ |
Same as div() using the format a/b. |
Boolean macros and operations
|
and (x,y) |
The result is “yes” if Boolean expression x and y are both “yes”. If either Boolean expression is “no", the result is “no”. |
|
not(x) |
The result is “yes” if the Boolean expression x is “no”. Otherwise, the result is “yes”. |
|
or(x,y) |
The result is “yes” if Boolean expression x or y is “yes”. If neither Boolean expression is “yes”, the result is “no”. |
|
xor(x,y) |
The result is “yes” if either Boolean expression x or y is “yes”. If both Boolean expressions are “no” or if both are “yes”, the result is “no”. |
|
& |
Same as and(), in the format x&y. |
|
| |
Same as or(), in the format x|y. |
|
^ |
Same as xor(), in the format x^y. |
Conditional macros and operations
|
equals(x,y) |
The result is ‘yes’ if the two values are the same. Otherwise, the result is ‘no’. Both x and y must be the same type: string, number or interval. |
|
greater(x,y) |
The result is ‘yes’ if x is greater than y. Otherwise, the result is ‘no’. Both x and y must be the same type: number or interval. |
|
if(test,x,y) |
Returns x if the Boolean expression test evaluates to true. Otherwise, the result is y. |
|
less(x,y) |
The result is ‘yes’ if x is less than y. Otherwise, the result is ‘no’. Both x and y must be the same type: number or interval. |
|
= |
Same as equals(), in the format x=y. |
|
> |
Same as greater(), in the format x>y |
|
< |
Same as less(), in the format x<y |
Date macros and operations
|
age(d1,d2) |
Returns the difference d1-d2 in calendar years. |
|
atleaststatemintime(s) |
Returns the earliest date and time the order or study existed in state s or any later state. The result is in DateTime format. Available for the Study Information and Scheduler Order tables only. |
|
atmoststatemaxtime(s) |
Returns the most recent date and time the order or study was in state s or any earlier state. The result is in DateTime format. Available for the Study Information and Scheduler Order tables only. |
|
curdatetime() |
Returns the current date and time in DateTime format. |
|
minstatetime(s) |
Returns the earliest date and time the order or study existed in state, s. The result is in DateTime format. |
|
maxstatetime(s) |
Returns the most recent date and time the order or study existed in state, s. The result is in DateTime format. |
Multi-value macros and operations
|
avg(id) |
Returns the mathematical average of the values in the multi-valued field, id, such as an object-level field in the study table. Number and Interval Type variables only. |
|
count(id) |
Returns the number of entries in the multi-valued field, id, such as an object-level field in the study table. |
|
countif(id,s) |
Returns the number of entries in the multi-valued field, id, whose value is s. Number, Interval and String Type variables only. The result is in Number format. |
|
hasvalue(<Colid>) |
Checks the specified field value for NULL or an empty string. Returns “no” if no value is present. Otherwise, it returns “yes”. |
|
max(id) |
Returns the greatest value in the multi-valued field, id, such as an object-level field in the study table. The comparison is numeric for Number and Interval Types, lexicographic for String and DateTime Types. |
|
min(id) |
Returns the lowest value in the multi-valued field, id, such as an object-level field in the study table. The comparison is numeric for Number and Interval Types, lexicographic for String and DateTime Types. |
|
sum(id) |
Returns the mathematical sum of the values in the multi-valued field, id, such as an object-level field in the study-table. Number and Interval Type variables only. |