Now that we know how to retrieve the value of a custom field in an insight, lets see how to create an insightboard parameter containing its all possible values.

You have two options:

  • Insightboard parameter with predefined values - if you know the exact values of the custom field and it is not likely the available values to change, you can simply list all the available options
  • Insightboard parameter with dynamic values - when you do not have all available values of the custom field or the values are likely to change often.

Lets learn more about the second option. The information in Gtmhub entities is kept in a json string and if we create an insightboard parameter using custom_fields field as a title/value of the parameter definition, the result will not be readable for the end user. Following these steps will lead us to the desired result:

1. First step is to find a way to extract the field values from the json string. Virtual data sources are the perfect solution for this case. Here is an example code of the VDS extracting custom fields values:

SELECT DISTINCT 
CASE
WHEN customfields <> ''
THEN (customfields::json ->> '<field_name>')
ELSE ''
END AS <field_name>
FROM <gtmhub_entity>

where <field_name> should be replaced with the name of the custom field and <gtmhub_entity> with the entity the fields is part of.

2. Next, use the virtual data source for creating the insightboard parameter. When creating the parameter, select dynamic values option. For entity type select the created in step 1. Virtual Data Source and for title/values - the available option from the drop-down menu.

3. Last step would be to add the parameter in the SQL code for your insight. Here we have a detailed guide on writing an insight with insightboard parameter.

Example

Let's review a real case. We have a custom field named region on user level. As an administrator of the platform I would like to be able to find which are the employees for every region.

1. Navigate to Insights > Data Sources and click on "Add new virtual data source" button:

  • Give a name of the data source: user_region
  • Build the entity using this SQL code:
SELECT DISTINCT
CASE
WHEN customfields <> ''THEN (customfields::json ->> 'region')
ELSE 'No Region'
END AS region
FROM gtmhubusers
  • Execute to verify the information:

2. Navigate to Settings > Insightboard parameters and click on "Add new parameter":

  • Title: Region
  • Key: region
  • Selector: Multi-select drop-down
  • Dynamic values: enabled
  • Entity type: user_region
  • Title field: region
  • Value field: region

3. Navigate to Insights > Insightboards and click on Manage Insights menu. Click on "Create new insight" and select "Use SQL expect editor" option.

  • Title: Users and Regions

SQL code for this Insight:

SELECT * FROM (
SELECT
CASE
WHEN customfields <> ''
THEN (customfields::json ->> 'region')
ELSE 'No Region'
END AS region,
id,
firstname
FROM gtmhubusers
) all_users
WHERE CASE
WHEN '%%region%%' <> ''
THEN (region IN (SELECT(UNNEST(string_to_array('%%region%%', ',')))))
ELSE true
END
ORDER BY firstname

HTML code:

<div class="bigger-150 bold">{{insight.title}}</div>
<table mat-table class="table mt-2" ng-init="users = (data.default.length > 0) ? data.default : [data];">
<tr>
<th ng-click="orderKey = 'firstname'; reverse = !reverse;">Employee</th>
<th ng-click="orderKey = 'region'; reverse = !reverse;">Region</th>
</tr>
<tr ng-repeat="user in users | orderBy : orderKey : reverse">
<td>
<assignee id="{{user.id}}"></assignee>
</td>
<td>{{user.region}}</td>
</tr>
</table>

And this is how our insight looks like:

Did this answer your question?