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 comment
Official
David
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