Mapping Fields from external DB

[fullhouse]

Hello,

we are currently receiving our customer information from an external database. The Mapping is done in Config.pm:

Code: Select all

# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'UserTitle',      'Title',      'cp_title',       1, 0, 'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'cp_givenname',   1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'cp_name',        1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'cp_email',       1, 1, 'var', '', 0 ],
           # [ 'UserPassword',   'Password',   'cp_otrs_password', 0, 0, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'cp_email',       0, 1, 'var', '', 0 ],
            [ 'UserPhone',      'Phone',      'cp_phone1',      1, 0, 'var', '', 0 ],
            [ 'UserMobile',     'Mobile',     'cp_mobile1',     1, 0, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customernumber', 0, 1, 'var', '', 0 ],
            [ 'ValidID',         'Valid',     'cp_valid_id',    0, 1, 'int', '', 0 ],

Lately we have added three different new fields to our customer users on the external database. One is plain text, one is multiselect and one is a dropdown. How would i go about adding these to the Config.pm? Do I need to create dynamic fields in OTRS first? And if I do, how do i get the data to fill these dynamic fields?

[fullhouse]

Can anyone point me in the right direction here?

[root]

Hi,

It's all there. Usually, add a new line like

Code: Select all

 [ 'UserFieldname',      'FielLabel',      'Columnname',       1, 0, 'var', '', 0 ],

.

But I have not idea what you mean with drop down or multiselect. There is no option for this in ANSI SQL.

- Roy

[fullhouse]

I tried doing that but it doesn't work. The fields are not being displayed and the Overview when looking at all our customer users seems messed up.

Might the problem be that I can only import simple Text Fields this way?

[root]

Hi,

What is the DDL of the table you're trying to use?

- Roy

[fullhouse]

Hey Roy,

The DDL used is SQL.

[root]

Hi,

Ok, let's make it clear. What field type in SQL is a multi-select? What's your create table statement?

- Roy

[fullhouse]

Thanks a lot for your help. The Creation/Altering of the table was done by someone else but I got the information now:

Code: Select all

CREATE OR REPLACE VIEW kivi_customer_contacts
AS
 SELECT contacts.cp_title,
    contacts.cp_givenname,
    contacts.cp_name,
    contacts.cp_email,
    contacts.cp_phone1,
    contacts.cp_mobile1,
    contacts.cp_valid_id,
    customer.customernumber,
    customer.name,
    customer.street,
    customer.zipcode,
    customer.city,
    customer.country,
    customer.phone,
    customer.fax,
    customer.homepage,
    customer.notes,
        CASE
            WHEN customer.obsolete IS NULL OR NOT customer.obsolete THEN 1
            ELSE 2
        END AS customer_valid_id,
    customer.itime AS create_time,
    customer.mtime AS change_time,
    customer.employee AS change_by,
    customer.employee AS create_by,
   (select text_value from custom_variables where config_id=22 and trans_id=customer.id) as Supportvertrag,
   (select text_value from custom_variables where config_id=8 and trans_id=customer.id) as Reaktionszeit,
   (select text_value from custom_variables where config_id=2 and trans_id=customer.id) as "Gebuchte Pakete"
   FROM contacts
     LEFT JOIN customer ON contacts.cp_cv_id = customer.id
  WHERE customer.id IS NOT NULL AND contacts.cp_email IS NOT NULL AND contacts.cp_email <> ''::text AND contacts.cp_valid_id;

The new Fields are Supportvertrag, Reaktionszeit and Gebuchte Pakete

[root]

Hi,

I guess your problem is the column "Gebuchte Pakete", what's your entry in the mapping for this field?

- Roy

[fullhouse]

This is how it looks like:

Code: Select all

            [ 'UserTitle',      'Title',      'cp_title',       1, 0, 'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'cp_givenname',   1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'cp_name',        1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'cp_email',       1, 1, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'cp_email',       0, 1, 'var', '', 0 ],
            [ 'UserPhone',      'Phone',      'cp_phone1',      1, 0, 'var', '', 0 ],
            [ 'UserMobile',     'Mobile',     'cp_mobile1',     1, 0, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customernumber', 0, 1, 'var', '', 0 ],
            [ 'ValidID',         'Valid',     'cp_valid_id',    0, 1, 'int', '', 0 ],
            [ 'UserReaktionszeit', 'Reaktionszeit', 'reaktionszeit', 1, 0, 'var', '', 0 ],
            [ 'UserSupportvertrag', 'Supportvertrag', 'supportvertrag', 1, 0, 'var', '', 0 ],
            [ 'UserGebuchtePakete', 'GebuchtePakete', 'gebuchte pakete', 1, 0, 'var', '', 0 ],

[root]

Hi,

Try this

Code: Select all

            [ 'UserGebuchtePakete', 'GebuchtePakete', '"gebuchte pakete"', 1, 0, 'var', '', 0 ],

whitespace in a column name shouldn't be there, even if it's possible

- Roy

[fullhouse]

That was actually it! The syntax must still not be correct though.

I first tried doing it your way but it didn't work out. Then i deleted the row GebuchtePakete and just left the other two. The other two Fields are now being displayed and filled correctly. I guess now I just gotta find out the right syntax or tell the guy to change the field so there are no spaces in it.

Anyways, thanks a lot for your help!! Really appreciate it.