NEX variables

Sometimes, complex business logic is required in Network Expression (NEX) rules to support specific use cases.

Use variables in your NEX rules helps you to write and maintain these complex business rules.

Benefits

  • NEX rules are easier to read and modify in the future.

  • Re-use lengthy calculations within NEX expressions.

Comparison

Review the difference between creating rules with and without variables to perform a simple union.

NEX rule - Without variables

Directly perform the UNION operation. 

[ "Oncology", "Pediatrics", "Vascular Surgery" ] UNION [ "Oncology", "Pediatrics", "General Surgery" ]

NEX rule - With variables

  • Enclose the entire rule with parentheses ().

  • Define variables using the AS keyword (for example, set1 and set2)

  • Use the defined variables in subsequent expressions.

(
[ "Oncology", "Pediatrics", "Vascular Surgery" ] as set1,
[ "Oncology", "Pediatrics", "General Surgery" ] as set2,
set1 UNION set2
)

Example 1

Populate a list of valid state licenses if the HCP has an MD.

Using variables

(
SELECT DISTINCT (lic.type_value__v
FROM licenses__v AS lic
WHERE lic.license_status__v == 'A'
AND UPPERCASE(lic.record_state__v) == 'VALID'
AND UPPERCASE(lic.type__v) =='STATE') AS license_list,

IF(ISEMPTY(license_list)
OR medical_degree_1__v !='MD' ,
null,
JOIN(license_list,
';'))
)

Without variables

IF(ISEMPTY(SELECT DISTINCT (lic.type_value__v
FROM licenses__v AS lic
WHERE lic.license_status__v == 'A'
AND UPPERCASE(lic.record_state__v) == 'VALID'
AND UPPERCASE(lic.type__v) =='STATE'))
OR medical_degree_1__v !='MD' ,
null,
JOIN(SELECT DISTINCT (lic.type_value__v
FROM licenses__v AS lic
WHERE lic.license_status__v == 'A'
AND UPPERCASE(lic.record_state__v) == 'VALID'
AND UPPERCASE(lic.type__v) =='STATE'),
';'))

Example 2

Use a lookup table and NEX rule to populate the record type field based on the HCP type.

With variables

(
LOOKUP('table__t',
'record_type',
hcp_type: hcp_type__v) AS record_type_result,

IF(ISEMPTY(record_type_result),
'other',
record_type_result)
)

Without variables

IF(ISEMPTY(LOOKUP('table__t',
'record_type',
hcp_type: hcp_type__v) ),
'other',
LOOKUP('table__t',
'record_type',
hcp_type: hcp_type__v) )

Example 3

Define a transformation rule to specify that only the following addresses are pushed to CRM:

  • addresses with a CRM key

  • Professional addresses from OpenData

  • primary addresses

With variables

(
SELECT FIRST (ck.vid__v
FROM custom_keys__v AS ck
WHERE ck.custom_key_status__v=='A'
AND CONTAINS(ck.custom_key_source_type__v,
"VCRM")
ORDER BY ck.modified_date__v DESC ) AS ckmatch,
if( primary_address__c == 'Y', TRUE, FALSE) as primary_address,
if (address_type__v == 'P' AND record_owner_type__v == 'VOD', TRUE, FALSE) as professional_vod_address,

if (address_status__v == 'A' AND country__v == 'US' AND(record_state__v=='VALID' OR record_state__v=='UNDER_REVIEW')
AND ((!ISNULL(ckmatch)) OR primary_address OR professional_vod_address),"A","I")
)

Without variables

if (
address_status__v == 'A' AND country__v == 'US' AND(record_state__v=='VALID' OR record_state__v=='UNDER_REVIEW') AND
(
(!ISNULL(SELECT FIRST (ck.vid__v
FROM custom_keys__v AS ck
WHERE ck.custom_key_status__v=='A'
AND CONTAINS(ck.custom_key_source_type__v,
"VCRM")
ORDER BY ck.modified_date__v DESC )))
OR ( primary_address__c == 'Y' )
OR (address_type__v == 'P' AND record_owner_type__v == 'VOD')) ,"A","I")