SELECT statement in NEX rules

AD
DM

Using SELECT, you can retrieve data from sub-objects and relationship objects to populate entity level fields. For example, you can pull DEA numbers from the License sub-object to populate a field on HCP records. You can then map the fields to Veeva CRM or any other downstream systems.

The SELECT statement and supported functions can replace the need to use custom scripts in combination with NEX rules.

Supported features

The SELECT statement can be used anywhere in Network where NEX rules are supported.

For example, use in NEX rules in the following features:

  • source subscriptions (Transformation and After Update rule points)

  • data model fields

  • transformation rules

  • OpenData subscriptions (filtering records)

Source subscription considerations

When NEX expressions are used in source subscriptions, line breaks are not supported. The NEX expression must be one line.

Supported functions and options

Functions

Use SELECT with the following functions:

  • ALL

    Syntax 

    SELECT ALL(<expression> FROM <expression> AS 'alias' WHERE <expression> ORDER BY <expression> LIMIT <number>)

  • ANY 

    SELECT ANY(<expression> FROM <expression> AS 'alias' WHERE <expression>)

Options

SELECT can also be used with the following options:

  • SELECT FIRST

    Scans sub-object records owned by a top level entity (example, HCO, HCP) to return the first sub-object that meets the defined criteria given the defined ORDER.

    Syntax 

    SELECT FIRST(<expression> FROM <expression> AS 'alias' WHERE <expression> ORDER BY <expression>)

  • SELECT DISTINCT

    Scans sub-object records owned by a top level entity (example, HCO, HCP) to return the collection of unique sub-objects that meet the defined criteria.

    Syntax 

    SELECT DISTINCT(<expression> FROM <expression> AS 'alias' WHERE <expression> ORDER BY <expression>)

For details, see the examples below.

Example 1

A NEX rule on an HCP custom field. The rule looks through the addresses on the HCP record and populates the field with the first primary address that meets the following criteria:

  • address_status__v value is A (Active)

  • record_state__v value is VALID or UNDER_REVIEW

  • address_ordinal__v is 1

    If multiple addresses have an ordinal of 1, use the most recently modified address

If multiple addresses match all conditions above, the Veeva ID (VID) is used as the tiebreaker.

NEX rule

The rule uses the SELECT statement with the FIRST function.

SELECT FIRST (addr.vid__v FROM addresses__v AS addr WHERE addr.address_primary__c == 'Y' OR (addr.address_ordinal__v == 1 AND addr.address_status__v == 'A' AND (addr.record_state__v IN [ 'VALID', 'UNDER_REVIEW' ])) ORDER BY addr.modified_date__v, vid__v DESC)

NEX rule for source subscriptions

Use this syntax if you are using the rule in a source subscription.

[
"custom_field__c = SELECT FIRST (addr.vid__v FROM addresses__v AS addr WHERE addr.address_primary__c == 'Y' OR (addr.address_ordinal__v == 1 AND addr.address_status__v == 'A' AND (addr.record_state__v IN [ 'VALID', 'UNDER_REVIEW' ])) ORDER BY addr.modified_date__v, vid__v DESC)"
]

Result

The HCP field is populated with the address VID.

Example 2

A NEX rule on an HCP custom field that uses a lookup table to populate the field.

Description

In this example, the rule accesses the lookup table called citytier_t.csv. It will return the first value from the locality_v field that meets the following criteria:

  • record_state__v value is VALID or UNDER_REVIEW

  • vid__v is not null

Note: If multiple addresses match all the conditions above, the VID is used as the tiebreaker.

NEX rule

Use the SELECT statement with the FIRST function to return a value from a lookup table and update a custom field. 

LOOKUP('citytier__t', 'city_tier_cn__c', locality__v: SELECT FIRST (addr.city_tier_cn__c FROM addresses__v AS addr WHERE addr.record_state__v IN ['VALID', 'UNDER_REVIEW'] AND addr.vid__v != null ORDER BY addr.vid__v)) ?? '1'

NEX rule for source subscriptions

