Multivalued reference fields

Multiple value reference fields are supported for Network data model fields. This includes Veeva fields, custom fields, and CDA fields.

Key highlights

  • Supports mapping to Vault CRM and Veeva CRM multivalued picklist fields

  • Supports fields in the Common Data Architecture (CDA)

  • Fields can contain a maximum of 50 values

Supported objects and fields

  • Objects - Multivalued reference type fields can be created on all Veeva objects and custom objects.

  • Fields - Supported for new Veeva fields, custom fields, and CDA fields

    Note: Existing fields cannot be changed to support multiple values.

Supported features

Multivalued reference fields are supported in the following Network features:

  • Data model (Network and CDA)

  • Record Profiles

  • Revision History

  • Data Lineage

  • Data Updater

  • Profile Layouts

  • Search (including Advanced Search)

  • Source Subscriptions

  • Target Subscriptions

  • Reporting - Advanced queries only (SQL Query Editor)

  • Vault CRM Bridge

Create a multivalued reference field

Administrators and Data Managers can create custom fields that support multiple values.

On the Custom Field configuration:

  1. In the Type list, choose Reference.

  2. Select the Allow Multiple Values? option.

    The option cannot be turned off after the field is created.

The field values will be stored as a comma separated list.

Updating fields

Multivalued fields can be updated by editing record profiles, using source subscriptions, and through the Data Updater.

Edit profiles

Multivalued fields can be edited on record profiles.

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.

Source subscriptions

Use this advanced property in source subscription configurations support these fields. The property defines the delimiter for multivalued fields. By default, the delimiter is a comma (,). 

feed.multivaluedattribute.separator: (string, default ",")

The import file should include a comma separated list of values for the field.

Field value limit

A maximum of 50 values can be included in the import file. If the file contains more than 50 values for one field, the record is skipped and a warning displays in the Job Error Log.

Profile page

On record profiles, you can view all values for the field. Four rows of values display by default. Use the scroll bar to see additional values.

Summary cards

Multivalued fields can be added as fields on the record summary header and sub-object summary cards. A few values display beside the field with a count of additional fields. Click the count to open a pop-up that displays all the field values.

Fields are configured for these summaries in the profile layout (Data Model > Profile Layout). Edit the field and select the Is Summary Field? option.

View revisions

Multivalued fields display in the Network features that identify revisions so you can review and compare the revisions of the record and field.

The following features are updated to support the fields:

  • Revision History

  • Field Revisions

  • Version History tab (Profile page)

  • Data Lineage

Example Revision History

The Version columns display a comma separated list and a count of additional values. Click the count to open a pop-up to display all values.

Use the search bar to find a specific value. A count of all values displays below the search bar.

Example Field Revision

All values also display on the Field Revisions dialog. Click the count to expand the value list.

Export field revisions

If you export field revisions, the values display in the exported .csv file as a comma separated list containing the reference code and label.

Example

Data change requests

Data stewards can identify and manage changes to multivalued fields.

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.

Exporting data

Target subscriptions are updated to include a new setting so you can define the delimiter for multivalued fields that are sent to downstream systems.

In the General Export Options section, the Multiple Value Fields Delimiter option is set to a comma (,) by default. Change the delimiter if the downstream system expects a different delimiter.

The following delimiters are supported:

  • , (comma) (default)

  • : (colon)

  • ; (semi-colon)

  • tab

  • | (pipe)

Reporting

When multivalued fields are used in the SQL Query Editor, they are treated as a String. Values are stored as separated list of reference codes: (V1,V2,V3)

Query tips

  • Using SELECT * or SELECT <multi_value_field>__v, the value will be returned as 'V1,V2,V3'

  • To filter the table on exact match use:

    WHERE <multi_value_field>__v = 'V1,V2,V3'

  • To filter on a single value, use string functions.

    Example 

    WHERE <multi_value_field>__v LIKE '%V1%'

    or

    WHERE STRPOS(<multi_value_field>__v, 'V1`)>0

  • To filter on any of values, repeat the conditions for each of the expected matches.

    Example

    To get matches on either V1 or V2

    WHERE (<multi_value_field>__v LIKE '%V1%' OR <multi_value_field>__v LIKE '%V2%')

  • To filter on a set of values, repeat the conditions for each of expected matches.

    Example

    To get matches on both V1 and V2

    WHERE (<multi_value_field>__v LIKE '%V1%' AND <multi_value_field>__v LIKE '%V2%')

  • Order is evaluated on the value list as string literals.

    Example

    'V1,V2,V3' comes before 'V2,V3' in the following:

    ORDER BY <multi_value_field>__v

SQL functions

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 available to query the fields:

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

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

  • multivalued_value - Use to extract a single reference code at a given position.

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

  • 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.

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

  • multivalued_firstOf - Returns the first reference code that displays on the multivalued field from the provided values (order does not matter).

For example queries and results, see Sample multivalued field queries.

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.

Network API

Retrieve Field Details MetaData API

The multivalued parameter identifies multivalue reference fields (True/False).

For details, see the Veeva Network Developer Help.

Search API considerations

To refine the results for multivalued fields, use the fieldQueries parameter. The filters parameter is not supported for multivalued fields.