Developer forum

Forum » Integration » Import price from ERP on familiy products

Import price from ERP on familiy products

Martin Grønbekk Moen
Martin Grønbekk Moen
Reply

I would like to get feedback from the community on one particular issue we have related to importing prices.

In our scenario we are importing all products from ERP (NAV).
There are two import jobs, one for products, and another one for prices.

This is working completly fine as long as the products are kept as single products.
But as soon as we group them as family products or variants the price import no longer know how to update the prices.

How are you guys handling price import from ERP whenever the variant information is not part of the ERP, but handled in DW?
Since the variant id is not beeing sent from the ERP, how is it then possible to update the prices for family/variant products?

Any advice is appreciated :)


Replies

 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Reply

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

 
Martin Grønbekk Moen
Martin Grønbekk Moen
Reply
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
 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Reply

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

 
Martin Grønbekk Moen
Martin Grønbekk Moen
Reply
Yes, please. A little example script would be great. Then I guess I will be able to complete it myself. Thanks!
 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

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

 
Anders Ebdrup
Anders Ebdrup
Reply

Yes, that would be very nice! :-)

 
Martin Grønbekk Moen
Martin Grønbekk Moen
Reply
Yes, please!
 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

+1

Adrian

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Reply

(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)

TableScripts.gif
 
Scott Forsyth Dynamicweb Employee
Scott Forsyth
Reply

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

        }
    }
}

 

You must be logged in to post in the forum