Business One - DTW - Delete and modify 19K Address records

[NewBoldCorp]

Good afternoon.

I have a client with a customer that has over 19,000 CRD1 (aka addresses) records. I need to delete about 350 of them and modify about 5,000 – 6,000 of them. I am getting the error message “This entry already exists in the following tables (ODBC -2035) This entry already exists in the following tables (ODBC -2035)65171”.  None of the values within my Address (the address name) column are duplicated.

This BP has address records where the LineNum values are not in sequential order as other users have manually removed BP address records via client as needed.

After exporting all of the records and all of the fields from the CRD1 table for the business partner and properly populating the fields for DTW (i.e tNO/tYES, bo_BillTo/bo_ShipTo, etc.), I have tried the following…

• DTW address file contain all address (19K+) records except for the records to be deleted.
• The LineNum column started with zero and increased by 1 for each additional row regardless of the current LineNum value in SAP
• Amazingly the DTW didn’t tank, but the error message above was returned.
• DTW address file was reduced to 3,000 address records
• The 3000 does not include those that should be deleted
• The LineNum column started with zero and increased by 1 for each additional row regardless of the current LineNum value in SAP
• Error message above was returned.
• DTW address file was reduced to only 2 records
• Only a single bill to & ship to record with the LineNum column populated with 0 and 1 respectively.
• The error message above was returned.

I DO NOT understand the update logic of the CRD1 table for a business partner. I totally understand how the LineNum column/field should be populated starting with zero and increasing by for each row. I do this all the time with marketing docs, service call expense documents (aka SCL4) and service contract lines. The LineNum field for CRD1 seems to have no purpose beyond a count or sort order. It’s not used as an identifier. The index on the table is a cluster that doesn’t contain the LineNum field.

SAP Notes, blogs, Community Q&A don’t address how to update via DTW addresses when there have been significant manual removals. This is a regular issue as the client's customer will have significant address changes over time and will need regular address maintenance.

Any assistance on this matter would be greatly appreciated as I am at my wit's end.

[ANKIT_CHAUHAN]

Hi NewBoldCorp,

The most common mistake that we have observed in this regards, is that the users are using the customized templates.

Always create the templates from the DTW as below:

1. In the Data Transfer Workbench menu bar, choose Templates → Customize Template.
2. Choose the Select Object button and select an object.
3. To create a new template, right-click a structure, choose Create Template for the Structure, and set the path to save the template.
4. Fill the data and import using DTW.

We suggest not to change the column order from the order in the standard templates.
We suggest not to delete any columns from the standard templates. if a value is not required to be updated leave the column blank.

About your other questions, only adding new records or updating existing ones is supported via DTW.
It is not possible to delete/remove/cancel/close records using DTW for main objects.

We would ask you to refer to SAP KBA 3109415 for Importing Business Partner Addresses Using DTW.

Hope it helps!

Kind regards,

ANKIT CHAUHAN

SAP Business One Support