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?
- 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.
- 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> <?php echo $row[‘Custom1’]; ?> </td>
<!– AB – Custom Col 1 –>
~ Line 511
Finally update the footer colspan to make everything balanced.
<td colspan=”8″>
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.
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
hey, thanks for replying. It is ‘Short Answer’ . In this case, what would I need to modify in the SQL?
Thanks
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).
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
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.
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
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
You should be able to model off the other rows, and by substituting different column names getting different columns.
I hope this helps
Andrew
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
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
Got it. Thanks!!
All appears to be working well.
-Doug
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.
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
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
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!)
ON client page can we add a column of ticket assigned to:
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
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”
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.
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.
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.
@Jonathan, in regards to your issues with the ost_form_entry* tables, here is some code I modified for Keith, it does a lookup into the ost_form_entry_values table. See if you can adapt it (its for 1.9.4, not the latest 1.9.5.1). https://gist.github.com/a-bennett/dd4118b455f0d5787891 The instructions are found in lines 6,7,8. Maybe that can help?
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$$!
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$$!
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 :)
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.
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 :-(
Great Guys! i’ve tested on osticket 1.9.6 just now and with the steps indicated by Jonathan all works fine!!!
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?
got it working, but the sort option generte an error. I can sort other column fine exexct the custome column
I would like to know how you make it work :)
Thx.
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
:,(
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..
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 #.
It doesn’t work correctly.
See below to find working solution from my side
Solution for versions 1.9.7 and above.
There is no more column cdata.field_XX and we need one more LEFT JOIN and additional WHERE.
https://github.com/spikalev/osticket_multiple_fields/commit/f2f4d6e9d03eeb2fd6961a69c051f4c0a50312bb#diff-608431b1d6b19826e700101a9f360084
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
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 :)
Can someone make it for the latest version 1.9.12?
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
Full working version without errors in 1.9.12 updated (in pastebin, sorry):
http://pastebin.com/XBkTZxte
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
So I have everything working but for some reason it’s showing the id of the custom list with this. how do I get it to only show the room?
http://tinypic.com/r/33yno1y/9
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.
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 { }
Sorry the code was not pasted correctly
Here is a link to a paste bin with the full code.
Please let me know if I did some mistakes in the translation from spanish to english in order to be more readable
http://pastebin.com/hfz1LArn
Hi Ignacio, I had pasted it as you writed, but it doesn’t show me any tickets now.
Greg, it might be easier if you supply your entire file in a GitHub gist or Pastebin so that others can try & assist you.
Hello Andrew, here is my file
https://github.com/greg1104/os-ticket/blob/master/ticket.inc.php
As I writed before I need to change {“22″:”244mm”} to 244mm
And also If you can help me, how to add more custom columns to the file. Is it that I have to add the same extra code as to ‘custom1’ or it is any different way to do that. I also want to hide some of a default colums.
Thanks for any help
Regards
Greg
Should work, let me try the code and today o will post the fix, or the spanish verison im using without problems ;)
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 :)
Sorry, the paste:
http://pastebin.com/jTNQ52Lf
It’s working, thanks :)
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?
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.
In the part that renders the table (the
Would be please possible to get the same howto for version 1.10?
Thanks a lot.
I’ll see what I can do by the start of next week :)
any updates?
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
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!
Same here, it seems after applying this mod, no tickets being displayed…
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 ?
Hi,
have anyone already tested the custom field modification with version 1.9.14?
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?
Hi,
Do you have the solution for V1.10 ?
In this version I doesn’t find from sql clause.
Someone can help me ?
I haven’t had a chance to look at the newest version yet sorry.
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.