Data in target file is not updating - new record created when it shouldn't

[Kirsten2]

I have a xls file with columns and data loaded in to an import set table:

I then transform using TMap with mapped fields. Purchasing Document is a unique field so coalesce is true.

After Transform I check the ast_contract and a new record is created instead of updating the existing record. The Contract Number is the PO# and is unique in the xls and import file.  The first record is the one that was created when it should have been the second record updated. Also, the first record was created without the PO# even though it is in the import file.

What have I missed? Thank you!

[MugTm09]

Hello, @Kirsten2 .
Please check whether the cell format type in the XLS file matches the field type of the target table for import.
You may be able to find more detailed hints by reviewing the logs to see how the generated transformation map performed the conversion.

[Kirsten2]

The PO# in ast_contract and the import file is string. In the excel that field is General.

[MugTm09]

Hello, @Kirsten2 .
Perhaps the cell format in Excel should be set to 'Text'.(String)

[Bhuvan]

@Kirsten2 

Check your import set table if the value is populated correctly as the value should not be blank. If coalesce field is empty, you can create onBefore transform to ignore creation.

https://www.servicenow.com/docs/bundle/zurich-integrate-applications/page/administer/import-sets/con...

Can you share the mapping for u_purchasing_document to po_number to check coalesce settings. What is the data type for the po_number field ?

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan