In this Explore recipe, you'll learn how to create a report that captures the time when a custom checkbox was checked. If the checkbox is ever unchecked, the first timestamp of when it was checked will still be captured. However, if the checkbox is later rechecked, there will be a secondary entry for when the checkbox was checked.
In this example, a checkbox was checked, unchecked, and then rechecked. Two timestamp entries are recorded:
What you'll need
Skill level: Easy
Time required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- A checkbox custom field. For help creating this field type, see Adding custom fields to your tickets and support request form
Creating the query
Use the following steps to create this report in Explore.
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket Updates, then click New query. Query builder opens.
- Next, you'll create a standard calculated attribute that records changes to your custom checkbox. From the calculations menu (
), click Standard calculated attribute.
-
On the Standard calculated attribute page, enter a name for your attribute like Checkbox checked, then enter or paste the following formula:
IF ([Changes - Field name]="{your_checkbox_field_name}")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
[Update - Timestamp]
ENDIFOn the first line of the formula, replace "{your_checkbox_field_name}" with the name of your custom checkbox in quotes. For example, if your custom checkbox is named "Checkbox_1" replace "{your_checkbox_field_name}" with "Checkbox_1".
You'll end up with an attribute that looks like this: - When you're finished, click Save.
- Now you can construct the query using your new calculated attribute. In the Metrics panel, click Add.
- From the list of metrics, choose Updates > Updates, then click Apply. Ensure that the metric aggregator is set to D_COUNT.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes, then the name of the standard calculated attribute you created. When you are finished, click Apply.
- Explore now displays your report, however NULL values (where there is no timestamp) are displayed. Next, you'll filter these NULL values from the report. Click the calculated attribute you just added to the Rows panel.
- On the attribute settings page, click the Excluded tab, enable the NULL checkbox, then click Apply. You'll see that NULL values are excluded from the report.
- In the Rows panel, click Add.
- From the list of attributes, choose Ticket > Ticket ID, then click Apply. This adds the ticket ID and exact timestamp for when the checkbox was checked to the report.
Here's an example of how the finished report might look:
2 Comments
Does this formula work for any custom field? I guess the better question are timestamps saved for all custom fields?
Thanks
Hi Jed Hollander - While you won't see an attribute exclusive to the custom field change timestamp, you will be able to understand the exact timestamp in which a custom field was updated by using the Update - Timestamp. The timestamp shown within the Update - Timestamp attribute will be based on the exact data you're looking at in Explore.
For instance, in the formula provided in this recipe article, the Update - Timestamp attribute will only reflect ticket updates in which Checkbox_1 was selected.
In terms of whether you'd be able to use this same formula for other custom fields, you should be able to use this for most (if not all) custom ticket field types. However, you'll likely need to change the formula based on the type of custom field you'd like to report on since not all custom field types would return an old/new field value of either 1 or 0. Instead, the custom field value might actually be text or a ticket tag.
Additionally, in the case of a dropdown menu that has a lot of different options, this formula might be a better way to go so that you don't have to explicitly call out each individual dropdown menu option within your query.
IF ([Changes - Field name]="Dropdown_1")
AND
([Changes - Previous value]=NULL))
AND
([Changes - New value]!=NULL)
THEN
[Update - Timestamp]
ENDIF
Hope that helps point you in the right direction!
Please sign in to leave a comment.