Transform Map not coalescing on sys_id for the target table

[S A Martin]

I have a data source using an XLSX attachment. It has a column to identify records by their sys_id to update the existing records.  None of the other values on the target table are unique, and the ability to update any of the other fields is one of the requirements.  They also want to be able to import new records by leaving the sys_id empty in the spreadsheet.

My initial mapping only includes the source's Sys_Id (u_sys_id) to the target table's Sys Id (sys_id) and a name pairing (e.g source.u_name > target.name).  When I run the transform, it inserts all records even the ones with a matching Sys_Id.

• The records are being imported to a domain-scoped table
• The imported records are being placed in different domains on the table
• The import is being run from the TOP domain

I think the domain separation may be the cause of the trouble because this seems like it should be the most basic kind of coalesce.

Any help would be immensely appreciated

[bammar]

I would try the following approach. Assuming the destination table you want to update has a task number - use that number/column to coalesce - and if your spreadsheet has no task number then it will create a new record by default. 

[S A Martin]

Unfortunately, the target table does not have a task number.  It's a custom table that doesn't extend any of the core tables.  The sys_id is the only field on the table that contains unique values.