Adding a count of all tickets from a Requestor in the OnCreate template

RT Users
1

Hi,

I’d like to be able to have the template used for new ticket creation include a count of the number of tickets that that specific requestor has created. Ideally, the count of all tickets, active and resolved, anything other than “deleted” (which I Shred anyway).

For example:

Tue Nov 03 13:33:16 2020: Request 22222 was acted upon.
 Transaction: Ticket created by username123@gmail.com
       Queue: TestQ
     Subject: Subject of the email.
       Owner: Nobody
  Requestors: username123@gmail.com
  ReqHistory: 18
      Status: new
  Ticket URL: https://...

And from there the original email would follow.

What I’m after is that concept of the ReqHistory, and in this case, it would mean that username123@gmail.com has a total of 18 tickets in RT.

Is there a relatively easy way to get this, like a " {$Ticket->RequestorAddresses->TicketCount}" value?

What I’m trying to do is have, in each new email, a quick view of whether or not a requestor’s email address is one which is a regular user of the system. I know I can get this from the web UI, but it would also be helpful in the email transactions.

Thanks!

2

You can try something like this:

{
   my $first_requestor = $Ticket->Requestor->UserMembersObj->First;

   my $tickets = RT::Tickets->new( $Ticket->CurrentUser );
   $tickets->FromSQL( 'Requestor.Id LIKE '.$first_requestor->Id );

   $OUT = $tickets->Count;
}

I didn’t test this so it probs requires some tinkering

3

Thanks for the pointer, good starting place, appreciate it.

Couldn’t get this to work, but tried a few dozen variations, all seem to result with

Template parsing error: Can't call method "FromSQL" on an undefined value at template line 15.

As an error. I’ll explore some examples of FromSQL being called and see if I can work out what the right query may be.

4

Try this, I tested quick and it seems Id is being funky:

{
   my $first_requestor = $Ticket->Requestor->UserMembersObj->First;

   my $tickets = RT::Tickets->new( RT->SystemUser );
   $tickets->FromSQL( 'Requestor.EmailAddress LIKE "'.$first_requestor->EmailAddress.'"' );

   $OUT = $tickets->Count;
}
5

Sorry for the likely faux pas of reviving this topic, but, I had a follow-up thought and wasn’t sure it really deserved a whole new thread.

This “request history” modification in the OnCreate is working outstanding, and has made my life a lot easier.

However, as with all such things, it ends up spawning off another problem to solve: Is there any perl that I could add that would, for cases where the Count is > 1, tell me the time, in days, between the last communication from a given requestor, and the new ticket?

That is, if username123@gmail.com had 18 previous tickets in my system, could I add some perl that would add a line to the template that told me:

ReqTimeInterval: 93 days

This would, ideally, indicate that username123@gmail.com has sent nothing to my RT instance in 93 days, which could allow me to spawn off other automation/responses based upon the amount of time between messages.

Thanks for any thoughts!

6

Depending on where you want to apply this scrip or template or elsewhere the syntax will look a little different but I believe this is the general idea:


my $transactions = RT::Transactions->new( RT->SystemUser );
$transactions->Limit( FIELD => 'Creator', VALUE => $self->TicketObj->CurrentUser->Id );
$transactions->OrderBy( FIELD => 'Created', ORDER => 'DESC' );

my $most_recent_transaction = $transactions->First;
# Then you can grab the `->Created` date from this most recent transaction.
7

Great, thanks, I’ll give that a go - it’s going to go in a Template, the one that I use for “Transaction”, so I’ll muck about with it in one of my test queues…ta!