Itgenclr083: De externe server heeft een fout geretourneerd: (422) Unprocessable Entity

[forums]

When synchronizing a list of contacts from memory with ActiveCampaign, an error message occurs:

itgenclr083: De externe server heeft een fout geretourneerd: (422) Unprocessable Entity.
itgenacn045: duplicate email: E-Mail-Adresse existiert bereits im System. (email = john.doe@acme.com, phone = +99 12 345 67 89, firstName = John, lastName = Doe).

However, the following query returns no result for john.doe@acme.com:

select *
from   v3.contacts
where  email = 'john.doe@acme.com'

The error message can also be triggered via:

insert into v3.contacts
( email
)
values 
( 'john.doe@acme.com'
)

How can I solve / avoid this?

[forums]

The duplicate email addres check in ActiveCampaign is a little bit special.

The column Email shows the email address loaded, but the equality comparison seems to be done case-insensitive (see Ist bei E-Mail-Adressen die Groß- und Kleinschreibung wichtig? | Mailchimp).

It seems that the formal meaning can be different, but this is not a good practice.

The following email addresses are different for the Invantive synchronize statement:

• john.doe@acme.com
• John.Doe@ACME.com

Both email addresses are considered equal by ActiveCampaign, leading to the “duplicate email” error.

It is highly recommended to make sure all contacts have a lower-case email address.

The following code makes all contacts have a lower-case email address:

declare
  l_cnt_casing pls_integer;
begin
  create or replace table Contacts_Ist@inmemorystorage
  as
  select *
  from   v3.Contacts
  ;
  --
  l_cnt_casing := 0;
  for r in
  ( select t.id
    ,      t.Email
    from   Contacts_Ist@inmemorystorage t
    where  t.Email != lower(t.Email)
    order
    by     t.Email
  )
  loop
    dbms_output.put_line('Correct casing of ' || r.Email || ' (ID #' || to_char(r.Id) || ').');
    update Contacts
    set    Email = lower(r.Email)
    where  id = r.id
    ;
    l_cnt_casing := l_cnt_casing + 1;
  end loop;
  dbms_output.put_line
  ( 'Corrected casing of ' 
    || to_char(l_cnt_casing) 
    || ' contact email addresses.'
  );
end;