Recent searches


No recent searches

Reporting on single Custom Field added none-to-many times to ticket



Posted Oct 24, 2023

Root Problem: We are trying to track time spent on a ticket for a given date. The time tracking add-in does not give us the ability to add a date when we are adding seconds/minutes to a ticket.

I have created a custom field (Almanac Entry Full) that stores the Date, Minutes spent and our inhouse time tracking systems database record Id (DbId) where we do billing from and is our source of truth. I then send a ticket update API call and the value of this custom field is formatted like this:

YYYY-MM-DD:mmmm:####

Where the first 10 characters are the year, month and day. The mmmm (zero left-padded number) representing the number of Minutes spent on the ticket for that date. The last set of #'s is that unique DbID. Those 3 can be combined and are always unique, even if you add 15 minutes to the same ticket for the same day multiple times because the DbId will always be different. I have learned, without uniqueness in these Custom Fields, it will not add it, since nothing has changed.

Thus far, this look great. I can report on the fact that ticket #10 has 4 instances of my custom field in it and ticket #11 has only 2. I even have 3 metrics that extract out the parts of the string as useful values (Date, int and int specifically from the YYYY-MM-DD:mmmm:#### format) - the Date is shown here:

IF (REGEXP_MATCH([Almanac Entry Full], "\d{4}-\d{2}-\d{2}")) THEN
  DATE(LEFTPART([Almanac Entry Full], 10))
ELSE
  NULL
ENDIF

What I am not able to figure out is how to get Every instance of the "Almanac Entry Full" for EACH ticket. I am hoping to have something like this:

Ticket #    Subject                    Date              Minutes
10            Ticket Ten                10/20/2023    30
10            Ticket Ten                10/21/2023    120
11            Ticket Eleven           10/20/2023    60
11            Ticket Eleven           10/22/2023    60

The backend data in the Almanac Entry Full field could be like the following:

Entries for ticket #10
2023-10-20:0015:1234
2023-10-20:0015:2314
2023-10-21:0075:2431
2023-10-21:0045:2355

Entries for ticket #11
2023-10-20:0015:3456
2023-10-20:0045:1865
2023-10-22:0060:4548

Am I going about this correctly?

I could bang this out in SQL in less time that in took me to type out. Hope you can help, thanks!


0

1

1 comment

Official

image avatar

David

Zendesk Customer Care

Hi Kevin,

I see challenge you’re facing is extracting each instance of the “Almanac Entry Full” for each ticket. This is because Zendesk’s reporting is designed to aggregate data, rather than display individual entries.

One possible workaround could be to create a separate custom field for each date you want to track. For example, you could have a custom field for “Time Spent on 2023-10-20”, “Time Spent on 2023-10-21”, etc. Then, you could use these fields in your reports to track time spent on each ticket for each date.

However, this approach could quickly become unwieldy if you’re tracking a large number of dates. It might be more efficient to export your data and analyze it in a separate tool that’s better suited for this type of analysis, such as a SQL database or a spreadsheet program.

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post