Dennis Kavadas wrote:
> Am interested in setting up a backend database lookup to pul our client
details into OTRS
> Has anyone managed to achieve this ?
Dear Dennis:
I'm not really sure what you're asking here, since OTRS by definition has a
back end database for client details. I'm assuming you want a method to
populate the OTRS customer database back end. If you understand basic
database administration, there are two tables in the OTRS database you'll
need to populate:
customer_company
customer_user
The easiest way to upload data into these fields is to build an SQL
statement that will do it, and you can actually ask the OTRS database to do
this for you. Here is one way of doing so based on the assumption that you
the most common type of OTRS installation (it can be on a test or
production server) using MySQL for the database back end.
1. Use PHPMyAdmin, a database manager.
2. Click your OTRS database to select it.
3. In the OTRS database click the checkbox by the customer_company
table, then select the Browse action (the first action icon). This will
open the table.
4. At the top of the PHPMyAdmin screen you should see >hostname
>databasename >customer_company .
5. Choose the Export tab.
6. Accept the defaults (sql export type, no compression).
7. Click Go in the lower right hand corner.
8. This will download a file called customer_company.sql which contains
the query you need to populate the table.
Here's what the query looks like with one sample record for
customer_company:
INSERT INTO `customer_company` (`customer_id`, `name`, `street`, `zip`,
`city`, `country`, `url`, `comments`, `valid_id`, `create_time`,
`create_by`, `change_time`, `change_by`) VALUES
('00001', 'Sample Company Name', '1234 Any Street', 'postcode', 'Yourcity',
'Yourcountry', NULL, NULL, 1, '2011-12-15 13:00:00', 3, '2011-12-15
13:00:00', 3),
('00002', 'Another Company Name', '1234 Different Street', 'postcode',
'Yourcity', 'Yourcountry', NULL, NULL, 1, '2011-12-15 13:00:00', 3,
'2011-12-15 13:00:00', 3);
The bit from INSERT INTO to VALUES is the command, the rest is data. Here
two customer company records are created. You should have your naming
convention in mind already for customer_company. If you want to be sure,
manually populate one record in the OTRS front end.
Use the same method for customer_user. The fields are different for each
user but the customer_id field will be the same for each user of the same
company.
Note that in the command the fields are in backticks and delimited by
commas. In the record the fields are in single quotes and delimited by
commas. Each record is inside parentheses, a comma at the end of each
record and a semicolon at the end of the last record. Assuming you have a
customer list already, open your list in a spreadsheet tool, then use the
spreadsheet to arrange your fields in the order shown here. Save the
spreadsheet as a comma-delimited file then use a text editor to format the
sql query properly.
Performing these steps is fairly simple but may be unfamiliar to you if you
have not done any database administration. If you find it difficult, you
will need help from anyone with some basic database experience.
Be sure to make a copy of the database and do your testing on that, being
sure to make a backup each time before you attempt loads.
This will either be enough information to help you achieve your goal of
populating your customer list into the OTRS database, or if you find this
too daunting you will probably want to hire or ask for help from someone
with a bit more database experience. Good luck with your OTRS deployment.
Rob M.
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
