Hi Guys,

We are looking at creating some new drop down menus on the ticket screen.

1 -> Company

2 - > Company Contact

What we are aiming to do is create some new fields within the MySQL Database

and on the 'Company' Drop-Down menu call on the contents of the column called 'Company'. When the company in question has been selected we would like the system to link direct to a company contact which will then be populated in the 'Company Contact' Drop-down.

Basically, Select Company. Company will then call on a list of Contacts to choose from in a seperate drop down menu. Which in turn would populate their email address from another field with MYSQL - Also the ability to add 'New Contact's would be great if someone new calls up.

The company we work for runs its backend system on MSSQL so creating a routine to port information out and into the backend of MYSQL is not a problem. We would just need some guidence of the coding side of things.

Currenty running version: 1.6.0

I look forward to hearing from some of you Guru's ^_^

Hi Guys,

I've had bit more of a thought about this and I think we would need 2 tables. One for Companies and One for Company Contacts+ Emails.

Table 1

CompanyID | CompanyName

Table 2

CustomerContactID | CustomerContact | EmailAddress

Anyone got some type of coding along the lines of what we are trying to achieve?

Hi guys,

anyone able to shed some light on where we should at least start looking?

Are there going to be lots of companies hence the need for MySQL or could this be done using :

http://www.plus2net.com/javascript_tutorial/dropdown-list.php(http://www.plus2net.com/javascript_tutorial/dropdown-list.php)

Hi Tomt,

Many thanks for your reply.

That is exactly what we are looking for but the information to be pulled in from mySQL fields within a table. Any ideas?

Are there going to be lots of companies hence the need for MySQL or could this be done using :

http://www.plus2net.com/javascript_tutorial/dropdown-list.php(http://www.plus2net.com/javascript_tutorial/dropdown-list.php)

Tomt, looking at this form though. I would be very handy indeed to have this placed on a new ticket as well.. It mean we could select if it was Hardware/Software and then which item in question.

Following the link I posted, there is an example of getting the data from a MySQL database and populating the dropdown lists.

I tend to try this sort of thing, then embed it into the ticketing software once i know it works.

Hi Tomt,

Excellent find! Many thanks for that.. Will look into this and try to get it working and the have a go an putting it into the new ticket screen..

Would you know which files I would need to input this coding into to make it work with a new ticket (sorry bit of a n00b when it comes to php)

For a client on a new ticket = include/client/open.inc.php

For staff on a new ticket = include/staff/newticket.inc.php

For a client on a new ticket = include/client/open.inc.php

For staff on a new ticket = include/staff/newticket.inc.php

Many thanks for that Tomt,

However, not being very ofay with PHP. i'm not to sure where you would place the javascript if there are not header of body tags?

Just incase someone out there is a PHP guru and have a spare 30mins on their hands.. this is what we are hoping to achieve..

I have attached the Screenshot.

Select Company - Will read off a list in a table from MySQL.

Support Type - Will read off a static list of 2 choices 'Hardware' or 'Software'.

thanks in advance for any help available.

ss.JPG

Hi guys,

As I dont expect everything to be handed to me on a plate, I have managed to figure out and put into place the Drop-down menu's 'Tomt' so kindly found me as you can see from the screen shot.

Now I just need some help/advice on how to get this into the ticket (as seen in previous screen shot)

thanks in advance guys.

ss2.JPG

backup and open \include\client\open.inc.php

FIND:

<tr>

<th width="20%">Full Name:</th>

<td>

