I am following your instructions for adding a custom field in the ticket view but am running into an odd issue, hoping you can help. I have a custom list that is for each location of my organization. I’ve found the form ID, in my case it’s 36. I’ve added the list to the ticket details form and have given the list the variable name location.
In doing that the ticket list now shows the new field in the heading but I get ” there are no tickets matching your criteria”. I found that if I remove the variable from the form field I see all of the tickets again but none of them have data populated in the new column. Any idea where my trouble lies?
Hi Doug,
Hmmm odd, a couple of other people have reported similar things.
I did notice v1.9.5 came out last week, and I’m yet to look at that.
The best place to start is by finding the line in tickets.inc.php that says
//echo $query
And remove the //
Reload the ticket page and you should see a huge messy SQL query that starts with SELECT …..
Copy that into a tool like phpMyAdmin and run the SQL query. It will return a more useful error than a blank page.
If you can’t work out from there, send me the SQL & the error and i might be able to help point you in the right direction.
Apologies, I should have mentioned that I ran the SQL query after reading your other posts on the topic. When I run it I get SQL Error (1054): Unknown column ‘cdata.field_36’ in ‘on clause’
As crazy as it sounds, the error is helpful.
It’s saying that it can’t find the right column inside of the table ost_ticket__cdata
The easiest way I’ve found to work this out is to do the following:
1). Create a ticket and in your new feild that your trying to get to display as a column use some really silly answer, such as “Bananas”. (As long as the word Bananas isn’t likely to appear anywhere else).
2). Start by trying to find a row in the table ost_tickets__cdata that has the word Bananas in it – if you’ve found it, look at the column name – it’s probably not “field_XX” from the error. Readjust the ID in the custom tickets.inc.php
All should be sweet.
3). If you can’t find it. Search the entire database for the word Bananas (I also tend to use % like % just in case it’s stuck with other data).
If the data is from a list, you’ll likely have to do a join. I’ve done some basic work around this somewhere.
I created a list called Dogs and populated with choices for husky and beagle. I then created a ticket and selected one of them.
In the ticket__cdata table I see a row labeled ‘dogs’ so I changed the ‘field_id’ from 37 (line 13) to dogs and received this similar error:
Unknown column ‘cdata.field_dogs’ in ‘on clause’
I then tried to remove the leading ‘field_’ from tickets.inc.php (line 282) but now see the tickets view is completely empty. The DB error shows that it’s still looking for cdata.field_dogs so I must have missed something.
Andrew, I’m hoping you could help a little with the following problem I’m having with OsTicket. I recently added some javascript to the edit view to disable certain input fields. this was setup as a safeguard so people didn’t easily change data. everything works great including a button to enable those fields except for the fact that OsTicket is dynamically changing form input IDs. the javascript will never work when logging in w/other user accounts b/c OsTicket changes the form input ID.
i am trying to hunt down how the form is rendered and how i can pull those input IDs.
$field->render(); in file ‘staff/templates/dynamic-form.tmpl.php’
UPDATE: took 2 days of digging. found this thread and removed the random ID based off user session. I have my install non-public so i’m not worried about spammers (yet).
Sorry for my delayed reply.
I’ve been off working on a bunch of projects. I’m glad you were able to find a solution.
My idea would be to select it by the DOM in like nth object.
So something like the 4th input inside the form.
I’m guessing that the order isn’t going to change.
Hi Mark, if you have a bit of PHP & SQL knowledge it shouldn’t be hard. The only thing that might get tricky is if you need to join two tables to get the human readable description of the status. If I had a space moment I’d look for you, but with Easter, I’ll be out of the office for a few weeks.
thank u for the wonderful tutorials .. i am using osticket and want to know how i can query the database so that i can get the entire data for a ticket in one row (i.e include all custom field in form_entry_value) for that particular ticket.
i tried this but it is crap.
SELECT ticket.ticket_id,ticket.`number`,ticket.dept_id,val.field_id,val.value,ticket.staff_id,ticket.team_id,user.name, email.EMAIL as email, dept.dept_name,status.state,status.name as status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created
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_email email ON user.id = email.email_id
LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id
LEFT JOIN ost_form_entry entry ON ticket.ticket_id = entry.object_id
LEFT JOIN ost_form_entry_values val ON entry.id = val.entry_id
Hello
Thank you for the various tutorials on your website on osTicket.
I would also add a column, but I would display information on a form created manually and this information is not contained in osticket__cdata.
Can you help me?
What information do you need?
Thank you in advance
Sorry for my English, I’m French.
WAGET Kevin
Hi Kevin,
I’ve sent you an email, I may or may not be able to assist, hopefully I can at least point you in the right direction.
Andrew
HI Andrew,
Thank you for your contributions to osTicket.
I am following your instructions for adding a custom field in the ticket view but am running into an odd issue, hoping you can help. I have a custom list that is for each location of my organization. I’ve found the form ID, in my case it’s 36. I’ve added the list to the ticket details form and have given the list the variable name location.
In doing that the ticket list now shows the new field in the heading but I get ” there are no tickets matching your criteria”. I found that if I remove the variable from the form field I see all of the tickets again but none of them have data populated in the new column. Any idea where my trouble lies?
Thank you,
Doug Taylor
Hi Doug,
Hmmm odd, a couple of other people have reported similar things.
I did notice v1.9.5 came out last week, and I’m yet to look at that.
The best place to start is by finding the line in tickets.inc.php that says
//echo $query
And remove the //
Reload the ticket page and you should see a huge messy SQL query that starts with SELECT …..
Copy that into a tool like phpMyAdmin and run the SQL query. It will return a more useful error than a blank page.
If you can’t work out from there, send me the SQL & the error and i might be able to help point you in the right direction.
Cheers
Andrew
Andrew,
Apologies, I should have mentioned that I ran the SQL query after reading your other posts on the topic. When I run it I get SQL Error (1054): Unknown column ‘cdata.field_36’ in ‘on clause’
I’m stuck. Thanks again!
Doug
As crazy as it sounds, the error is helpful.
It’s saying that it can’t find the right column inside of the table ost_ticket__cdata
The easiest way I’ve found to work this out is to do the following:
1). Create a ticket and in your new feild that your trying to get to display as a column use some really silly answer, such as “Bananas”. (As long as the word Bananas isn’t likely to appear anywhere else).
2). Start by trying to find a row in the table ost_tickets__cdata that has the word Bananas in it – if you’ve found it, look at the column name – it’s probably not “field_XX” from the error. Readjust the ID in the custom tickets.inc.php
All should be sweet.
3). If you can’t find it. Search the entire database for the word Bananas (I also tend to use % like % just in case it’s stuck with other data).
If the data is from a list, you’ll likely have to do a join. I’ve done some basic work around this somewhere.
Let me know how you go.
Getting somewhere…kind of.
I created a list called Dogs and populated with choices for husky and beagle. I then created a ticket and selected one of them.
In the ticket__cdata table I see a row labeled ‘dogs’ so I changed the ‘field_id’ from 37 (line 13) to dogs and received this similar error:
Unknown column ‘cdata.field_dogs’ in ‘on clause’
I then tried to remove the leading ‘field_’ from tickets.inc.php (line 282) but now see the tickets view is completely empty. The DB error shows that it’s still looking for cdata.field_dogs so I must have missed something.
Doug
Andrew, I’m hoping you could help a little with the following problem I’m having with OsTicket. I recently added some javascript to the edit view to disable certain input fields. this was setup as a safeguard so people didn’t easily change data. everything works great including a button to enable those fields except for the fact that OsTicket is dynamically changing form input IDs. the javascript will never work when logging in w/other user accounts b/c OsTicket changes the form input ID.
i am trying to hunt down how the form is rendered and how i can pull those input IDs.
$field->render(); in file ‘staff/templates/dynamic-form.tmpl.php’
any help would be appreciated.
UPDATE: took 2 days of digging. found this thread and removed the random ID based off user session. I have my install non-public so i’m not worried about spammers (yet).
https://github.com/osTicket/osTicket-1.8/issues/135
Sorry for my delayed reply.
I’ve been off working on a bunch of projects. I’m glad you were able to find a solution.
My idea would be to select it by the DOM in like nth object.
So something like the 4th input inside the form.
I’m guessing that the order isn’t going to change.
Andrew, have you had to create a custom query in the URL yet? This seems to have worked w/older versions of OsTicket but not newer versions. This was the only thread I found online: http://osticket.com/forum/discussion/comment/93172#Comment_93172
Hey Andrew,
I am looking to add a ticket status column to my staff panel. I am using OST V1.9.5.1. Do you know of a solution that will work?
Regards,
Mark
Hi Mark, if you have a bit of PHP & SQL knowledge it shouldn’t be hard. The only thing that might get tricky is if you need to join two tables to get the human readable description of the status. If I had a space moment I’d look for you, but with Easter, I’ll be out of the office for a few weeks.
thank u for the wonderful tutorials .. i am using osticket and want to know how i can query the database so that i can get the entire data for a ticket in one row (i.e include all custom field in form_entry_value) for that particular ticket.
i tried this but it is crap.
SELECT ticket.ticket_id,ticket.`number`,ticket.dept_id,val.field_id,val.value,ticket.staff_id,ticket.team_id,user.name, email.EMAIL as email, dept.dept_name,status.state,status.name as status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created
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_email email ON user.id = email.email_id
LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id
LEFT JOIN ost_form_entry entry ON ticket.ticket_id = entry.object_id
LEFT JOIN ost_form_entry_values val ON entry.id = val.entry_id
Hi Darth,
Are you trying to use everything in the ticket queue screen, or are you just trying to make an SQL query of everything?
Also, which version of osTicket are you running?
I basically want to dump all the custom field associated with the ticket in one row.. I am using version 1.9
Osticket is 1.9 and i want to get SQL query of everything especially all the ticket details + custom form fields linked to that ticket as ROWS
Hello. I want to ask how to add the SLA Plan name in the export ticket file and in SCP ticket’s view. Please help.
I am using osticket version 1.9.15