Hi everyone,

I have a custom ticket rule setup on my system to send an email an email address listed in the Custom 1 of the user that submitted it.  Since the update to 7.x, this rule has been broken.  The error is 1054: Unknown column 'SUBMITTER.CUSTOM_1' in 'field list'.

Here's the SQL we're using:

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/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
      SUBMITTER.CUSTOM_1 AS SUBMITTER_CUSTOM_1,     -- $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
      -- -- example of static distribution list
      'bpendry@martinsnet.com' 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

I need to know what we need to change to make this work if possible.  I've taken a look at the database, and it looks like the custom mappings are now held outside of the user table in their own table.

Thanks in advance for the help

Nick
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
There have been several posts about this issue, here is one:

Searching the site for user_field_value will yield more.

There is also a KB article available here:

Answered 11/30/2017 by: chucksteel
Red Belt

  • Hi Chuck, thanks for the reply. I've modified the script a few times to try and make this work, I keep getting an error.

    1054: Unknown column 'USER_FIELD_VALUE.USER_ID' in 'on clause'

    Here's the current SQL that I think should work. I'm not sure what I'm missing....

    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/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
    SUBMITTER_CUSTOM.FIELD_VALUE AS SUBMITTER_CUSTOM_1, -- $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
    -- -- example of static distribution list
    'bpendry@martinsnet.com' 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
    /* submitter custom*/ LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = USER_FIELD_VALUE.USER_ID AND USER_FIELD_VALUE.FIELD_ID = 1
    /* 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
    • In your join statement you are creating an alias for the USER_FIELD_VALUE table and calling it SUBMITTER_CUSTOM. Once you do that you need to refer to the alias and not the actual table name:
      LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = SUBMITTER_CUSTOM.USER_ID AND SUBMITTER_CUSTOM.FIELD_ID = 1
      • Sorry for the slow response. I think we have a working script, but for some reason it's always showing 0 rows selected when I try and test.

        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/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
        SUBMITTER_CUSTOM.FIELD_VALUE AS SUBMITTER_CUSTOM_1, -- $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
        -- -- example of static distribution list
        'bpendry@martinsnet.com' 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
        /* submitter custom*/ LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = SUBMITTER_CUSTOM.USER_ID AND SUBMITTER_CUSTOM.FIELD_ID = 1
        /* 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

        If I remove the "AND C.ID=<CHANGE_ID>" and run the query in MySQL Workbench, I get correct results for all the tickets in the system. But for some reason this still doesn't seem to work.

        Any thoughts?

        I appreciate your help.

        Nick
      • You can't effectively test rules that run on save because of the CHANGE_ID variable. The best method is to save the rule and then create a ticket to test. I have a queue setup for testing rules that only admins can see.
      • I'm afraid the last run log isn't showing much. Simply this: 12/06/2017 11:33:07> Starting: 12/06/2017 11:33:07 12/06/2017 11:33:07> Executing Select Query... 12/06/2017 11:33:07> selected 0 rows

        as I said before, I can see the results correctly, so I'm not sure what to look at next
      • Try adjusting the C.DESCRIPTION to LIKE '%Created%'.
      • Changing that doesn't seem to have made any difference at all. Is there a better way to trouble shoot this than to just look and see if the save has results?
      • The CHANGE_ID Variable didn't change with 7.x did it?
      • You can look at the HD_TICKET_CHANGE table for the ticket you created and see what it reports. You could also remove the C.DESCRIPTION like "%Created%" so that it should match on any change to the ticket and see if it triggers an email.
      • No, the variable is still the same. Another way to trouble shoot is to find the ID for the change in the HD_TICKET_CHANGE table for that ticket and then run the query using that ID in MySQL Workbench where the variable normally would be.
      • It seems to work when I take the WHERE
        C.DESCRIPTION LIKE '%CREATED%' out. So that's a step in the right direction.
      • We're working now! I hate to say it was mostly how I was testing that was the issue, but now the issue is resolved. Thank you very much for your time and effort to help a handicapped scripter like myself :)
Please log in to comment

Answers

0
Using your email address as a nickname on a public forum probably isn't up there in the list of The World's Greatest Ideas.
Answered 11/30/2017 by: VBScab
Red Belt

  • Just curious why you say that. It's becoming fairly common practice to use email addresses as usernames on websites.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share