Error invalid_value on Freshdesk when inserting contact persons from Exact Online

Question

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'

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

Answer 1

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