Data model

Multivalued reference fields

Support for multivalued reference fields is extended on record profiles, data change requests, and reporting.

These enhancements are enabled by default in your Network instance.

Profiles

Multivalued fields can now be edited on record profiles. Previously, the fields were locked.

CDA fields

This enhancement does not apply to multivalued CDA fields. These fields remain read-only on profiles:

  • all_degree_cda__v

  • all_spec_cda__v

  • all_spec_group_cda__v

Edit multivalued fields

When the profile page is in Edit mode, you can search for values, add, remove and sort values on the fields.

Click the Undo icon to revert the change.

Add values

Values display on the field in the order that they are added.

A maximum of 50 values can be added to each field. When the limit is reached, the remaining values are dimmed and cannot be selected.

Sort values

You can change the order of the values so they are listed by priority on the profile.

Click the field and hover over a value to display the Handle icon. Use the icon to move the value into a different position in the list.

Data change requests

Data stewards can identify and manage changes to multivalued fields. Previously, the fields were read-only.

CDA fields

These CDA fields remain read-only on DCRs:

  • all_degree_cda__v

  • all_spec_cda__v

  • all_spec_group_cda__v

Identify changes

Before editing the field, Data Stewards can quickly identify the values that are requested to be changed.

  • New values - Highlighted in green color.

  • Removed values - Dimmed with a strikethrough.

  • Current values - Highlighted in gray color.

When Data Stewards click the Edit icon, the highlighting and strikethroughs are removed. Data Stewards can search for values, add, remove, and sort the values.

Sort values

Data Stewards can change the order of the values in the list. The order that is defined when the DCR is approved is the order the values will display on the record profile.

To reorder the fields, click the Sort icon and choose one of the following options:

  • Order by Requested Value - (Default) Display the requested values at the beginning of the list.

  • Order by Current Value - Display the current value on the record at the beginning of the list. The newly requested values will move to the end of the list.

To manually reorder values so they are listed by priority, click the field and hover over a value to display the Handle icon. Use the icon to move the value into a different position in the list.

Submit DCRs from the Network API

Integration users can submit data change requests on custom multivalued reference fields.

A maximum of 50 values are supported for each field.

Exceeded limit

If the limit is exceeded, the changes for that field will be automatically rejected.

The following Resolution Note will be applied to the task:

System Rejected - Multivalued field's reference code count has exceeded the limit. 

The result in the API will be CHANGE_REJECTED.

Reporting

Advanced reporting users can run queries on multivalued reference fields in the SQL Query Editor (Reports). Previously, when the fields were used, they were treated as a String.

The following SQL functions are now available to query the fields.

multivalued_size

Returns the number of values (integer) in a multivalued field.

Syntax

multivalued_size(<multivalued field name>)

Example query

This query returns a count of the values in the all_specialties__c multivalued field.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_size (all_specialties__c)
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • Multivalued_size - A count of the field values.

If a record has no values for the field, the multivalued field name column is empty and the Multivalued_Size column value is 0.

multivalued_index

Returns the numerical position of the provided reference code in the multivalued field (or null if the value is not present).

Syntax

multivalued_index(<multivalued field name>, '<ref code>')

Example query

In this example, the query returns the position of the MSR value for the all_specialties__c field for each record.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_index (
            all_specialties__c,
            'MSR'
        ) AS md_spec__idx
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the value sin the order they display on the record.

  • MD_SPEC_IDX (custom column name) - The placement of the defined reference code in the list.

    In the example results, the MSR value is in the eighth (8) position for the first three records. It is not a value in the fourth record so 0 displays.

If a record has no values for the field, the multivalued field name column is empty and the MD_SPEC_IDX column value is 0.

multivalued_value

Use to extract a single reference code at a given position.

Syntax

multivalued_value(<multivalued field name>, index)

Example query

This query extracts the values in the first (1) and second (2) position from the all_specialties__c multivalued field.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_value (
            all_specialties__c,
            1
        ) AS primary_spec,
        multivalued_value (
            all_specialties__c,
            2
        ) AS secondary_spec
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • Primary_spec (custom column name) - The reference code that is listed as the first value in the multivalued field.

  • Secondary_Spec (custom column name) - The reference code that is listed as the second value in the multivalued field.

The columns are null if there are no values on the field for a record.

multivalued_has

Returns True or False if the field contains all the provided reference codes in any order.

Syntax

