H Jansen H Jansen - 1 month ago 5
SQL Question

Error invalid_value on Freshdesk when inserting contact persons from Exact Online

In a Freshdesk testing environment, the following SQL statement copies all contact persons involved with a sales order from Exact Online to Freshdesk:

insert into contacts@freshdesk
( name
, address
, phone
, email
)
select delivery_account_name
, fulladdress
, account_phone
, account_email
from freshdesktickets@inmemorystorage


The following SQL is used to prepare the Freshdesk tickets on basis of Exact Online:

create table FreshdeskTickets@inmemorystorage
as
select dlvl.delivery_account_name
, acad.fulladdress,acad.account_phone
, acad.account_email
from ExactonlineXML..DeliveryLines@eolnl dlvl
join fulladdress@inmemorystorage acad
on acad.id_attr = dlvl.delivery_deliveryaddress_id_attr
where dlvl.deliverydate_attr = '2016-10-07'
group
by dlvl.delivery_account_name
, acad.fulladdress
, acad.account_phone
, acad.account_email


This runs with no problems. When I execute this SQL statement on the Freshdesk environment of the customer, I receive an error with code itgenoda001 (see picture): Validation failed. All messages: invalid_value: exact_koppeling: It should be one of these values: 'Ja,Nee'

itgenoda001 error on Freshdesk

Obviously there are different versions of Freshdesk in use. How can I adapt my query such that works fine on both versions?

Answer

The reason you get this error message is that the your second Freshdesk installation has a custom field named exact_koppeling, which is missing in your test installation.

There is no easy way to avoid this. You either have to add the custom field to your test installation too, or you have to maintain two versions of the same statement.

I guess you need the insert statement like this, in order to make it work on your second installation:

insert
into contacts@freshdesk
( name
, address
, phone
, email
, exact_koppeling
)
select delivery_account_name
,      fulladdress
,      account_phone
,      account_email
,      'Ja' -- Or 'Nee'
from   freshdesktickets@inmemorystorage