Column 'customer_id' (tab. 'ticket') not populated with id

[Stichel]

Hello!

I configured the external database mapping in Config.pm and chose the following settings:

Code: Select all

CustomerKey => 'email'
CustomerID => 'cnum'
...
Map => [ ...
'UserLogin', 'Username', 'email', ...
'UserCustomerID', 'CustomerID', 'cnum', ...
'UserEmail', 'Email', 'email', ...
... ]

Whenever a ticket is created, the customer information of the sender is displayed correct in OTRS. Only the customer id is filled with the customer's email address. If you click on the link it fails because OTRS cannot map a customer id to an email address.

I recognized that in table ticket there are two columns customer_id and customer_user_id and both columns get populated by the customer's email address which I think is wrong. When I write the customer id into column customer_id directly, it is displayed correctly in the OTRS agent ticket zoom.

Why is column customer_id populated with the customer's email address instead of it's customer id?

Thanks for support!

[crythias]

Existing tickets won't be retroactively adjusted if you change the customer_id. Use Generic Agent to retro fix.

[Stichel]

Thanks crythias for help!

This even happens to new tickets after the configuration of customer_id has been changed!?

Regarding retro fix, which job do you suggest me to create for this purpose?

[crythias]

This even happens to new tickets after the configuration of customer_id has been changed!?

No. It will rely on the CustomerUser configuration in Config.pm (make sure SysConfig doesn't have something, too.)

Regarding retro fix, which job do you suggest me to create for this purpose?

It's unfortunately a pain, because I don't know your parameters.

If you were creating everyone in a company/department with the same CustomerID, I'd search for customer_id = *@domain.tld and Set customer_id=company
But if it's completely changed, you're going to have a task on your hands. While not recommended to do so, you *could* mass update the ticket table directly (after backup of database).

Works like this:
Create a spreadsheet. Column A is email addresses/old customer_id and Column B is new customer_id.
Column C is something like:

Code: Select all

="update ticket set customer_id = """ & B2 & """ WHERE customer_id = """ & A2 & """;"

copy that down your list, then copy the result into a sql interface for your otrs data.

Test a sample entry or 2-3 before you do the massive update.

In theory, it shouldn't hurt much, but it's a one way change, and cannot be undone (though you can swap the column entries and go "back" unless you're combining customer_ids/they would no longer be a one-to-one and you'd have to consider other options.).

If you're not comfortable with this, you can use the API to do it but ... basically, same deal.