multivalued_has(<multivalued field name>, '<ref code 1>', '<ref code 2>', ... )

Example query

This query returns True if the MSR and CTR reference codes are listed as a value anywhere on the all_specialties__c multivalued field, otherwise, False is returned.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_has (
            all_specialties__c,
            'MSR', 'CTR'
        ) AS is_radiology
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • Is_Radiology (custom column name) - The value is True (t) if all the reference codes (MSR and CTR) are listed on the multivalued field. The value is False (f) if all the reference codes are not listed.

    In these example results, the first and third record contain both values (in any position), so True is returned. The second record contains only one of the values and the fourth record contains neither value so False is returned.

multivalued_contains

Use to find if the multivalued field contains all the provided reference codes. The order that the codes are listed in the field is important. Returns True or False.

Syntax

multivalued_contains(<multivalued field name>, '<ref code 1>', '<ref code 2>', ...)

Example query

This query returns True if the MSR and CTR reference codes are listed as a value in that order on the all_specialties__c multivalued field, otherwise, False is returned.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_contains (
            all_specialties__c,
            'MSR', 'CTR'
        ) AS is_radiology
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • Is_Radiology (custom column name) - The value is True (t) if the MSR and CTR reference codes are listed in the specified order on the multivalued field.

    The value is False (f) if those reference codes are not listed or they are listed in a different order.

multivalued_anyOf

Returns True or False if the multivalued field contains any of the provided reference codes in any order.

Syntax

multivalued_anyOf(<multivalued field name>, '<ref code 1>', '<ref code 2>', ...)

Example query

This query returns True if either of the CTR or MSR reference codes are listed as a value anywhere on the all_specialties__c multivalued field, otherwise, False is returned.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_anyOf (
            all_specialties__c,
            'CTR',
            'MSR'
        ) AS is_Radiology
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • Is_Radiology (custom column name) - The value is True (t) if the CTR and/or MSR reference codes are values on the multivalued field (in any order).

    The value is False (f) if neither reference code is not listed.

multivalued_firstOf

Returns the first reference code that displays on the multivalued field from the provided reference codes (order does not matter).

Syntax

multivalued_firstOf(<multivalued field name>, '<ref code 1>', '<ref code 2>', ...)

Example query

This query returns the reference code that is listed first on the all_specialties__c multivalued field from the provided values (AI, AM, CMG). If none of the reference codes are listed as the value on the field, null is returned.

SELECT
        vid__v,
        all_specialties__c,
        multivalued_firstOf (
            all_specialties__c,
            'AI',
            'AM',
            'CMG'
        ) AS first_spec
    FROM
        hcp
    ORDER BY
        all_specialties__c ASC

Results

This query returns the following details in the results:

  • VID - The ID of the record.

  • Multivalued field name - A list of the reference codes for the values in the order they display on the record.

  • First_Spec (custom column name) - The reference code that is listed first on the multivalued field from the list of provided values.

    The value is null if none of the reference codes are listed.

Joins on multivalued fields

You can use the multivalued functions and join against other reporting tables, for example, reference language tables or revision history tables.

Translation query example

This query extracts the records that have the AM reference code as the first value on the all_specialties__c multivalued field. Then, it returns the English label for the AM code.

SELECT
        vid__v,
        last_name__v,
        hcp_status__v,
        specialty_code.en AS specialty_1_english
    FROM
        hcp JOIN reference_lang specialty_code
            ON specialty_code.code = multivalued_value (
            all_specialties__c,
            1
        )
    WHERE
        multivalued_has (
            all_specialties__c,
            'AM'
        )
        AND reference_type = 'Specialty'

Results

The English label displays for the AM reference code: Allergy & Immunology.

Cluster management

Updated cluster codes are available for the following country/provider combinations:

  • Germany - Insight Health™

    The new cluster version is Version 3.0.

  • Spain - IQVIA™

    The new cluster version is Version 4.0

The new cluster version is available by default if you have the country/provider combination enabled in your Network instance.

Update addresses

To update addresses with the latest cluster codes:

  1. In the Admin console, click Data Model > Cluster Management.

  2. Select the country/provider cluster configuration.

  3. In the Cluster Management Details section, expand the Cluster Version field and choose the newest version.

  4. Save your changes.

  5. Click Refresh Addresses to run a data maintenance job to ensure that all addresses for the country have the latest cluster codes.

The new cluster version is available by default if you have these country/provider combinations enabled in your Network instance.