NEX functions

This list includes functions currently supported for rule expressions.

ALL

Traverses from the parent HCP or HCO to its associated sub-object records to determine whether all of them meet the defined criteria. Returns a Boolean.

ALL(sub-object set name WHERE expression)

ANY

Traverses from the parent HCP or HCO to its associated sub-object records to determine whether any of them meet the defined criteria. Returns a Boolean.

ANY(sub-object set name WHERE expression)

CLEANSE_STRING

Use this function with a data cleansing dictionary (lookup table) to cleanse and standardize data in text fields. For more information, see Data cleansing.

CLEANSE_STRING(field_name, 'data_cleansing_dictionary_name')

CONCAT

Combines attributes and static values. Returns a string.

CONCAT(string1, string2, string3...)

CONTAINS

Determines whether the value contains the specified characters. Returns a Boolean.

CONTAINS(string, search string)

COUNT

Traverses from the parent HCP/HCO to its associated sub-object records to determine the number of sub-objects meeting the defined criteria. Returns a number.

COUNT(sub-object set name WHERE expression)

DATE

Converts the input value into a date, for example MM-DD-YYYY. Returns a date.

STRING(DATE(value, date pattern))

The TODAY, NOW, DATE, and DATETIME functions are returned in epoch or long format, which Network will reject unless you convert the output to string using the STRING function.

For more information on date patterns, visit http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html.

Note: For matching purposes, the STRING function must be used to convert the date to a string. If the date is not a string, it cannot be identified by the match process.

DATEADD

Enables you to add or subtract time periods to or from date values. The date parts supported are: YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND. If the integer is positive, it is added to the date. If it is negative, it is subtracted from the date. Returns date/datetime types.

DATEADD(date part, integer, date attribute)

DATETIME

Converts the input value to datetime format.

STRING(DATETIME(value, date pattern))

The TODAY, NOW, DATE, and DATETIME functions are returned in epoch or long format, which Network will reject unless you convert the output to string using the STRING function.

For more information on date patterns, visit http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html.

Note: The date format syntax is case sensitive.

DELETE

Delete objects and sub-objects during data load. Sets the record state to DELETED and the status to INACTIVE, and inactivates all custom keys on the object and sub-objects.

DELETE()

Note: This function can be used only for File Preparation and Transformation rule points in source subscriptions. The function must be called before the data gets to the merge stage.

DOUBLE/LONG

Converts the input value into a number using an optional number pattern.

DOUBLE(value, number pattern)

LONG(value, number pattern)

For more information on number patterns, see http://docs.oracle.com/javase/tutorial/i18n/format/decimalFormat.html.

DROP

Enables you to drop sub-objects (including address, license, and parent HCOs) under the post-merge rule point.

DROP()

This function discards the record without a warning message in the Job Details UI. If you require a message, use the REJECT function instead.

EXISTS

Traverses from the parent HCP or HCO to its associated sub-object records to determine whether any exist. Returns a Boolean.

EXISTS(sub-object set name)

FAILJOB

Causes the job to stop, with a failure code and the message you supply. Returns a log message.

FAILJOB(log message)

FIND

Determines the character position of a string. Returns a number.

FIND(string, search string)

GETTARGETTYPE

Check the entity type of the record. Available to use in source subscriptions and custom fields.

GETTARGETTYPE()

Note: If the function is used in the File Preparation stage, the item type (alias name) is returned because the object is not yet mapped to the Network object type in that stage. For example, you might see ACCOUNT returned in the File Preparation stage, which will be mapped to HCP or HCO in a later stage.

IF

Enables you to evaluate an expression that returns a Boolean value, and to specify what action to take, depending on that value. If you do not provide a False action, no action is performed when the expression evaluates to False. Returns value type based on the condition.

IF(condition, value if true, value if false)

INVALIDATE

Sets the record state to INVALID (effectively removing it from Network) and the status to INACTIVE, inactivates all custom keys, and invalidates any related sub-objects for the record.

INVALIDATE()

Note: This function can be used only for Transformation rule points in source subscriptions.

ISEMPTY

Same as ISNULL with the addition that if the value is blank ("") the function will also return True.

ISEMPTY(string)

ISNEWOBJECT

Indicates if the object is new to the system. Returns a Boolean.

ISNEWOBJECT()

ISNULL

Determine whether an attribute contains a value. In the subscription, you define how NULL is defined by the source. Based on that definition, this function returns true if the value is considered to be NULL by Network. Returns a Boolean.

ISNULL(any)

ISVALIDREF

Compares a value to a list of network codes for a given reference type. If the system is populated, it will leverage the aliases provided the ordering of entity model rules is "entity.model.rules": "network:nex, network:normalize_table, network:normalize_alias, network:normalize_cleanup".

