Item Rule Utility Functions
Use these functions in building more complex expressions.
Date Operators
The +
(plus) and -
(minus) operators add or subtract the specified
number of days from a date. A single number is interpreted as a number
of days. You can also use dates in the format specified by the standard
ISO 8601.
Syntax:
expression1 + expression2
expression1 - expression2
Example:
The following example expresses a time 3 days after the Item Lead Time
[Item].[Logistics].[LeadTime] + 3
auto_sequence
Syntax:
auto_sequence("Sequence Name", starting_num, increment_by)
Searches for Sequence Name
in the database tables. The sequence name
isn't case-sensitive.
If
the sequence exists, then the function returns the next value from the sequence. If the
sequence doesn't exist, then the function creates a sequence with the specified name and
returns starting_num
. Further numbers in the sequence are incremented
by increment_by
. This function can be used in defining the return value
of assignment and validation rules.
Example: The following expression used as the return
value of a rule whose target attribute is [General
attributes].[EDC Number]
produces a sequence of numbers beginning
at 1000 and increasing by 5 for each new item:
auto_sequence("EDC Number", 1000, 5)
between
Syntax:
between(value, minimum, maximum)
Returns TRUE if value
falls between minimum
and maximum
. Returns FALSE otherwise. All arguments
are of type Number, Date, or DateTime.
decode
Syntax:
decode(expression, search1, result1, [search2, result2, ...], [default])
Compares expression
to the specified series of search
arguments, one at a time, and returns the corresponding result
when the first match is found. If no
match is found, returns default
. If default
is omitted, returns null. Requires
specification of at least expression,
search1
, and result1
. You can specify an unlimited number of pairs of search
and result
arguments. The default
argument is
optional.
Example:
The following example returns RED COLOR if the item's body color is RED, BLUE COLOR if it's BLUE, and causes a rejection if none of those colors is matched.
Severity: Reject
If Expression: decode([Item].[BODY ATTR].[COLOR], "RED", "RED COLOR", "BLUE", "BLUE COLOR", "NONE") == "NONE"
in
Syntax:
in(expression, value1, value2,...)
Returns TRUE if expression
is found in value1
, value2
, or other following value arguments.
Returns FALSE otherwise. Requires specifying at least expression
and value1
. You can specify an unlimited number of value
arguments.
Example:
The following example returns TRUE if "RED" is contained in the value of either of the specified attributes for colors.
If Expression: in("RED",[Item].[BODY ATTR].[COLOR], [Item].[COVER ATTR].[COLOR])
The functions in()
and not_in()
accept arguments of
either String or Number. All arguments must be of the same type. The
type of the first argument determines the type assigned to the subsequent
arguments.
not_in
Syntax:
not_in(expression, value1, value2,...)
Returns TRUE if expression
isn't found in value1
, value2
, or other following value arguments.
Returns FALSE otherwise. Requires specifying at least expression
and value1
. You can specify an unlimited number of value
arguments.
Example:
The following example returns TRUE if "RED" isn't contained in the value of either of the specified attributes for colors.
If Expression: not_in("RED",[Item].[BODY ATTR].[COLOR], [Item].[COVER ATTR].[COLOR])
to_number
Syntax:
to_number(string)
Returns string
as
a value of type Number.
Example:
The following example returns the count of an item as a string.
to_number([Item].[PHYSICAL PROP].[COUNT])