Workfront Data Connect query examples

To help you better utilize your Workfront Data Connect data, this page contains basic example queries you can use to familiarize yourself with the syntax and structure of specific kinds of queries.

Custom data query

This example demonstrates how you can compose a query to return your custom data in Workfront, such as custom forms and custom fields.

Scenario

Your organization utilizes a custom form named Finance Integration. The form is attached to every project, and contains the following fields:

You need to include this information in the response for a query against Data Connect. Custom data values for a record in the data lake are contained in a column titled parametervalues. This column is stored as a JSON object.

Query

SELECT
    projectid,
    parametervalues,
    name,
    parametervalues:"DE:Business Unit"::int as BusinessUnit,
    parametervalues:"DE:Project ID"::int as ProjectID,
    parametervalues:"DE:Expanded Project Name"::text as ExpandedProjectName
FROM PROJECTS_CURRENT
WHERE ExpandedProjectName is not null

Response

The above query returns the following data:

Explanation

When querying the parametervalues JSON object, each custom data field can be accessed as a column using the following:

<field_name>:"<parameter_name>"::<data_type> as <column_name>

Time in status query

This example demonstrates how to measure the time a project spent in previously assigned statuses. It can easily be adapted to measure task or issue time in a status, or it can be adapted to measure how long an object had any other attribute (including custom data values) applied to it.

Scenario

Your organization leadership believes you are spending too much time in each stage of your work lifecycle. Before making recommendations to improve the process, you want to create a baseline measurement of how often a project status changes over time and how many days a project stays in any given status.

You’re going to use the PROJECTS_EVENT data view to pull in a list of each status change against the project object. You’ll compare the new status to the previous status, grab the effective time range for the previously assigned status, and then calculate the days spent in that status.

Using this raw output of time spent in each status per project, you can start to build visualizations or aggregate the data further to build status duration averages by status, project type, or time of year. This baseline is then used to set a benchmark you can measure against to meet your leadership’s expectations.

The following query uses the Data Connect PROJECTS_EVENTS data view to compare each project status change event and display the time in status.

Query

-- Calculate the begin/end effective timestamp and duration in days

SELECT

    projectid,
    name as project_name,
    prev_status as previous_status,
    status,
    status_change_date as status_begin_effective_timestamp,
    case
        when status_change_date is null then NULL
        else
            nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)
    end as status_end_effective_timestamp,
    datediff('DAYS',status_change_date, nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)) as status_duration_days

FROM
    ( -- Filter to just the records that have changed
     SELECT projectid,
        name,
        prev_status,
        status,
        begin_effective_timestamp as status_change_date
     FROM
        (  -- Calculate records where previous status is different
          SELECT DISTINCT
           pe.projectid,
           pe.name AS name,
           pe.STATUS,
           nvl(lag(pe.STATUS) over (partition by pe.projectid order by pe.BEGIN_EFFECTIVE_TIMESTAMP), status) prev_status,
           begin_effective_timestamp

          FROM projects_event pe
         -- Set any WHERE conditions to limit the results as needed
         --WHERE
            -- pe.PROJECTID = '5ebe…c1e1'
        )
        WHERE prev_status != status
    )
    order by status_change_date;

Response

The above query returns the following data:

Explanation

The query uses Data Connect’s change event tracking capabilities.  It determines the date an event that had a new status value different from the previous event was triggered.

Examining the query from the inside out:

Planning: single record type query

This example demonstrates how to query Workfront Planning data for a single record type stored in the Data Connect data lake.

Scenario

Your organization uses Workfront Planning to track Campaigns. Each Campaign record includes a name, status, start date, end date, and owner. You want to pull a list of all active campaigns and their key details for use in a dashboard.

Query

SELECT
  recordid,
  FIELD_VALUES:"Name"::text AS campaign_name,
  FIELD_VALUES:"Status"::text AS campaign_status,
  FIELD_VALUES:"Start Date"::date AS start_date,
  FIELD_VALUES:"End Date"::date AS end_date,
  FIELD_VALUES:"Owner"::text AS owner
FROM PLANNINGRECORD_CURRENT
WHERE WORKSPACEID = '<your_campaign_workspace_id>'
AND RECORDTYPEID = '<your_campaign_record_type_id>'
AND FIELD_VALUES:"Status"::text = 'Active'
ORDER BY start_date ASC

Response

The above query returns the following data:

Explanation

Planning records in Data Connect share a single table structure regardless of record type. The RECORDTYPEID column is used to scope the query to a specific record type — in this case, Campaigns. Replace <your_campaign_record_type_id> with the ID of the record type you want to query, which can be found in the Workfront Planning record type settings or by querying RECORDTYPE_CURRENT.

Field values are stored as a JSON object in the FIELD_VALUES column and are accessed using the same colon-notation syntax used for custom form data:

<field_column>:"<field_name>"::<data_type> AS <alias>

Field name references must match exactly the field name defined in the Planning record type field configuration, including capitalization, spacing and emoji.

SELECT
    ID AS recordtypeid,
    DISPLAYNAME AS record_type_name,
    WORKSPACEID
    FROM RECORDTYPE_CURRENT
    ORDER BY record_type_name ASC

Planning: connected record types query

This example demonstrates how to query data across two connected Planning record types — a parent record type and a record type it is connected to.

