Developer forum

Hi Martin,

1)

you can use the TableScript with finding the existing product by number using the api: Dynamicweb.Ecommerce.Services.Products.GetProductByNumber() and once found take its Id
so the code can look like that:
using System;
using System.Linq;
using System.Collections.Generic;
using Dynamicweb.DataIntegration.Integration;

namespace CustomTableScripts
{
    public class EcomProductsRelatedSetProductIdByProductNumber : TableScript
    {
        private const string FilteredTableName = "EcomProductsRelated";        

        public override void ProcessInputRow(Mapping mapping, Dictionary<string, object> row)
        {
            if (mapping.DestinationTable == null || mapping.DestinationTable.Name != FilteredTableName)
            {
                return;
            }

            var productNumber = row[GetSourceColumnNameFromDestinationName(mapping, "ProductRelatedProductId")].ToString();

            //don't map if the value is empty
            if (string.IsNullOrEmpty(productNumber))
            {
                return;
            }            

            var product = new Dynamicweb.Ecommerce.Services.Products.GetProductByNumber(productNumber)
            if (product == null)
            {
                //product wasn't found so leave the row as it is
                return;
            }

            row[GetSourceColumnNameFromDestinationName(mapping, "ProductRelatedProductId")] = product.Id; //this will overwrite the one in the mapping

            productNumber = row[GetSourceColumnNameFromDestinationName(mapping, "ProductRelatedProductRelId")].ToString();

            //don't map if the value is empty
            if (string.IsNullOrEmpty(productNumber))
            {
                return;
            }

            var product = new Dynamicweb.Ecommerce.Services.Products.GetProductByNumber(productNumber)
            if (product == null)
            {
                //product wasn't found so leave the row as it is
                return;
            }

            row[GetSourceColumnNameFromDestinationName(mapping, "ProductRelatedProductRelId")] = product.Id;            
        }

        private string GetSourceColumnNameFromDestinationName(Mapping mapping, string destinationColumnName, bool destinationColumnExistenceIsOptional = false)
        {
            var activeColumn = mapping.GetColumnMappings().FirstOrDefault(c => destinationColumnName.Equals(c.DestinationColumn.Name, StringComparison.CurrentCultureIgnoreCase))?.SourceColumn?.Name;
            if (destinationColumnExistenceIsOptional)
            {
                return activeColumn ?? null;
            }
            else
            {
                return activeColumn ?? throw new InvalidOperationException($"Table Script: unexpected error has occurred since the destination table doesn't have a column called {destinationColumnName}");
            }

        }
    }
}

2)

no

BR, Dmitrij