Use custom fields in insights

Learn how to use custom fields in an insight and filter the results by the custom field values

Neli Ivanova avatar
Written by Neli Ivanova
Updated over a week ago

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'

Did this answer your question?