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

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!

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

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.

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;
}