Scenario

Your organization connects Campaign records to Tactic records in Workfront Planning. You want to produce a report that shows each campaign alongside key details from its associated tactics. They specifically want to show the tactic name, strategic priority, and budget allocation so that leadership can review campaign activity organized by tactic.

In Data Connect, connections between native Planning record types are stored directly in the FIELD_VALUES_RAW column of PLANNINGRECORD_CURRENT. For a reference field named “Tactics”, the value is a JSON array of connected record objects, each containing an id property with the connected record’s RECORDID. Use Snowflake’s LATERAL FLATTEN to expand this array into rows and join to the connected record type.

Query

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  t.FIELD_VALUES:"Name"::text AS tactic_name,
  t.FIELD_VALUES:"Strategic Priority"::text AS strategic_priority,
  t.FIELD_VALUES:"Budget Allocation"::float AS budget_allocation
FROM PLANNINGRECORD_CURRENT c,
INNER JOIN REFERENCE_CURRENT R
ON r.FROM_REFERENCEID = c.REFERENCE_IDS:"Tactics"::text
INNER JOIN PLANNINGRECORD_CURRENT t
-- Join to the Tactic record using the connected record ID from the array
ON t.RECORDID = r.TO_RECORDID
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
ORDER BY tactic_name, campaign_name

Response

The above query returns the following data:

Explanation - Modified KP

Connections between native Planning record types are stored in a REFERENCE_CURRENT join table. The REFERENCE_CURRENT join table is used for joins between RecordType. When joining between RecordType, the TO_RECORDID field should be used.

The REFERENCE_ID column in the PLANNINGRECORD view contains a list of all REFERENCEID fields that are applicable to that planning record. You can access the id by utilizing the same JSON notation as a field_value.

<reference_ids>:"<reference_name>"::text

The REFERENCE_CURRENT view contains one or more records where the TO_RECORDID points to other planning recordId fields in the PLANNINGRECORD_* views.

To join another REFERENCE field to additional planning records the same pattern of joining to REFERENCE_CURRENT and the PLANNINGRECORD_* views would be added to the above query.

Planning: record type joined to Workfront Workflow data query

This example demonstrates how to join a Workfront Planning record type to a native Workfront Workflow object — in this case, a Project — using Planning’s native connection feature, which stores external object references in the REFERENCE_CURRENT view.

Scenario

Your organization connects Campaign records in Workfront Planning to Workfront Projects using Planning’s native connection feature. You want to produce a combined report showing campaign details alongside live execution data from the linked project — specifically the project’s current Percent Complete, Planned Completion Date, and assigned Project Owner — so that campaign managers can track delivery progress without leaving their Planning workspace context.

Query

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  conn.TO_EXTERNALID AS linked_project_id,
  p.name AS project_name,
  p.percentcomplete AS project_percent_complete,
  p.plannedcompletiondate AS project_planned_completion,
  p.ownerid AS project_owner_id,
  u.name AS project_owner_name
FROM WORKFRONT.PLANNING.PLANNINGRECORD_CURRENT c
-- Join to the references table to find Workfront Project connections
INNER JOIN WORKFRONT.PLANNING.REFERENCE_CURRENT conn
ON conn.REFERENCE_ID = c.REFERENCE_IDS:"ProjectId"::text
-- Join to the Workfront Projects table on the external ID
INNER JOIN WORKFRONT.WF.PROJECTS_CURRENT p
ON p.projectid = conn.TO_EXTERNALID
-- Join to Users to resolve the project owner name
LEFT JOIN WORKFRONT.WF.USERS_CURRENT u
ON u.userid = p.ownerid
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
AND p.completiontype != 'CPL' -- Exclude completed projects
ORDER BY campaign_name

Response

The above query returns the following data:

Explanation

Connections from a Planning record type to a native Workfront Workflow object are stored in REFERENCE_CURRENT. Each row in this view represents one directional link: TO_EXTERNALID holds the ID of the connected Workfront object. Rows representing Workfront connections are identified by TO_EXTERNALCONNECTIONNAME = 'workfront' and a TO_EXTERNALOBJECTNAME value that corresponds to the Workfront object type’s API code — for example, PROJ for Projects.

The REFERENCE_ID column in the PLANNINGRECORD tables contains a list of all REFERENCEID fields that are applicable to that record. You can access the id by utilizing the same JSON notation as a field_value.
A single REFERENCE_ID in the PLANNINGRECORD_CURRENT may contain one or more reference links in the REFERENCE_CURRENT table that link to objects of a specific object type in the Workfront table.

<reference_ids>:"<reference_name>"::text

Note that Planning views (PLANNINGRECORD_CURRENT, REFERENCE_CURRENT) reside in the WORKFRONT.PLANNING schema, while native Workfront Workflow views (PROJECTS_CURRENT, USERS_CURRENT, etc.) reside in the WORKFRONT.WF schema. Cross-schema joins require fully qualified table names.

The query performs three joins:

To join another REFERENCE field to additional external records the same pattern of joining to REFERENCE_CURRENT and the Workfront Workflow views would be added to the above query.

External and Planningrecord values can be joined in the same query by joining multiple times to the REFERENCE_CURRENT table an using the appropriate join pattern.