Duplicated Incident Numbers when using Transform Map

[xMTinkerer]

Hello,

  I am building an integration that makes updates to existing Incident records. We are only updating the State, Work Notes and Assigned To. I've created a temp table that extends the Import Set Row table and added 4 fields: State, Work Notes, Assigned To and Incident Number. In the field mapping, only Incident Number is set to coalesce.

However, when I insert new records to this temp table, I am getting new Incidents created with the same Incident Number (ex INC0010006). It was my understanding that the coalesce check box would ensure an update instead of an insert. So, why is my transform map creating an Incident with the same number instead of updating the existing incident?

Thanks!

  --- Nomad

[Sashi K1]

It should. Give a try this way. Write a script on field mapping to make sure source has a value and its trimmed prior to mapping. Also print to logs to verify what is coming from source.

Did you also consider using correlation_id at task level too instead of auto number.

Thanks

Ajay

[xMTinkerer]

Hello Ajay. Thanks for the quick response!

I added this to the Transform Map script:

log.error( "TESTING? source.u_incident_number: [" + source.u_incident_number + "]" );

And the system log shows this:

Note the "inserts: 1".

As for correlation_id, from here, it seems that is used for holding the external system's item id? We have a many items to one Incident here, so I'm not sure that would be applicable. But if I am misunderstanding, please let me know.

[Mike Allen]

I only like using the sys_id as the coalesce field for this exact reason.

What happens when you set your number field to be unique = true in incident?

[xMTinkerer]

Well, we were using sys_id as the coalesce field, however we ran into this primary key violation:

FAILED TRYING TO EXECUTE ON CONNECTION 0: INSERT INTO task (`u_req_category`, `a_ref_5`, `upon_approval`, `knowledge`, `made_sla`, `state`, `reopen_count`, `approval_set`, `sys_created_on`, `reassignment_count`, `impact`, `urgency`, `opened_at`, `u_noc_flag`, `sys_domain`, `priority`, `sys_updated_by`, `notify`, `u_task_requested_for`, `sys_mod_count`, `u_environment`, `upon_reject`, `u_impacted_users`, `u_all_assignment_groups`, `work_notes`, `sys_updated_on`, `sys_id`, `approval`, `number`, `u_suppress_notification`, `u_public`, `sys_created_by`, `incident_state`, `opened_by`, `sys_class_name`, `active`, `a_ref_3`, `escalation`, `contact_type`, `assignment_group`) VALUES(NULL, '239f0a3187843100308481b60b434d38', 'proceed', 0, 1, 1, 0, NULL, '2014-10-30 19:32:45', 0, 3, 2, '2014-10-30 19:32:45', 0, 'global ', 4, 'xmatters', 2, '239f0a3187843100308481b60b434d38', 0, 'Production', 'cancel', 1, '5ee25b5f0a0a3c1b01a791a8880bac17', NULL, '2014-10-30 19:32:45', '3afa27468788310094776d5709434dab', 'not requested', 'INC910835', 0, 0, 'xmatters', 1, '239f0a3187843100308481b60b434d38', 'incident', 1, '5ee25b5f0a0a3c1b01a791a8880bac17', 0, 'direct input', '5ee25b5f0a0a3c1b01a791a8880bac17')

Unique Key violation detected by database (Duplicate entry '3afa27468788310094776d5709434dab' for key 'PRIMARY')

: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '3afa27468788310094776d5709434dab' for key 'PRIMARY': 

Can you explain more about that unique = true? I don't see a unique checkbox on that column when i view the table, and I don't see any "unique" fields in the transform map or transform fields.