Use custom fields in insights | Quantive Help Center

Overview

Quantive Results allows you to extend the definition of objectives, key results, tasks, teams, and users by creating custom fields.
For more information, see Create custom fields.

The following article explains how to use these custom fields in an insight.

Access a custom field

The Quantive Results data source contains an entry named customfields. It contains the custom fields of the following:

quantiveresultsgoals - objectives
quantiveresultsmetrics - key results
quantiveresultsmetricsnapshots - key result updates
quantiveresultsteams - teams
quantiveresultsusers - users
quantiveresultstasks - tasks

The customfields record contains all available custom fields with their names and values. The information is kept in a JSON string format.

EXAMPLE: The following is an example of how the customfields record looks for a user that has two custom field - position and region. The values of these two fields for this specific user are Billing owner and EMEA:

{\"position\":\"Billing owner\",\"region\":\"EMEA\"}

Get the value of a custom field

To get the value of a custom field from a JSON string, use the following SQL query:

customfields::json ->> 'field_name'

Replace field_name with the name of the custom field.

EXAMPLE: You want to list all users on your account with their name, position, and region. The SQL query should look like the following:

SELECT 
    firstname || ' ' || lastname AS name,
    CASE 
        WHEN customfields <> '' THEN (customfields::json ->> 'position') 
        ELSE '' 
    END AS position,
    CASE 
        WHEN customfields <> '' THEN (customfields::json ->> 'region') 
        ELSE 'N/A' 
    END AS region
FROM quantiveresultsusers

This query returns all users with their name, position, and region.

If the user's position is not specified, the query returns empty string
If the region of the user is not specified, the query returns N/A.

RECOMENDATION: We recommend using CASE statements to ensure the query returns a value when the custom field is not populated.

Filter results by custom field

If you would like to get only records that contain a specific values for a custom field, you can use the following statement:

WHERE 
    customfields <> '' AND 
    customfields::json ->> 'field_name'  = 'field_value'

Replace field_name with the name of the custom field and field_value with the value that you want to filter by.

RECOMMENDATION: If the custom field is not required, we recommend using statement to check that the field is not empty - customfields <> ''

EXAMPLE: You want to get the names of all users that are part of the EMEA region only. The SQL query should look like the following:

SELECT 
    firstname || ' ' || lastname AS name
FROM quantiveresultsusers
WHERE 
    customfields <> '' AND 
    customfields::json ->> 'region'  = 'EMEA'