I have a rule to send an email to the user on ticket creation including the custom ticket fields we've created. All of the custom fields display correctly except one ($wisdnum) and i can't figure out why. Here is the query:

SELECT

      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    
    
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
    
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
    
      -- about status
      S.NAME AS STATUS,   -- $status
    
      -- about impact
      I.NAME AS IMPACT,   -- $impact
    
      -- about category
      CAT.NAME AS CATEGORY, -- $category
    
      -- other fields
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
      HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
      HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisdnum
      HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
      HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
      HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

      -- -- example of static distribution list
      'helpdesk@wacoisd.org' AS NEWTICKETEMAIL -- $newticketemail
    
    
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            /*AND C.ID=<CHANGE_ID>*/
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
     C.DESCRIPTION LIKE 'TICKET CREATED%'
    
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

and here is the format for the email being sent (and it is sending successfully):

The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)

Location: $location
Room: $room
Equipment: $equipment
Service Tag or WISD: $wisdnum
Description: $description

Opening Comment: $comment

So all the other variables get filled in, but Service Tag or WISD says "$wisdnum". Any ideas why?

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

This is going to sound strange but did you try putting an underscore for &wisdnum? You did that for user_email and indicated that worked. 

    HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisdnum
       HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

Answered 04/04/2013 by: nshah
Red Belt

  • Hmmm... I'll try it, but I have another variable $ticknum that correctly displays the ticket number.

    BUT... Just tried it and it worked. Wonder why. Thanks!
  • I don't think it's related to the type of field but mainly the syntax. you did the following for ticket

    D_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

    this AS statement is the only one incorrect for the $ variable. Im sure you could have either added the _ like you did or removed it from the AS statement and said AS WISDNUM and it would have worked.

    HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisdnum
  • That makes sense. Didn't notice or realize the variables had to match. Thanks!
Please log in to comment
Answer this question or Comment on this question for clarity

Share