Sample multivalued field queries

AD
DM
DS
ST

Some reference fields support multiple values.

The following examples can help you to find details about the values in the fields.

Number of values in a field

Use the multivalued_size function to return the number of values (integer) in a multivalued field.

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

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

Find the position of a value in the field

Use the multivalued_index function to returns the numerical position of the provided reference code in the multivalued field (or null if the value is not present).

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

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

Find the value of a given position

Use the multivalued_value function to extract a single reference code at a given position.

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.

Query if the field contains all provided values (any order)

Use the multivalued_has function to identify if the field contains all the provided reference codes in any order. Returns True or False.

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

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

Query if the field contains all provided values (specific order)

Use the multivalued_contains function 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.

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

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

Query if the field contains any of the provided values (in any order)

Use the multivalued_anyOf function to identify if the multivalued field contains any of the provided reference codes in any order. Returns True or False.

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

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

Find which provided field displays first on the field

Use the multivalued_firstOf function to return the first reference code that displays on the multivalued field from the provided values (order does not matter).

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

Sample 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'

Sample results

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