|
|
|
|
|
|
|
For a given column on a list view,
tree view, or results list table field, returns the average of all non-NULL
row values as a real type.
Following are exceptions:
- For
char columns, converts the column values to numeric and averages the
numeric values.
- If
the column type is currency, the average of the currency value is
calculated, and the currency type is ignored.
- If
the column type is enum (radio button or selection), the average of the
enum value is calculated.
- COLAVG
ignores date/time, date, and time columns; and returns NULL.
|
|
|
|
For a list view, tree view, or
results list table field, returns the total number of rows.
For a column field, returns the total
number of non-NULL rows in a given column.
|
|
|
|
For a given column on a list view,
tree view, or results list table field, returns the maximum value of the row
values.
|
|
|
|
For a given column on a list view,
tree view, or results list table field, returns the minimum value of the row
values.
|
|
|
|
For a given column on a list view,
tree view, or results list table field, returns the sum of all values as a
real type.
Following are exceptions:
- For
char columns, converts the column values to numeric and sums up the numeric
values.
- If
the column type is currency, the sum of the currency value is
calculated, and the currency type is ignored.
- If
the column type is enum (radio button or selection), the sum of the enum
value is calculated.
- COLSUM
ignores date/time, date, and time columns; and returns NULL.
|
|
(currency, type, timestamp)
|
|
For a given currency, converts
currency to different type according to new date and recalculates the
functional currency value(s).
CURRCONVERT($ $,
$ .TYPE$, $TIMESTAMP$)
Use other values for the type and
timestamp, such as a character field and date field. For example:
For information about data conversion
rules for currency fields, see the Form and Application Objects
guide.
|
|
|
|
|
|
|
|
For a given currency, sets new date
of currency and recalculates the functional currency value(s).
CURRSETDATE($ $,
$TIMESTAMP$)
Use other values for the timestamp,
such as a date field or integer. For example:
CURRSETDATE($ $,
1026779689)
For information about data conversion
rules for currency fields, see the Form and Application Objects
guide.
|
|
|
|
For a given currency, sets new type
of currency and recalculates the functional currency value(s).
Use other values for the type, such
as a character field. For example:
For information about data conversion
rules for currency fields, see the Form and Application Objects
guide.
|
|
|
|
For a given currency, sets new value
of currency and recalculates the functional currency value(s).
CURRSETVALUE($ $,
$ .VALUE$)
Use another field to set the currency
value, such as a decimal field. For example:
For information about data conversion
rules for currency fields, see the Form and Application Objects
guide.
|
|
|
|
Returns the date portion of the time
stamp.
|
|
|
|
For a given date, adds a specified
number of days, weeks, months, or years and returns the new date.
Specify datepart using one of the
following abbreviations, enclosed in double quotation marks:
The date parameter is the date value
to add to.
For example, to add 10 weeks to the
05/20/02, you would enter:
DATEADD(“ww”, 10, 05/20/02)
|
|
(datepart, startdate, enddate)
|
|
Depending on the value of datepart
(week or day), returns the number of weeks or days between the start date and
end date.
Specify datepart using one of the
following abbreviations, enclosed in double quotation marks:
For example, to find the number of
days between date1 and date2, you would enter:
DATEDIFF(“dd”, $date1$, $date2$)
|
|
|
|
Depending on the value of datepart
(month or day), returns the name of the month or day corresponding to date.
Specify datepart using one of the
following abbreviations, enclosed in double quotation marks:
For example, to find the weekday for
December 31, 2003, you would enter:
|
|
|
|
Depending on the value of datepart
(year, month, week, day, or weekday), returns the numeric value of the year,
month (1 to 12), week (1 to 52), day (1 to 31) or weekday (1=Sunday,
2=Monday, and so on). Specify datepart using one of the following
abbreviations, enclosed in double quotation marks:
For example, DATENUM(“mm”, 12/31/03)
|
|
|
|
Returns the day of the time stamp (1
to 31).
|
|
|
|
Used in Set Fields filter
actions only. Returns the unencrypted text value of the encrypted text
(cyphertext), using the encryption key (key). The return value will be 3/4 of
the size of the encrypted string.
For example, to decrypt the string in
Field1 using the key my_key, enter:
DECRYPT($Field1$,
"my_key")
To decrypt a string using a key in
KeyField, enter:
DECRYPT("String to
decrypt", $KeyField$)
|
|
|
|
Used in Set Fields filter
actions only. Returns the encrypted value of a text string (plaintext), using
the encryption key (key).
The output is limited to the size of
the field used for output, including the base-64 encoding. Therefore, you are
limited to encrypting a string that is 3/4 the size of the output field.
For example, to encrypt the string in
Field1 using the key my_key, enter:
ENCRYPT($Field1$,
"my_key")
To encrypt a string using a key in
KeyField, enter:
ENCRYPT("Word to encrypt",
$KeyField$)
|
|
|
|
Returns the hour of the time stamp (0
to 23).
|
|
|
|
For single-byte languages. Returns
the left-most bytes of the first parameter (char) up to the number
of bytes indicated by the second parameter (int).
For example, to set the value of a
field to the first ten bytes of the Submitter name, enter:
LEFT($Submitter$,10).
|
|
|
|
For single-byte or multi-byte
languages. Returns the left-most characters of the first parameter
(char) up to the number of characters indicated by the second
parameter (int).
For example, to set the value of a
field to the first ten characters of the Submitter name, enter:
LEFTC($Submitter$,10).
|
|
|
|
For single-byte languages. Returns
the number of bytes in the string (char).
|
|
|
|
For single-byte or multi-byte
languages. Returns the number of characters in the string (char).
|
|
|
|
Returns all characters in the string
(char) as lowercase characters.
Note:
No conversion will be performed to double-byte alphabets.
|
|
|
|
For single-byte languages. Returns
the value that results from padding the first parameter (char) to the left
with the value of the third parameter (char) so that the resulting value is
the length (in bytes) of the second parameter (int) or the length of
the original string, whichever is longer.
For example, if you want the results
of a Set Fields operation to be a 15-byte value with the prefix LEAD,
followed by zeros, and ending in the contents of the integer field Call #,
enter:
LPAD($Call
#$,15,"LEAD00000000000").
If the Call # field contains the
number 947, the result of the Set Fields action will be LEAD00000000947.
|
|
|
|
For single-byte or multi-byte
languages. Returns the value that results from padding the first parameter
(char) to the left with the value of the third parameter (char) so that the
resulting value is the length (in characters) of the second
parameter (int) or the length of the original string, whichever is longer.
For example, if you want the results
of a Set Fields operation to be a 15-character value with the prefix
LEAD, followed by zeros, and ending in the contents of the integer field Call
#, enter:
LPADC($Call
#$,15,"LEAD00000000000").
If the Call # field contains the
number 947, the result of the Set Fields action will be LEAD00000000947.
|
|
|
|
Returns the value of (char) after
deleting any blank spaces and tabs to the left.
|
|
|
|
Returns the maximum value of the set
specified. The data type of all values must match for the result to be
meaningful.
For example, to check the current
time and the escalation time and return only the greater (latest) value of
the two, enter:
MAX
($Escalate Date$, $TIMESTAMP$).
|
|
|
|
Returns the minimum value of the set
specified. The data type of all values must match for the result to be
meaningful.
For example, to check the current
time and the escalation time and return only the lower (earliest) value of
the two, enter:
MIN ($Escalate Date$, $TIMESTAMP$).
|
|
|
|
Returns the minute of the time stamp
(0 to 59).
|
|
|
|
Returns the month of the time stamp
(1 to 12).
|
|
|
|
Returns the value that results from
replacing any occurrences of the second parameter (char) found in the first
parameter (char) with the contents of the third parameter (char).
For example, to replace the name Bob
with the name Robert, enter:
REPLACE ($Submitter$,
"Bob", "Robert").
To replace all occurrences of the
double-quote character (") within a character string with another
character, for example, replacing any instance of a double quote with the
name Robert, enter:
REPLACE ($Submitter$,
"""", "Robert").
As a result, anywhere a
double quote is found in the Submitter field, the double quote will be
replaced with the name Robert.
|
|
|
|
For single-byte languages. Returns
the right-most bytes of the first parameter (char) up to the number
of bytes indicated by the second parameter (int).
For example, to set the value of a
field to the last four bytes of an account code, enter:
|
|
|
|
For single-byte or multi-byte
languages. Returns the right-most characters of the first parameter
(char) up to the number of characters indicated by the second
parameter (int).
For example, to set the value of a
field to the last four characters of an account code, enter:
|
|
|
|
Returns the rounded value of a real
number. For example, 5.1 to 5.4 are rounded to 5, and 5.5 to 5.9 are rounded
to 6.
|
|
|
|
For single-byte languages. Returns
the value that results from padding the first parameter (char) on the right
with the value of the third parameter (char) so that the resulting value is
the length (in bytes) of the second parameter (int) or the length of
the original string, whichever is longer.
For example, enter
RPAD($Submitter$,20," ") to add blank spaces after a
submitter’s name to make the entry 20 bytes long.
|
|
|
|
For single-byte or multi-byte
languages. Returns the value that results from padding the first parameter
(char) on the right with the value of the third parameter (char) so that the
resulting value is the length (in characters) of the second
parameter (int) or the length of the original string, whichever is longer.
For example, enter
RPADC($Submitter$,20," ") to add blank spaces after a
submitter’s name to make the entry 20 characters long.
|
|
|
|
Returns the value of (char) after
deleting any blank spaces and tabs to the right.
|
|
|
|
Returns the seconds from the time
stamp
(0 to 59).
|
|
|
|
For single-byte languages. Returns
the position (in bytes) of the second parameter (char) if it is
found in the first parameter (char). If the second string is not found,
returns a –1.
This function is zero-indexed (that
is, numbering of bytes begins at 0).
|
|
|
|
For single-byte or multi-byte
languages. Returns the position (in characters) of the second
parameter (char) if it is found in the first parameter (char). If the second
string is not found, returns a –1.
This function is zero-indexed (that
is, numbering of characters begins at 0).
|
|
|
|
For single-byte languages. Returns
the substring of bytes in the first parameter (char) starting at the
position (in bytes) indicated by the second parameter (int) and
continuing to the position indicated by the third parameter (int). The string
is zero-indexed (that is, numbering of bytes begins at 0). If the third
parameter is not included, the function returns bytes to the end of the
string.
For example, to set the value of a
field to six bytes of the Location field, skipping a three-byte prefix,
enter:
SUBSTR($Location$, 3, 8).
|
|
|
|
For single-byte or multi-byte
languages. Returns the substring of characters in the first
parameter (char) starting at the position (in characters) indicated
by the second parameter (int) and continuing to the position indicated by the
third parameter (int). The string is zero-indexed (that is, numbering of
characters begins at 0). If the third parameter is not included, the function
returns characters to the end of the string.
For example, to set the value of a
field to six characters of the Location field, skipping a three-character
prefix, enter:
SUBSTRC($Location$, 3, 8).
|
|
|
|
Returns the time portion of the time
stamp.
|
|
|
|
Returns the truncated value of a real
number. For example, 5.1 through 5.9 are truncated to 5.
|
|
|
|
Returns all characters in the string (char)
as uppercase characters.
Note:
No conversion will be performed to double-byte alphabetic characters.
|
|
|
|
Returns the weekday of the time stamp
(1 to 7, where 1=Sunday and 7=Saturday).
|
|
|
|
Returns the year portion of the time
stamp.
|