Using rule expressions
Rule expressions (NEX rules) enable you to enforce data quality standards when you process externally sourced data through source subscriptions. You configure rule expressions using a set of functions and operators to define specific rules for a particular stage of the data load.
Note: Rule expressions defined in a source subscription must be enclosed in square braces ([ ... ]).
Rule points
Rule expression configuration includes a number of rule points that define transformations or data feed acceptance criteria at various stages of the data load.
The data load stages include:
- File preparation
- Transformation
- After update
- Feed acceptance
File preparation rules
File preparation rules are applied after Network reviews each file individually, but before it joins them. This rule point is often used to properly format custom keys.
If your subscription contains two files, they will ultimately be loaded into the same Network object (for example, HCO), but will have different rules applied based on the file.
For this example, there are two files: HCP_PARTY and STATE_ADDRESS.
Transformation rules
Transformation rules are applied after Network has modeled the files to the Network data model, before matching and merging. All functions are available for these rules except those that require traversal through a sub--object record. This includes the ANY and ALL functions.
Transformation rules can be used for cleaning strings by removing extra characters, or applying business rules specific to a particular subscription.
After update rules
After update rules are applied after Network has joined files and matched and merged records. All functions are available and are applied to the post-merge state of the data. These rules only run on an entity for which data is being changed; If the import is identical to existing data, they will not run.
Feed acceptance rules
Feed acceptance rules are triggered at various stages of the load, based on the record count at each stage.
Configure rule expressions
To configure rule expressions, perform the following actions in the Network Expression Rules section of a subscription:
- Click the Add Rule link.
- Select the rule point from the Rule Point drop-down list.
- Select the entity for which the rule applies from the File/Entity drop-down list.
- Type or paste your rule expression code in the Rule text area.
- Click the Verify link to verify your code.
- Click the Add Rule link to add more rule expressions.
Rule expression functions
| Function | Syntax | Output | Description |
|---|---|---|---|
| IF | IF(condition, value if true, value if false) | type of value chosen by condition |
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.
Note that you cannot make assignment statements within the "value if true" or "value if false" parameters. You can, however, use the The following will not work: IF(email_1__v == 'test12@test.com', email_2__v = 'DEFAULT@TEST.COM')Instead you would use: email_2__v = IF(email_1__v == 'test12@test.com', 'DEFAULT@TEST.COM', email_2__v) |
| LEN | LEN(string) | Number | Returns the length (in characters) of an attribute. |
| ISNULL | ISNULL(any) | Boolean | 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. |
| CONTAINS | CONTAINS(string, search string) | Boolean | Determines whether the value contains the specified characters. |
| FIND | FIND(string, search string) | Number |
Determines the character position of a string. For example, in a file where the first name and last name are in one column in the format "last name, first name" you would use: Last name = LEFT(NAME, FIND(NAME, ',')-1)First name = RIGHT(NAME, FIND(NAME, ',')+1)
|
| CONCAT | CONCAT(string1, string2,string3...) | String | Combines attributes and static values into a string. |
| LEFT | LEFT(string, number) | String | Parses out the number of defined characters from the left. |
| RIGHT | RIGHT(string, number) | String | Parses out the number of defined characters from the right. |
| MID | MID(string, number, number) | String | Parses out the number of defined characters from the defined midpoint. |
| TODAY | STRING(TODAY()) | Date |
Returns the current date at midnight in GMT. For example, 2019-04-22 00:00:00 GMT. Note that 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. |
| NOW | STRING(NOW()) | Datetime | Returns both the date and time in GMT. For example, 2019-04-22 07:30:44 GMT. Note that 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. |
| STRING | STRING(value, pattern) | 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.
For example: Note that 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. |
| NUMBER | NUMBER(value, number pattern) | Number |
Converts the input value into a number, for example, #,##0.00.
For more information on number patterns, see http://docs.oracle.com/javase/tutorial/i18n/format/decimalFormat.html. |
| DATE | STRING(DATE(value, date pattern)) | Date |
Converts the input value into a date, for example, MM-DD-YYYY.
For more information on date patterns, visit http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html. Note that 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. Important: 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. |
| DATETIME | STRING(DATETIME(value, date pattern)) | Datetime |
Converts the input value to datetime format, for example, DATETIME(HCP.birthday,'MM/dd/yyyy'). Note that the date format syntax is case sensitive. For more information on date patterns, visit http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html.
|
| PROPERCASE | PROPERCASE(string) | String | Sets value to its proper case. This cannot be applied to Veeva-owned fields in the local instance. |
| LOWERCASE | LOWERCASE(string) | String | Sets all characters to lowercase. This cannot be applied to Veeva-owned fields in the local instance. |
| UPPERCASE | UPPERCASE(string) | String | Sets all characters to uppercase. This cannot be applied to Veeva-owned fields in the local instance. |
| REGEXFIND | REGEXCONTAINS(string, regex) | Boolean | 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. |
| REGEXREPLACE | REGEXREPLACE(string, regex, replacement) | String | 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. |
| DROP | DROP() | Enables you to drop sub-objects (including address, license, and parent HCOs) under the post-merge rule point. For example: IF(address_type__v=='M',DROP())". The DROP function discards the record without a warning message in the job details UI. If you require a message, use the REJECT function instead. |
|
| INVALIDATE | 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. For example: IF(hco_status__v=='I',INVALIDATE()) (invalidate based on status) or IF(address_type__v=='M',INVALIDATE()) (invalidate based on address type). |
|
| REJECT | REJECT(log message*) | Message |
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. For example: IF(LEN(email_1__v) >= 16, REJECT(CONCAT('E-mail [', email_1__v, '] with length [', LEN(email_1__v), '] is too long'))) |
| TRIM | TRIM(string) | String | Returns the original value with the whitespace removed. |
| ISEMPTY | ISEMPTY(string) | Boolean | Same as ISNULL with the addition that if the value is blank ("") the function will also return TRUE. |
| DATEADD | DATEADD (date part, integer, date attribute) | Date/Datetime | 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. |
| FAILJOB | FAILJOB(log message) | Log message |
Causes the job to stop, with a failure code and the message you supply. For example: IF (feed__v.records < 1000, FAILJOB( 'Feed expected to contain 1000 or more records'))
"IF(((feed__v.rejected*100) / feed__v.records) > 50, FAILJOB(CONCAT('Count: ', feed__v.records, ' Rejected: ', feed__v.rejected, ' Ratio: ', (feed__v.rejected*100) / feed__v.records)))" |
| ALL |
ALL(sub-object set name WHERE expression) |
Boolean |
Traverses from the parent HCP or HCO to its associated sub-object records to determine whether all of them meet the defined criteria. For example: Compact syntax can be used to combine expressions, for example, in the following example:
|
| ISVALIDREF | ISVALIDREF(reference type, code) | Boolean |
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:
|
| EXISTS | EXISTS(sub- object set name) | Boolean |
Traverses from the parent HCP or HCO to its associated sub-object records to determine whether any exist.
For example: |
| ANY | ANY(sub-object set name WHERE expression) | Boolean |
Traverses from the parent HCP or HCO to its associated sub-object records to determine whether any of them meet the defined criteria.
For example: Compact syntax can be used to combine expressions, for example, in the following example:
|
| COUNT | COUNT(sub- object set name WHERE expression) | Number |
Traverses from the parent HCP/HCO to its associated sub-object records to determine the number of sub-objects meeting the defined criteria.
For example: For an HCP with a total of five addresses, where two are invalid and three are valid:
|
| ISNEWOBJECT | ISNEWOBJECT() | Boolean |
Indicates if the object is new to the system.
Example: is_new_object__c = ISNEWOBJECT() |
| MIN | MIN(value1, value2, value3 . . .) | String, Number, Date/Datetime |
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.
Example: MIN(created_date__v, NOW()) (suitable if the object is being updated, and has not just been created)MIN(DATE('12-12-2000', 'MM-dd-yyyy'), birth_date__v) |
| MAX | MAX(value1, value2, value3 . . .) | String, Number, Date/Datetime | 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.
Example: MAX(created_date__v, NOW()) (suitable if the object is being updated, and has not just been created)MAX(DATE('12-12-2000', 'MM-dd-yyyy'), grad_trg_end_date__v) |
Operators
| Operator | Description | Parameter field type | Return type |
|---|---|---|---|
| == | Equals | All | Boolean |
| != | Does not equal | All | Boolean |
| () | Parentheses (to indicate precedence), for example, IF (email_1_v == 'test4@test.com' OR email_1_v == 'test5@test.com'), REJECT() ) | All | Not applicable |
| > | Greater than | Number, Date, Datetime | Boolean |
| < | Less than | Number, Date, Datetime | Boolean |
| >= | Greater than or equal to | Number, Date, Datetime | Boolean |
| <= | Less than or equal to | Number, Date, Datetime | Boolean |
| + | Sum | Number | Number |
| - | Subtract | Number | Number |
| NOT | Reverse parameter, for example, NOT("1==2") = TRUE | Boolean | Boolean |
| AND - && | True if all parameters are true | Boolean | Boolean |
| OR - || | True if any parameters are true | Boolean | Boolean |
| IN | True if parameter one is in parameter two (chosen from multiple values), for example, IF ( email_1__v IN [ 'test4@test.com', 'test5@test.com' ], REJECT() ) | Parameter one - All, Parameter two - Collection | Boolean |