Data model

Network expression (NEX) rules

Several new functions are available by default in your Network instance.

LOOKUPN()

This function is used specifically for lookup tables.

Use this function to lookup all values matching a key in the defined lookup table. For example, you can use the functions to store a product family in a custom field. This new function is very similar to the LOOKUP() function, but it returns all matching values on the table; the LOOKUP() function ends after it finds the first value.

By default, LOOKUPN() returns 100 results. You can 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.

Syntax

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

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.

JOIN()

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

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

Syntax

JOIN(<collection>,<separator>)

Example NEX rule

In a source subscription, create an after update rule for the lookup table, address__t, that returns a maximum of 10 postal codes from the United States and Spain. Format the returned array so each value is separated by a pipe (|).

[
  "postal_code_list__c = JOIN( LOOKUPN('address__t', 'postal_code__v', 10, country__v: ['US', 'ES']), '|')"
]

Support for field collections

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

Example

You can include some of the specialty set of fields (speciality_1__v to specialty_10__v) in the LOOKUPN() function.

JOIN(LOOKUPN('speciatlymatrix__t', 'product_family_name__c', product_specialty__c: [specialty_1__v, specialty_2__v, specialty_3__v, specialty_4__v, specialty_5__v], product_specialty_indicator__c: 'E'), ';')

GETTARGETTYPE()

The GETTARGETTYPE() function can be used to check the entity type of the record. This function can be used when you write Network Expression (NEX) rules in source subscriptions and for custom fields.

The function is available by default in your Network instance.

Syntax

GETTARGETTYPE()

Example 1

In this example, the function is used on a custom field to add an alternate key to HCP and HCO records. The format of the alternate key is determined by the entity type of the record. For example, if the entity type is HCP, prefix the key with HCP (HCP-<ID_number>).

IF(gettargettype() == 'HCP',
NEWALTERNATEID('HCP-{#########}', 'SEQ:100000000,999999999,HCP'),
NEWALTERNATEID('HCO-{#########}', 'SEQ:100000000,999999999,HCO'))

Example 2

In this example, the function is on a custom field to concatenate the entity type with a value; for example, the Network entity ID (VID).

concat(gettargettype(),vid__v)

Source subscriptions

The GETTARGETTYPE() function can be used in any of the rule points: File Preparation, Transformation, After Update, and Feed Acceptance.

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.

DELETE()

Use this function to delete objects and sub-objects during data load. Sets the record state to DELETED (effectively removing objects and sub-objects from Network) and the status to INACTIVE, and inactivates all custom keys on the object and sub-objects.

Syntax

DELETE()

Example

In this example, if the main object's record state is DELETED, delete the related sub-objects for the record, and inactive all custom keys for the main object and sub-objects.

IF(hco_status__v=='D',DELETE())