Developer forum

Forum » Integration » Change the Product ID

Change the Product ID

E-mail notifications
Tomas Gomez
Reply
Posted on 30/10/2019 15:27:32

Hi,

I have a question about changing the Product ID. We integrated all the products in our website with a custom Product ID (CUSTOMIDENTIFIER001). Now this identifier is not valid anymore and we would want to use the default Dynamicweb identifier (PROD001) . We would want to change all the IDs in a single batch.

We don't know if we can change the ID of the products directly in the EcomProducts table of the database, because that would break the relations with other tables.

What do you think is the best option to modify the ID of all the products at the same time? 

Regards,
Tomas


Replies

 
Nicolai Pedersen
Posted on 30/10/2019 15:45:08
Reply
This post has been marked as an answer

There is no best option... Only going through the database and do the hard work. I would create a backup field on the product table for the old product id, and replace all the ids on the productid column - and then go trough all tables where the product id is present and replace using that backup column.

Maybe create a script that can loop trough all tables, all fields and look for all productids in each field, and replace with a new value... But the simplest version is probably to do it table by tabled with a cup of coffee or 2!

Also remember to replace product ids in Paragraph.ModuleSettings and item types with fields that link to products etc.

BR Nicolai

Votes for this answer: 1
 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Posted on 30/10/2019 15:52:52
Reply
This post has been marked as an answer

Hi Tomas,
I am not sure if my proposition will work fine but you can try to export all Dynamicweb tables that have the relations to ProductId's column using
Dynamicweb Provider -> Xml provider job to some xml file and try to replace the string values CUSTOMIDENTIFIER to PROD and then
create a database backup and delete data from the relation tables where there were replaced values and then reimport that xml file to Dynamicweb database.

Best regards,
Dmitrij

Votes for this answer: 1
 
Tomas Gomez
Posted on 31/10/2019 13:39:08
Reply

It is not a simple task... thanks for the ideas!

I'm trying Dmitriy's suggestion, I'll let you know :-)

BR, Tomas

 
Tomas Gomez
Posted on 04/11/2019 11:18:17
Reply

Hi again,

We finally did the change of the IDs. We are still in staging development, so it has been easier because there are no orders or other product related items. Anyway, I write down the steps for the record:

  1. Create a new integration task to export the EcomProducts into a CSV file.
  2. Delete the content of the EcomProducts table.
  3. Modify the CSV file with new ProductIDs.
  4. Create a new integration task to import the CSV file into the EcomProducts table.
  5. Update the new ProductIDs in the EcomProductCategoryFieldValue and EcomGroupProductRelation tables. We used the ProductEAN field, that is another product identificator, to match the products in the SQL sentences:

    • UPDATE [EcomGroupProductRelation] SET [GroupProductRelationProductId] = [EcomProducts].[ProductId] FROM [EcomProducts]
      WHERE [EcomGroupProductRelation].[GroupProductRelationProductId] = [EcomProducts].[ProductEAN]

    • UPDATE [EcomProductCategoryFieldValue] SET  [EcomProductCategoryFieldValue].[FieldValueProductId] = [EcomProducts].[ProductID] from [EcomProducts] WHERE [EcomProductCategoryFieldValue].[FieldValueProductId] = [EcomProducts].[ProductEAN] 

  6. That's all, folks!

Regards,
Tomas

 

You must be logged in to post in the forum