Kevin Grigsby

We are nearly 2 years since the reply... What is this "new" feature?

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!