ISVALIDREF(reference typecode).

JOIN

Use this function to format an array of values using a delimiter.

JOIN(<collection>,<separator>)

Note: This can be used with the LOOKUP and LOOKUPN functions.

LEFT

Parses out the number of defined characters from the left. Returns a string.

LEFT(string, number)

LEN

Returns the length (in characters) of an attribute. Returns a number.

LEN(string)

LOGDETAILWARN

Displays a warning in the job history for any record failing certain criteria.

Note that this is not specifically for lookups. It can be used for other NEX functions also, for example, REJECT().

LOOKUP

Used for lookup tables only.

Use to look for a value from a range (one or multiple columns in a row) and then returns a value in the same row. Returns the first value that is found in the table.

LOOKUP('lookup_table_name', 'column_name', <lookup_column_1>: <expression1>, <lookup_column_2>: <expression2>)

Support for field collections

A collection of fields can now be referenced in LOOKUP or LOOKUPN functions for NEX rules.

LOOKUPN

Used for lookup tables only.

Use this function to lookup all values matching a key in a lookup table. For example, you can use the functions to store a product family in a custom field. Returns all matching values on the table; by default, returns 100 results. Specify a parameter to limit the number of lookups returned. The maximum limit is 500 values. If more than 500 is defined, the NEX rule automatically fails. In a source subscription, the job will complete, and the error displays in the Job Error Log.

Tip: To ensure that the array returned by LOOKUPN can be easily managed in a field or another calculation, always use JOIN with LOOKUPN.

LOOKUPN('lookup_table_name', 'column_name', <limit_parameter>, <lookup_column_1>: <expression1>, <lookup_column_2>: <expression2>)

Note: If a field contains an empty value, the empty value is returned in the results. For example, if the lookup returns two values but the first value is null, the null value and separator displays with the text value.

LOOKUPVALIDATE

Used for lookup tables only.

Use to look for a value from a range (one or multiple columns in a row). Returns TRUE if value found, FALSE otherwise.

LOOKUPVALIDATE('lookup_table_name',<lookup_column_1>: <expression1>, <lookup_column_2>: <expression2>)

LOWERCASE

Sets all characters to lowercase. This cannot be applied to Veeva-owned fields in the local instance. Returns a string.

LOWERCASE(string)

MAX

Returns the highest value specified. Each value must be of the same type; for example, date. NULL values are ignored unless all are NULL, in which case NULL is returned. Accepts string, number, or date/datetime types.

MAX(value1, value2, value3...)

MID

Parses out the number of defined characters from the defined midpoint. Returns a string.

MID(string, number, number)

MIN

Returns the lowest value specified. Each value must be of the same type; for example, date. NULL values are ignored unless all are NULL, in which case NULL is returned. Accepts string, number, or date/datetime types.

MIN(value1, value2, value3 ...)

NOW

Returns both the date and time in GMT.

STRING(NOW())

Note: The TODAY, NOW, DATE, and DATETIME functions are returned in epoch or long format, which Network will reject unless you convert the output to string using the STRING function.

PROPERCASE

Sets value to its proper case. This cannot be applied to Veeva-owned fields in the local instance.

PROPERCASE(string)

REGEXCONTAINS

Enables you to use regex to identify aspects of the value. For more information on regex syntax, visit http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html.

REGEXCONTAINS(string, regex)

REGEXREPLACE

Enables you to use regex to update aspects of the value. For more information on regex syntax, visit http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html.

REGEXREPLACE(string, regex, replacement)

REJECT

Rejects the record and generates a job warning. If you do not provide a log message, the rule syntax appears with an explanation for the rejection. Adding a log message helps significantly with troubleshooting.

REJECT(log message*)

Parses out the number of defined characters from the right. Returns a string.

RIGHT(string, number)

SETI

Loads dynamic attribute data from Veeva CRM.

STRING

Converts the input value into a string. This is typically used to prepare data for Network. Other conversion functions are used to prepare a value to go through another function. The pattern is optional, depending on the value.

STRING(value, pattern)

TABLEEXIST

Checks for the existence of a lookup table and returns TRUE if exists, FALSE otherwise.

Note: Use only as a Feed Acceptance rule.

TABLEEXIST('lookup_table_name')

TODAY

Returns the current date at midnight in GMT. Uses the date format.

STRING(TODAY())

Note: The TODAY, NOW, DATE, and DATETIME functions are returned in epoch or long format, which Network will reject unless you convert the output to string using the STRING function.

TRIM

Returns the original value with the whitespace removed.

TRIM(string)

UPPERCASE

Sets all characters to uppercase. This cannot be applied to Veeva-owned fields in the local instance.

UPPERCASE(string)