[BUG] - CSV Upsert behaves like Insert, fails to Update records

[AlwaysThinkin]

Attempts to use "operation": "Upsert" with a CSV fail with "duplicate value found" errors. Bulk API Job Details show the operation as insert instead of upsert. Behavior is consistent with insert operations.

To reproduce, use an object with an external ID text field set to Unique Case Insensitive. Create a CSV with an existing record's external ID and an additional field with a new value. Use run --sourceusername csvfile to start the job. Note that any records in the CSV that have an external ID that is not in Salesforce will get inserted successfully; also that the same failing record will succeed when changing the operation to update in export.json.

The result expected is that the record will be updated by reference with the external ID. And that if the Bulk API V2 is used, it will show the operation as upsert. Similar data from a CSV was successfully upserted with Apex Data Loader.

In export.json, I've tried it with excludeIdsFromCSVFiles set to true and false as well as having ID in the query or but get the same result for all 4 combinations.

export.json 2024-06-03__17_50_04.log Contact_insert_target.csv Contact_source.csv

[hknokh]

Hello, @AlwaysThinkin

Thank you for reporting a bug. I will take a look at it as soon as possible and let you know of any updates.

Cheers

[github-actions[bot]]

This case has been marked as 'to-be-closed', since it has no activity for the 3 days.
It will be automatically closed in another 3 days of inactivity.

[jorgesolebur]

@hknokh I am having the same issue as @AlwaysThinkin . I am using SFDMU with source CSV and target a SF sandbox. I have defined a compounded external ID with an UPSERT operation (export.json available below - object cgcloudMetadata_Wizard_Configurationc). A record having compounded external ID with value A is present in both CSV and sandbox already, before executing SFDMU (but some of the other field values are different). I am expecting SFDMU to UPDATE record A, not insert it.

However when I check the logs, there is a file generated cgcloudMetadata_Wizard_Configurationc_insert_target.csv instead of cgcloudMetadata_Wizard_Configurationc_update_target.csv. The logs show that SFDMU has tried indeed to INSERT the record instead of updating.

Hopefully I have a custom validation in SF to reject the INSERT as it is a DUPLICATE, but in other objects I do not have it. This is a CRITICAL issue, because that means that SFDMU will keep INSERTING records every time we run the command and there are changes between source and target. I am afraid I will need to stop using SFDMU and start doing manual imports (or pass to CCI...)

Can you please look into this whenever u can? Happy to help with all the info you need :-)

export.json 2024-06-07__14_15_51.log

[hknokh]

Hello,

I wanted to let you know that I am still reviewing your case and will inform you as soon as I have any updates. Due to my current time constraints, this process may take longer than usual.

Thank you for your patience and understanding.

Best regards

[jorgesolebur]

@hknokh I have been testing a little bit and it seems that the issue only appears when we define an Upset operation with an external ID that is composite (defined by concatenating more than 1 field) : "externalId":"Name;cgcloud__Sales_Org__c",

[jorgesolebur]

@hknokh I have done further testing and even if the value defined in "externalId" in the JSON file is not composite (only 1 field) for some objects they are always inserting data (even if the record exist in CSV and target sandbox with the same externalId value), and for other they are doing upsert correctly. A little bit weird to be honest.

[jawills]

I have seen this behavior when there is a duplicate record found for the external id. e.g if there are two products call 'Test Product', and the externalId is set to Name, a third product will be created.

[jorgesolebur]

any updates @hknokh ?

[hknokh2]

Not for now. It requires deep investigation, and unfortunately, I haven't had time for it so far. I will update you when I have more information.

[hknokh2]

Thank you, @jawills, for highlighting this issue.
@jorgesolebur @AlwaysThinkin

This behavior is expected when there are multiple records with the same external ID.

Here's an explanation:

The tool does not use Salesforce's native Upsert functionality because it supports any type of external ID field, including compound fields—one of SFDMU's key advantages. When there are two records with the same external ID, the tool maps the first source record to the first target record. It then locks this external ID to the first target record, preventing reuse for any subsequent records with the same ID. As a result, the second record is treated as a new record that does not exist in the target and is therefore inserted.

It's crucial to ensure that your records are unique by external ID.

[github-actions[bot]]

This case has been marked as 'to-be-closed', since it has no activity for the 3 days.
It will be automatically closed in another 3 days of inactivity.

[github-actions[bot]]

This case has been closed, since it has no activity for the last 6 days. Feel free to reopen it, if you need more help.