osTicket 1.9.4 –Easy Custom Column in Ticket View

By popular demand, I have update the instructions for the Easy Custom Column for osTicket 1.9.4 for form lists.

What does this allow you to do?
It allows you to show a custom form list in your Agents Ticket Queue. This is rather helpful for when you have a list of items relating to your business and you want to be able to see it.

I have only tested this code with lists.

The full code can be found here:

https://gist.github.com/a-bennett/6febddc9e328151dc99a

For those who are interested in doing it themselves (or maybe have already edited there tickets.inc.php file) you can see the changes between the original file and the updated here:

https://gist.github.com/a-bennett/6febddc9e328151dc99a/revisions

How do I find the form ID?

  1.   By looking at the table _form_field and finding your form object by looking down the list of the labels, then once you have found it taking the ID from that row.
  2. If you know how to inspect an web element, (using web inspector, firebug, etc) then you can try the following:
    In the admin panel go to “Manage” -> “Forms” -> click on your form name.
    Inspect the input field of the name of your item mine came up like this:
    <input type=”text” size=”32″ name=”label-17″ value=”Business”>
    Then take the number after the “label-X” so mine is 17.

For those interested here is a break down:

At the top of the file add the following two lines:
$ab_list[‘field_id’] = “XX”; //ID from from _form_field table
$ab_list[‘heading’] = “XXXXXXX”; //Name displayed to front end users.

~Line 169:
Update the $sortOptions array withe the following:

$sortOptions=array(‘date’=>’effective_date’,’ID’=>’ticket.`number`*1′,
‘pri’=>’pri.priority_urgency’,’name’=>’user.name’,’subj’=>’cdata.subject’,
‘status’=>’status.name’,’assignee’=>’assigned’,’staff’=>’staff’,
‘dept’=>’dept.dept_name’,
‘custom1’=>’Custom1′); //AB Added

~Line 253
Append to the end of the $qselect. variable the following: (remember to move the ;)
.’ ,cdata.’.$CustomList1Name.’ as Custom1′; //AB – Custom 1

~Line 262
Add the following to the $qfrom variable straight after the line:

.’ LEFT JOIN ‘.TABLE_PREFIX.’ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) ‘
.’ LEFT JOIN ‘.TABLE_PREFIX.’form_entry_values fentry_val ON (cdata.field_’.$ab_list[“field_id”].’ = fentry_val.entry_id) AND fentry_val.field_id = ‘.$ab_list[“field_id”] //AB We need to link to the form values as of 1.9.4

~Line 412

Add in the following header row just above the </tr>.

<!– AB – Custom Col 1 –>
<th width=”60″ >
<a <?php echo $custom1_sort; ?> href=”tickets.php?sort=custom1&order=<?php echo $negorder; ?><?php echo $qstr; ?>”
title=”Sort By <?php echo $CustomList1Desc; ?> <?php echo $negorder; ?>”><?php echo $CustomList1Desc; ?></a></th>
<!– AB – Custom Col 1 -–>

 

~Line 498

Add in the data row just about the </tr>
<!– AB – Custom Col 1 –>
<td nowrap>&nbsp;<?php echo $row[‘Custom1’]; ?>&nbsp;</td>
<!– AB – Custom Col 1 –>

~ Line 511

Finally update the footer colspan to make everything balanced.

<td colspan=”8″>