Use this syntax if you are using the rule in a source subscription.

[
"custom_field__c = LOOKUP('citytier__t', 'city_tier_cn__c', locality__v: SELECT FIRST (addr.city_tier_cn__c FROM addresses__v AS addr WHERE addr.record_state__v IN ['VALID', 'UNDER_REVIEW'] AND addr.vid__v != null ORDER BY addr.vid__v)) ?? '1'"
]

Result

The HCP custom, city_tier_cnc__c, field is populated with the appropriate tier from the first city in the lookup table that met the address criteria.

Example 3

A NEX rule on an HCP custom field that populates the field with a separated list of specific values.

Description

In this example, the rule looks through the licenses for specific states on the record. It populates the field with a concatenated list of those states if the license has the following criteria:

  • license_status__v value is A (Active)

  • record_state__v value is VALID

  • type__v value is state (case insensitive)

Other requirements:

  • preserve the order that is presented in the state collection

  • if the HCP does not have a license in the state collection, return NULL

NEX rule

This rule uses SELECT with the DISTINCT function.

(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' AND (lic.type_value__v IN ['CA', 'CO', 'WA', 'DEA', 'NY']) ORDER BY lic.type_value__v) AS result, IF(ISEMPTY(result), null, JOIN(result, ';')))

NEX rule for source subscriptions

Use this syntax if you are using the rule in a source subscription.

[
"custom_field__c = (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' AND (lic.type_value__v IN ['CA', 'CO', 'WA', 'DEA', 'NY']) ORDER BY lic.type_value__v) AS result, IF(ISEMPTY(result), null, JOIN(result, ';')))"
]

Result

The field is populated with a concatenated list of states from the type_value__v field separated by a semi-colon. If none of the licenses meet the criteria, null is returned.

Example results

The HCP record contains the following licenses.

The custom field is populated with the specific states that met the criteria defined in the NEX rule.

Example 4

A NEX rule on an HCP custom field that looks through the licenses on the record and populates the field with the collection of license values.

Description

Populate a field with all active state licenses on the HCP record.

The licenses must have the following criteria:

  • type_value__v is not empty

  • license_number__v is not empty

  • type__v value is state (case insensitive)

  • license_status__v value is A (Active)

  • record_state__v value is VALID or UNDER_REVIEW

Other requirements

  • populate each license as <type_value_v> - <license_number_v>

  • sort the collection

  • separate each value with a semi-colon (;)

  • add a trailing separator ';' if the field value is not empty

  • return empty string if licenses don't exist or are empty

NEX rule

This rule uses SELECT but does not define a function. If a function isn't defined , ALL is assumed. 

(JOIN(STRINGSORT(SELECT (CONCAT(lic.type_value__v, ' - ', lic.license_number__v) FROM licenses__v AS lic WHERE !ISEMPTY(lic.type_value__v) AND !ISEMPTY(lic.license_number__v) AND UPPERCASE(lic.license_status__v) == 'A' AND (UPPERCASE(lic.record_state__v) IN ['VALID', 'UNDER_REVIEW']))), ';') AS result, IF(!ISEMPTY(result), CONCAT(result, ';'), result))

NEX rule for source subscriptions

Use this syntax if you are using the rule in a source subscription.

[
"custom_field__c = (JOIN(STRINGSORT(SELECT (CONCAT(lic.type_value__v, ' - ', lic.license_number__v) FROM licenses__v AS lic WHERE !ISEMPTY(lic.type_value__v) AND !ISEMPTY(lic.license_number__v) AND UPPERCASE(lic.license_status__v) == 'A' AND (UPPERCASE(lic.record_state__v) IN ['VALID', 'UNDER_REVIEW']))), ';') AS result, IF(!ISEMPTY(result), CONCAT(result, ';'), result))"
]

Result

The custom field is populated with a concatenated list of values, <type_value__v> - <license_number__v>, separated by semicolon. If none of the licenses meet the criteria, null is returned.

Example results

The HCP record contains the following licenses.

The custom field is populated with the list of license values that meet the criteria defined in the NEX rule.