Developer forum

Hi Martin,

The trick with supporting combining into families on the website (in the backend) is to do all of your joining in the ERP on ProductNumber, and keep ProductId/ProductLanguageId/ProductVariantId non-mapped so that they can be managed on the website.

This means that your primary key should be ProductNumber for both your import jobs (products and prices). Language should be considered too if you have multiple languages. 

Then you'll be free to combine into variant families on the website, and every product will have a 1-to-1 mapping between DW and the ERP. Note that DW doesn't enforce uniqueness when working with ProductNumber, so it's on you to take care of preventing duplicates and such.

Scott

Thanks Scott! We already do primary key mapping on product number, and it works fine for updating family products. What I failed to mention is that we are using the price matrix to import prices. And as far as I can see there is no option to use product number as primary Key in EcomPrices? Martin

Hi Martin,

Ah, got ya. In that case, you're right, you'll have to use a table script to do that. Have the XML response include the ProductNumber and the table script to look up the primary key from the product number and swap that out. I have a number of table scripts for that very situation, but not for EcomPrices since we usually get them live (rather than in batch). But, let me know if I can help by pointing you in the right direction with a table script for that.

Scott

Yes, please. A little example script would be great. Then I guess I will be able to complete it myself. Thanks!

So an option to map prices on SKU only would make price imports easier...?

Yes, that would be very nice! :-)

Yes, please!

+1

Adrian

(to be honest, we have a ton os table scripts because it's not just importing on SKUs that we have issues with. It's on User Addresses on ExternalId, Assortments, Impersonation settings, setting ProductId based on SKU/ProductNumber . I attached a visual of all our common table scripts. I blurred some that are very specific to us)

Hi Martin,

While waiting for a solution on the platform, below is a starting point for a table script. I didn't test it but I reused some proven table script elements I already had so it should be pretty close.

There are some considerations on how to map it.

• In your mapping, have ProductNumber on the left map to PriceProductId on the right. 
• In your mapping, map PriceProductVariantId and PriceProductVariantId on the right to any dummy field on the left. You can create 2 empty fields in your XML just for that. You can't use a constant static field though. It needs to exist in the mapping. 
• Select the table script and test!

 

using System;
using System.Linq;
using System.Collections.Generic;
using Dynamicweb.DataIntegration.Integration;

namespace Dna.TableScripts
{
    public class EcomPricesSetProductIdByProductNumber : TableScript
    {
        private const string FilteredTableName = "EcomPrices";

        public override string ScriptingName => "PriceMatrix: Set ProductId/VariantId/LanguageId from ProductNumber. Must also have empty VariantId and LanguageId fields mapped (any name is ok but it cannot be used for any other mapping and it cannot be a constant/static field).";

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

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

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

            //get Product by ProductNumber, giving preference to any with a variant ID
            var sql = $"SELECT TOP 1 * FROM EcomProducts WHERE ProductNumber = '{productNumber.Replace("'", "''")}' ORDER BY ProductVariantID DESC, ProductLanguageID DESC";

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

            row[GetSourceColumnNameFromDestinationName(mapping, "PriceProductId")] = product.Id; //this will overwrite the one in the mapping
            row[GetSourceColumnNameFromDestinationName(mapping, "PriceProductVariantId")] = product.VariantId;
            row[GetSourceColumnNameFromDestinationName(mapping, "PriceProductLanguageId")] = product.LanguageId;
        }

        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}");
            }

        }
    }
}