68 thoughts on “osTicket 1.9.4 –Easy Custom Column in Ticket View”

  1. Hey, I’ve done all that and even downloaded your file and tested but each time, I receive the following error:

    “There are no tickets matching your criteria.”

    I’ve set the custom field ID. I’m using 1.9.4 Stable.

    1. Howdy,
      Is the extra field your trying to view a custom list?
      If it isn’t you might need to modify the SQL statement slightly.

      Andrew

    1. I’m away from my computer, but the line you’ll want to focus on is line 269, and to be annoying they changed the database a lot between 1.9.2 & 1.9.4 and I’m not up to speed with it.

      Using a tool like phpMyAdmin will help you to find the exact info.

      I would start here & go from there:

      Change line 269 from:

      .’ ,fentry_val.value as Custom1′ //AB – Custom 1

      To:

      .’ ,cdata.field_’.$ab_list[“field_id”].’ as Custom1 ‘ //AB – Custom 1

      That should either give you the answer your looking for, or it will give you an ID (& hopefully not an error). If you get an ID value, you’ll need to work out a join with the appropriate table. Hopefully that points you in the right direction.

      Also as a side note, there’s a line somewhere with //echo $query

      If you remove the double slashes, it will print out the entire query used to create the page, if you copy that and paste it into the SQL section of phpMyAdmim you might get a better error message than “no tickets found”. (Which is the default if something isn’t quite right).

      1. Thanks once again, the first suggestion still gave the same error and no ID. So, I uncommented the query.

        I’ve copied the SQL query and ran it and receive the following error, I believe we may need to define the table: `ost_form_field` somewhere?:

        “Error Code: 1054
        Unknown column ‘cdata.field_19’ in ‘field list'”

        The query is:

        SELECT ticket.ticket_id,tlock.lock_id,ticket.`number`,ticket.dept_id,ticket.staff_id,ticket.team_id ,user.name ,email.address AS email, dept.dept_name, status.state ,status.name AS STATUS,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created ,IF(ticket.duedate IS NULL,IF(sla.id IS NULL, NULL, DATE_ADD(ticket.created, INTERVAL sla.grace_period HOUR)), ticket.duedate) AS duedate ,CAST(GREATEST(IFNULL(ticket.lastmessage, 0), IFNULL(ticket.closed, 0), IFNULL(ticket.reopened, 0), ticket.created) AS DATETIME) AS effective_date ,ticket.created AS ticket_created, CONCAT_WS(” “, staff.firstname, staff.lastname) AS staff, team.name AS team ,IF(staff.staff_id IS NULL,team.name,CONCAT_WS(” “, staff.lastname, staff.firstname)) AS assigned ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(” / “, ptopic.topic, topic.topic)) AS helptopic ,cdata.priority AS priority_id, cdata.subject, pri.priority_desc, pri.priority_color ,cdata.field_19 AS Custom1 FROM ost_ticket ticket LEFT JOIN ost_ticket_status STATUS ON (status.id = ticket.status_id) LEFT JOIN ost_user USER ON user.id = ticket.user_id LEFT JOIN ost_user_email email ON user.id = email.user_id LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id LEFT JOIN ost_ticket_lock tlock ON (ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() AND tlock.staff_id!=1) LEFT JOIN ost_staff staff ON (ticket.staff_id=staff.staff_id) LEFT JOIN ost_team team ON (ticket.team_id=team.team_id) LEFT JOIN ost_sla sla ON (ticket.sla_id=sla.id AND sla.isactive=1) LEFT JOIN ost_help_topic topic ON (ticket.topic_id=topic.topic_id) LEFT JOIN ost_help_topic ptopic ON (ptopic.topic_id=topic.topic_pid) LEFT JOIN ost_ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) LEFT JOIN ost_form_entry_values fentry_val ON (cdata.field_19 = fentry_val.entry_id) AND fentry_val.field_id = 19 LEFT JOIN ost_ticket_priority pri ON (pri.priority_id = cdata.priority) WHERE ( ( ticket.staff_id=1 AND status.state=”open”) OR ticket.dept_id IN (1,4,63,64,8,10,21,22,23,24,19,20,65,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,61,56,57,58,59,60,62,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87) ) AND status.state IN ( ‘open’ ) ORDER BY pri.priority_urgency ASC, effective_date DESC, ticket.created DESC LIMIT 0,25

        1. It looks like the error is pointing to the table ost_ticket__cdata saying that it can’t find a column called field_19. (The 19 is from the variable $ab_list[‘field_id’] from line 13 at the top of the file).

          I quickly added a form field to the built in form “Ticket Details” as a Short Answer. Then submitted a ticket. This created a new column in my ost_ticket__cdata table called “field_38”.
          Maybe if you look at that table (using phpMyAdmin or similar tool), you’ll be able to see what has changed in your installation. In my test, I changed the SQL from my previous comment, updated the $ab_list[‘field_id’] to 38 and I was able to see a new column with my new Short Answer text.

          A couple of things to consider when working backwards using a database, whenever testing and tracing something, try when possible to use seemingly stupid sample data, for example to test my short answer field I used the text “Pineapple”, because I knew I would be able to search the entire database for the word Pineapple and it should only return one or two results relating to this field.

          I hope this is helpful.

  2. Would it be any easier to change the content of some of the existing columns? There’s stuff there I could live without, but I’d really like to have the department and status listed.

    -Doug

    1. Hi Doug,
      If you understand a little php and html almost anything is possible.
      First try this:
      Find the line //echo $query; in the tickets.inc.php file and remove the //
      Then reload the page and you’ll see a large “SELECT blah blah blah blah”.
      Copy this and then paste it into a SQL query in a tool such as phpMyAdmin. That should show you a table with all your tickets. Including showing you columns that you don’t see in osTicket. Hopefully if your lucky the columns you want will be there.

      If they are you can then edit the tickets.inc.php all you want.

      The two areas to focus on are the table header, and the rows of the table. The header starts around row 378 (between the

      tags), and the data rows around 469.
      You should be able to model off the other rows, and by substituting different column names getting different columns.

      I hope this helps

      Andrew

      1. Andrew,

        That had to have been the scariest database query I’ve ever seen, but your suggestion worked like a charm.

        The ‘status’ field was already in there. I just changed IF($search && !$status)…. to IF(TRUE)….

        The other change I wanted involved changing “name” to “dept_name” in a couple of places.

        Thank you!!

        -Doug

        1. It is pretty crazy, do an advanced search and watch it change and grow!

          One thing i forgot to mention is if you want the ability to sort the columns, you’ll need to tweek the array $sortOptions which links the varable name from the a link on the header to the table column being sorted (sounds confusing, but if you look at an exsisting column it should make sense pretty quickly ;) )

          Enjoy

  3. Hi,

    Thank you so much for the nice and useful tutorial.

    I have tried the code but I failed to get the result that I want. It returned an empty table with no submitted ticket details after I have tested with the full code. I have changed the ID for $ab_list[‘field_id’] and heading for $ab_list[‘heading’] as well. Am I missing anything?

    Another quick question on this, can I add one column that display on selected Help Topics? It seems like the Help Topics field is not in the _form_field table.

    1. Howdy,

      To answer part 1, the easiest way to troubleshoot that is to find the line //echo $query; in the tickets.inc.php file and remove the //
      Then reload the page and you’ll see a large “SELECT blah blah blah blah”.
      Copy this and then paste it into a SQL query in a tool such as phpMyAdmin, that will give you a better handle on the error. Usually it will saying something about a field not being found.

      Part 2, Are you trying to display a column listing the help topic, or do you want information when a ticket is assigned to a help topic?
      Performing the steps in Part 1 above will give you an idea of all the content that is easily displayed, have a look to see if the information appears there.

      Hope that helps

      Andrew

  4. Hi, Im using this mod to in the latest version, im able to add a new field on the list, but i got the ID. Im pretty new on SQL and php so i been banging my head to the wall for some time…

    I probably need to join the ID i get with the table `ost_list_items` where the name of that id is stored.

    Any help will be great.

    Thanks a lot for this Mod.

    Juan

    1. Hi Juan,

      First up, there are a handful of small changes between 1.9.4 & 1.9.5.1 (latest). So it’s probably best to use a tool like Winmerge to update it (when I get a chance I’ll do it & re upload the code).

      Secondly, are you getting any sort of output, or just a list of nothing? I’ve noticed with different installs, the way osTicket stores info keeps changing (which is annoying!)

  5. Will this mod work with department field, I cant seem to get it to work. It just throws a Unknown column ‘cdata.field_23’ in ‘on clause’

    I feel like I’m doing something wrong

  6. Hi Guys,

    Just updating you with my situation. I got it to work finally. When I look in the cdata table, it doesn’t actually save it as field_19 etc. The field is called whatever you put in the variable box. So mine was: room_num so I just added the following to the query: “cdata.room_num as Custom1,” after “cdata.subject,”

    Line 268 + 269 were no longer required. Neither was line 282.

    All working now. Hope this helps anyone else with the same error: “Unknown column ‘cdata.field_XX”

    1. Cheers MWaqas, thanks for your feedback.
      This is something I’ve noticed with various comments. osTicket 1.9.5.x has changed the way they are sorting the info (again), I’m guessing they are just trying to work out the most flexible way to make it all work.

  7. I’m also seeing changes w/the new version of OsTicket. @Andrew, just to clarify some things … should I expect your code mod to allow me to display custom fields that have been set on a default form (e.g. Ticket Details) in addition to custom forms? In previous versions the “ost_ticket__cdata” table was used to store data however now I’m seeing form entries on one of the ost_form* tables (ost_form, ost_form_entry, ost_form_entry_values, ost_form_field). I did get your code to work on v1.9.5.1 however it’s only showing data from custom fields that have been added to a built-in form.

  8. Thanks for plugging away @MWaqas. I also was able to get this to work. Two additional questions though. One, table `ost_ticket__cdata` seems to be used when adding custom fields to the built-in forms. Has anyone modified this code to show data from custom forms/custom lists? In a previous post I determined this information as being saved in these tables: ost_form, ost_form_entry, ost_form_entry_values, ost_form_field. Two, if pulling list data from the ost_ticket__cdata table only the list ID is being shown which relates to the ost_list_items table. Anyone clear this up yet? I’ll be digging through files today and hopefully get an update to everyone here.

  9. UPDATE: To properly show a custom list value from the ‘os_ticket_cdata’ table I modified the following code:

    step 1 (nothing new here):
    $ab_list[‘field_id’] = “room_num”; // column name from `ost_ticket__cdata` table
    $ab_list[‘heading’] = “Room #”; // Name displayed to front end users.

    step 2 (add a new JOIN):
    .’ LEFT JOIN ost_list_items custom ON (custom.id = cdata.’. $ab_list[‘field_id’] . ‘)’

    The above assumes that room_num is a custom list containing custom list items and the ECHO $QUERY saved my a$$!

  10. UPDATE: To properly show a custom list value from the ‘os_ticket_cdata’ table I modified the following code:

    step 1 (nothing new here):
    $ab_list[‘field_id’] = “room_num”; // column name from `ost_ticket__cdata` table
    $ab_list[‘heading’] = “Room #”; // Name displayed to front end users.

    step 2 (edit the select):
    .’ ,custom1.value as Custom1′ //AB – Custom 1

    step 3 (add a new JOIN):
    .’ LEFT JOIN ost_list_items custom1 ON (custom1.id = cdata.’. $ab_list[‘field_id’] . ‘)’

    The above assumes that room_num is a custom list containing custom list items and the ECHO $QUERY saved my a$$!

    1. It seems that there are lots of changes and variables.
      I’m going to have to think about how to make this universal….good work tho :)

  11. Andrew,

    First off thank you for your work on this. I was wondering how hard would it be to change this to show / display a ticket’s status from the ticket.status_id table? Basically what I want it to do rather than a custom column, I would like to to display the ticket status for the customer statues that were recently updated with OS ticket. I attempted to modify the SQL code that you produced, however I’m getting very limited results with tickets that are only for the system vs actual tickets that need to be displayed.

  12. Sorry Guys but.. im lost …. I,m very poor in php and my form simply not working.
    Any one have full working code this mudule for latest osTicket ?

    :-) ID i found but this is my only success :-(

  13. Great Guys! i’ve tested on osticket 1.9.6 just now and with the steps indicated by Jonathan all works fine!!!

  14. I did the mod. It’s giving me error :
    Unknown column ‘cdata.field_47’ in ‘on clause’
    —- Backtrace —-
    #0 (root)/include/mysqli.php(177): osTicket->logDBError(‘DB Error #1054’, ‘[SELECT ticket….’)
    #1 (root)/include/staff/tickets.inc.php(280): db_query(‘SELECT ticket.t…’)
    #2 (root)/scp/tickets.php(492): require_once(‘/var/www/suppor…’)
    #3 {main}

    I’m using version 1.9.7. Does the mod work in this version?

  15. i cant got it work for 1.9.8.1
    give me an error #1054 – Unknown column ‘cdata.field_43’ in ‘on clause’
    because dont exist the field cdata.field_43
    :,(

  16. Now, worked,!
    Just i change

    .’ LEFT JOIN ‘.TABLE_PREFIX.’form_entry_values fentry_val ON (cdata.field_’.$ab_list[“field_id”].’ = fentry_val.entry_id) AND fentry_val.field_id = ‘.$ab_list[“field_id”] //AB We need to link to the form values as of 1.9.4

    to…..

    .’ LEFT JOIN ‘.TABLE_PREFIX.’form_entry_values fentry_val ON fentry_val.field_id = ‘.$ab_list[“field_id”] //AB We need to link to the form values as of 1.9.4

    good luck to all..

    1. This will allow it to go through without getting an error for the field not being found, but it won’t actually pull in data related to the particular ticket. It will just find something with that field_id and put it in there for all of the items.

      I am on version 1.9.9 and was having the same problem. The custom field shows in the ticket__cdata table. This join was to make sure it pulled in the correct data per ticket based on the column name in ticket__cdata being the same as ‘field_xx’ where xx is the value from the other table. The newer version of osTicket actually has a different/set column name and doesn’t require the variable setup to use the field id. Just use the join that is already there for the cdata table and pull in the column in the select statement that you want to use. You can skip the beginning part where you set the variables and find the field id #.

  17. Ive got 2 issues…

    1. I have got my “Product”(Custom field/list) column showing up in the tickets over view but it is showing the data like –
    {“2″:”WOP – iOS App”}
    {“4″:”Mindbody+ActiveCampaign Sync”}
    etc.

    2. I am getting error emails from the system. —
    [SELECT count(DISTINCT ticket.ticket_id) FROM ost_ticket ticket LEFT JOIN ost_ticket_status status
    ON (status.id = ticket.status_id) LEFT JOIN ost_user user ON user.id = ticket.user_id LEFT JOIN ost_user_email email ON user.id = email.user_id LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id WHERE ( ( ticket.staff_id=1 AND status.state=”open”) OR ticket.dept_id IN (1,2,3) OR (ticket.team_id IN (1,2,3,4) AND status.state=”open”) ) AND status.state IN (
    ‘open’ ) AND field_id IN (38) ]

    Unknown column ‘field_id’ in ‘where clause’

    —- Backtrace —-
    #0 (root)/include/mysqli.php(177): osTicket->logDBError(‘DB Error #1054’, ‘[SELECT count(D…’)
    #1 (root)/include/mysqli.php(203): db_query(‘SELECT count(DI…’)
    #2 (root)/include/staff/tickets.inc.php(220): db_count(‘SELECT count(DI…’)
    #3 (root)/scp/tickets.php(506): require_once(‘/home3/funhubco…’)
    #4 (root)/scp/index.php(17): require(‘/home3/funhubco…’)
    #5 {main}

    ——————–

    Thanks in advance for some of your awesome wisdom lol.

    p.s kickass blog/tutorial guys..

    Thanks Kayne

  18. I have managed to get my custom list to show in my ticket overview, however i’m having a few troubles…

    1. My custom column data is showing like this – here is a screen shot http://tinypic.com/r/1zgx1ko/8
    {“2″:”WOP – iOS App”}
    {“4″:”Mindbody+ActiveCampaign Sync”}

    2. I keeping get emails about the following DB Error –

    [SELECT count(DISTINCT ticket.ticket_id) FROM ost_ticket ticket LEFT JOIN ost_ticket_status status
    ON (status.id = ticket.status_id) LEFT JOIN ost_user user ON user.id = ticket.user_id LEFT JOIN ost_user_email email ON user.id = email.user_id LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id WHERE ( ( ticket.staff_id=1 AND status.state=”open”) OR ticket.dept_id IN (1,2,3) OR (ticket.team_id IN (1,2,3,4) AND status.state=”open”) ) AND status.state IN (
    ‘open’ ) AND field_id IN (38) ]

    Unknown column ‘field_id’ in ‘where clause’

    —- Backtrace —-
    #0 (root)/include/mysqli.php(177): osTicket->logDBError(‘DB Error #1054’, ‘[SELECT count(D…’)
    #1 (root)/include/mysqli.php(203): db_query(‘SELECT count(DI…’)
    #2 (root)/include/staff/tickets.inc.php(220): db_count(‘SELECT count(DI…’)
    #3 (root)/scp/tickets.php(506): require_once(‘/home3/funhubco…’)
    #4 (root)/scp/index.php(17): require(‘/home3/funhubco…’)
    #5 {main}

    How do i fix these issue??

    Thanks heaps guys, awesome blog :)

  19. To Kayne :

    The error : Unknown column ‘field_id’ in ‘where clause’
    Simply move the :
    $qwhere.=’ AND field_id IN (‘.$ab_list[‘field_id’].’) ‘;
    To the line just after:
    $total=db_count(“SELECT count(DISTINCT ticket.ticket_id) $qfrom $sjoin $qwhere”);

    And the error should gone :) , the sentence is correct but not the position of the line.

    To Davide:
    Im with the version 1.9.12 and works like a charm , but you must follw the instruction that did the mate Sergey some post above

    To Sergey and Andrew :
    Both of you are my idols :)
    Great job and 1 million thanks

    1. Hi Bobby, I used your code, but it show me result in parentheses
      exepmle:
      {“22″:”244mm”}
      it should be only :
      244mm

      Second question, haw to add more custom columns ?

      Regards
      Greg

  20. Thanks for the work on this guys, code worked great.
    Anyone try adding this to the users ticket window? I’d like the same custom field there as well.

  21. For those who want to change the output format from i.e.{“22″:”244mm”} to 244mm

    The code to show will be

    $characters = preg_split(‘/”:”/’, $row[‘Custom1’], -1, PREG_SPLIT_NO_EMPTY); // ntx split string into two arrays before and after “:”

    $dirtybrand = array_slice($characters, 1); // split array into two strings and take the second one

    $cleanbrand = implode($dirtybrand); // convert array to string

    $brandprev = preg_split(‘/”/’, $cleanbrand, -1, PREG_SPLIT_NO_EMPTY); // split string $cleanbrand into two arrays , before and after ”

    $brand = array_slice($brandprev, 0, 1); // split array into two arrays and take only the first one

    $brandfinal = implode($brand); // convert array into string

    echo ($marcabien) ; // show el string
    ?>

    This code replace the original

    And is done , your {“22″:”Hello World”} will be converted to Hello World , without ” ” or { }

      1. Greg, it might be easier if you supply your entire file in a GitHub gist or Pastebin so that others can try & assist you.

  22. There was a problem with the paste I did where the ‘ were converted to ´ and the php was broken.

    Here is a new Pastebin with the “spanish” code and I think without problems.

    Just paste this php where originally was the “echo (isset($row[‘Custom1’])) ? $row[‘Custom1’]” and is done ;)

    Tell me if there is problems :)

  23. Hi Guys, in 1.9.12 version there writed that we can set a lot of fields:

    $ab_list[‘field_id’]=implode(“,”, array(‘XX’)); //ID from from _form_field table , now you can set a lot of fields

    Is this mean we can add more columns? How to do that?

  24. I dont need it, but just for curiosity I´ve tried to show two custom columns but without success.
    If somebody has done it will be great, I´m so sure it should be easy but I´m not able to do it.
    If somebody can give us some ligth will be great, thanks.

    1. In the part that renders the table (the

      cell) you should be able to add the column there, provided that you have the data that you want from the SQL query (otherwise you’ll need to tweak that as well)
  25. Thank you for the mod! I’m so very close to having this working on v1.9.12.
    But I’ve hit one small bump in the road, on the staff page only new tickets coming into the system since i mad the mod are showing. Otherwise it will say “There are no tickets matching your criteria.”
    Though i can see along the top of my ticket numbers… Open(3), My Tickets (7), Ect.. So there are tickets on the system.

    I’ve turned off //Echo $Query; in the hope after looking at this someone may be able to assist me.

    SELECT ticket.ticket_id,tlock.lock_id,ticket.`number`,ticket.dept_id,ticket.staff_id,ticket.team_id ,user.name ,email.address as email, dept.dept_name, status.state ,status.name as status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created ,IF(ticket.duedate IS NULL,IF(sla.id IS NULL, NULL, DATE_ADD(ticket.created, INTERVAL sla.grace_period HOUR)), ticket.duedate) as duedate ,CAST(GREATEST(IFNULL(ticket.lastmessage, 0), IFNULL(ticket.closed, 0), IFNULL(ticket.reopened, 0), ticket.created) as datetime) as effective_date ,ticket.created as ticket_created, CONCAT_WS(” “, staff.firstname, staff.lastname) as staff, team.name as team ,IF(staff.staff_id IS NULL,team.name,CONCAT_WS(” “, staff.lastname, staff.firstname)) as assigned ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(” / “, ptopic.topic, topic.topic)) as helptopic ,cdata.priority as priority_id, cdata.subject, pri.priority_desc, pri.priority_color ,fentry_val.value as Custom1 FROM ost_ticket ticket LEFT JOIN ost_ticket_status status ON (status.id = ticket.status_id) LEFT JOIN ost_user user ON user.id = ticket.user_id LEFT JOIN ost_user_email email ON user.id = email.user_id LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id LEFT JOIN ost_ticket_lock tlock ON (ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() AND tlock.staff_id!=1) LEFT JOIN ost_staff staff ON (ticket.staff_id=staff.staff_id) LEFT JOIN ost_team team ON (ticket.team_id=team.team_id) LEFT JOIN ost_sla sla ON (ticket.sla_id=sla.id AND sla.isactive=1) LEFT JOIN ost_help_topic topic ON (ticket.topic_id=topic.topic_id) LEFT JOIN ost_help_topic ptopic ON (ptopic.topic_id=topic.topic_pid) LEFT JOIN ost_ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) LEFT JOIN ost_form_entry fentry ON (fentry.object_id=ticket.ticket_id) LEFT JOIN ost_form_entry_values fentry_val ON (fentry_val.entry_id = fentry.id) LEFT JOIN ost_ticket_priority pri ON (pri.priority_id = cdata.priority) WHERE ( ( ticket.staff_id=1 AND status.state=”open”) OR ticket.dept_id IN (4,5,7) ) AND status.state IN ( ‘open’ ) AND field_id IN (34) ORDER BY effective_date DESC LIMIT 0,50

    Running the query on my SQL databse it just shows the ‘new’ tickets i see on the dashboard, so not sure what i can do with this info.

    If you would like to see my Ticket.inc code i’ll be happy to post,
    This is the one thing that’s stopping me from being able to use this mod unfortunately.

    Thanks

    1. Kyle,
      Any luck in getting this working v1.9.12?
      I am running v1.9.14 and I get similar results (no tickets being displayed, including new tickets).

      Thanks All!

  26. Hi, I’ve been trying to adjust all of this to work a bit differently. What I am trying to do is add a column that displays the user email instead of a custom form field.

    What is the best way to go about it. Just change the cdata table in the queries to user_email.address ?

  27. I dont want to add another column but to change the date, ticket opened date to a custom date which is know is ID=49, how can I get this date & not the date the ticket was opened into the table?

  28. Did anyone get this to work properly? I tried every code that was posted here, and I can’t get it to work. I’m on 1.9.15.

    I ONLY get the correct data if the table is sorted by the field I added, and ONLY in one direction. And even then only some of the rows are populated correctly. If I sort it the other way it is reading subject or priority into the rows instead of custom form value. It’s bizarre, and I can’t figure it out.

    If someone could post complete, working solution for 1.9.15 I would really appreciated. Even the revisions posted by A. Bennett to GitHub are not working.

    Any thoughts?

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *