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:
-
In the Admin console, click Data Model > Cluster Management.
-
Select the country/provider cluster configuration.
-
In the Cluster Management Details section, expand the Cluster Version field and choose the newest version.
-
Save your changes.
-
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.