<?if ($thisclient && ($name=$thisclient->getName())) {

?>

<input type="hidden" name="name" value="<?=$name?>"><?=$name?>

<?}else {?>

<input type="text" name="name" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

</tr>

REPLACE WITH:

<tr>

<th width="20%">Full Name:</th>

<td>

<?if ($thisclient && ($name=$thisclient->getName())) {

?>

<input type="hidden" name="name" value="<?=$name?>"><?=$name?>

<?}else {?>

<input type="text" name="name" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

<th width="20%">Select Company:</th>

<td width="20%">

<select size="1" name="D1">

<option>option1</option>

<option>option2</option>

</select>

</td>

</tr>

FIND:

<tr>

<th nowrap >Email Address:</th>

<td>

<?if ($thisclient && ($email=$thisclient->getEmail())) {

?>

<input type="hidden" name="email" size="25" value="<?=$email?>"><?=$email?>

<?}else {?>

<input type="text" name="email" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

</tr>

REPLACE WITH:

<tr>

<th nowrap >Email Address:</th>

<td>

<?if ($thisclient && ($email=$thisclient->getEmail())) {

?>

<input type="hidden" name="email" size="25" value="<?=$email?>"><?=$email?>

<?}else {?>

<input type="text" name="email" size="25" value="<?=$info?>">

<?}?>

&nbsp;<font class="error">*&nbsp;<?=$errors?></font>

</td>

<th width="20%">Select Support:</th>

<td width="20%">

<select size="1" name="D1">

<option>option1</option>

<option>option2</option>

</select>

</td>

</tr>

EDIT the dropdown to suit your needs :)

Hi Tomt,

Many thanks! That is excellent and will come in very handy.

However, we need this on the staff side when opeing new tickets as we do not intend to allow the customer to create their own support tickets just yet.

Which files will need to be adjusted to this to show on a Staff created support ticket.. and once save would this information be carried through?

Your a star for helping me out. Thank you very much for this. ^_^

Apply the same idea as above to include/staff/newticket.php

Then you'll need to edit include/class.ticket.php to retrieve your new form fields and write them to the database.

Hi Tomt,

Many thanks for you help on this.

I have manged to put the information in to the Staff ticket which is great.. the next step will be to alter the .class file to write the new information to the table.

My question is, I guess I have to create a new field in the table for 'Support' & 'Company'? If so which table does this need to be written to?

The second step would be to somehow merge the javascript into PHP so the drop down menu pulls through the information.

By default when a new ticket is created, the ticket 'detail' is written to ost_ticket the body (text) of the ticket is written to ost_ticket_message

Both of these tables share similar fields and the data is joined between the two.

Where you write to and how you do it will depend on what you ultimately want to achieve !

For my multi company mod I created a new table called ost_company and created the fields I needed in their. I then added the field comp_id to ost_company, ost_ticket and ost_ticket_message.

My MySQL queries can then pull the correct data using the comp_id fields.

I run a test version of OSticket locally so I can edit and change the code and database to suit quickly. I also use HeidiSQL to look at the database structure.

Hope this helps :)

Hi Tomt,

First of all, many thanks for your help so far. I am not a php or javascript kind of guy so all your help is warmly received.

You asked what I ultimately want to achieve so I decided to take the time and show you in word form (guided with screenshots of where i'm at with this project).

The main goal is to have a ticket on the 'staff' side which would have additional fields that are populated by a MySQL table and this information to be filtered through OSticket to relavent sections. The main field to be added would be the Company list.

As you can see from Screenshot 1, I have managed to place the code in correctly for the staff new ticket screen.

In screenshot 2, Show the code use sent me in use but inScreenshot 3 in the SQL command I am needing to merge to be able to product the pulled through information.

Example of this can be seen working in Screenshot 4 and 5.

Once that has been achieved then I would need the informatio to be filtered through to the saved ticket screen shown in Screeshot 6 and then finally on the 'List ticket view' instead of the heading 'Department' showing.. We would need 'Company' as shown in screenshot 7.

I understand I would also need to edit the .class file but i'm not really too sure where to begin?!

any help would be great. Many thanks in advance for being so kind.

regards

Mark

ss1.JPG

ss2.jpg

ss3.jpg

ss4.jpg

ss5.JPG

P.S...Please find attached 6 & 7 as i'm only allowed to upload 5 images to a post.

ss6.jpg

ss7.JPG

Hi

I'm working away for the rest of this week.. hopefully someone else will jump in !