Import set - Unable to resolve target record, coalesce values not present

[Mira1992]

Hello there,

I need some help with understanding the import set for the Customer data.

Customer reports this behavior:

"Import set failed on the 18th, 21st and 23rd, but ran successfully on the 19th, 20th, 22nd and 24th.

Within the u_snowflake_data_consumers import from the 20th, we included a new user record named Matthew Newton. On the 20th and 22nd, this user was not created as a new consumer. On the 24th he was. We don’t understand why he wasn’t created on the first 2 imports, but was today.

This user didn’t exist on the Consumers table, so the consumer Import Set would typically insert the user into that table. It did finally do this today, but on 20th and 22nd this row failed with the error code ‘Unable to resolve target record, coalesce values not present: u_user’"

sys_transform_entry
source field: u_user
target field: u_jtc_user
coalesce: true

On the sys_transform_map they have Unused coalesce fields for empty values:
source field: u_user
target field: u_jtc_user
coalesce: true
coalesce empty fields: false

I found this in the SN Support KB0547762
Cause
"This error is seen on transform when the coalesce value in the source table (import set table) is empty (for transform types below):

• Synchronous transform - In the case of a customized Web Service/REST import, the most likely cause is that the record is inserted into the source table, but the coalesce value is not yet populated. This happens when the coalesce value is Updated in the import set table after the insert. The Synchronous transform triggers on an insert into the source table, and since the coalesce field is empty, throws the error.
• Asynchronous transform – In this case, the coalesce is most likely on a script that is returning a NULL value."

But that would not explain why it did not work the first two runs and worked fine the third.

Do you have any idea what might be the issue?
Thanks a lot. Any help appreciated!

[Mark Manders]

Was the data on the import table the same all three days?

[Mira1992 · Accepted solution]

Hello Mark,

that was what I asked the customer as it seems that the user field was empty. They will check the source if that is the cause.

My email to them:

I've reviewed the issue and unfortunately, I can't find any obvious reasons on the ServiceNow side for the errors you've encountered. The most plausible explanation is that there might be differences in the data in the import table on the days when the import set failed.

In particular, it seems that the 'u_user' field may have been empty in the source data on those occasions. This could explain why the import set was unable to resolve the target record, leading to the error message ‘Unable to resolve target record, coalesce values not present: u_user’.

When you open the scripted relation of that related list, there is a query containing a script include "TransformMapRelatedLists."

The function getUnusedCoalesceFieldsForEmptyValues focuses on coalesce fields in the transform map. If a coalesce field is empty and coalesce_empty_fields is set to false, this could prevent the system from properly matching records, leading to errors.

This suggests that there may be an issue with how the coalesce fields are configured, particularly when they are empty. If a coalesce field (u_user) is expected to be populated and it's not, and if the coalesce_empty_fields property is set to false, the system won't be able to resolve the target record, resulting in the errors you're seeing.

This script could be related to your import issue, especially if:

• The u_user field is a coalesce field - which it is.
• The u_user field is empty in some source records - which might be the case.
• The transform map is configured to not allow empty coalesce fields (coalesce_empty_fields is false) - which it is set as false.

The coalesce_empty_fields is set to false at the moment. We can consider setting it to true.

Unfortunately, I am not able to check the data source (file), and all the 20 test loaded records have the user field populated. Would it be possible from your side to compare the source data on the days when the import failed versus the days when it was successful?
Additionally, could you adjust some of the test records so that some of them have an empty user field? This might help us identify if the absence of data in the 'u_user' field is indeed causing the issue.

[Mark Manders]

But you have access to the import table, right? Is the user in there or not? 

[Mira1992]

Hello Mark,
thank you for your interest and for your help.

User was not there but I needed a confirmation from the customer to check the input file.
And their response:
The issue here was that we created the user on our core system on the 19th, but we didn’t create a website login ID for them until the 22nd at 7am