Table of Contents

Ecom provider

Import and export Ecommerce related data to and from DynamicWeb

The Ecom Provider can be utilized to abstract many of the more technical tasks involved when importing and exporting Ecommerce data.

As a destination provider, you can add incomplete data to the Ecommerce database, automatically generating the missing information. This means that you can supply a list of names, or product IDs, and have the Ecommerce Provider take care of the rest.

As a source provider, the Ecom Provider generates somewhat more simple and readable data structures, making it possible to substitute many of the IDs for their name (so a product will not simply have a list of IDs in the Groups column, but rather the names of the group).

Memory requirements

Because the Ecom Provider generates missing structures and relations, it uses more memory than the DynamicWeb Provider - as a rule of thumb, expect the memory requirements to be three times the size of your input data file.

Used as a source

When used as a source provider, the Ecom Provider allows you to specify whether you want the ID or the Name of e.g. the manufacturer column to be exported

Ecom provider as source

You can specify ID or Name for:

  • The Groups column for variants
  • The Manufacturer column for products
  • The VariantGroups column for products
  • The Groups column for products
  • The RelatedProducts column for products and variants
  • The RelatedGroups column for products and variants

Used as a destination

When used as a destination provider (i.e. to import data into DynamicWeb) you have access to more extensive customization options.

Ecom provider as destination

Option Description Interacts with Comments
Default language Set the default language for the imported products
Shop Select a shop to import products to
Insert only new records When ON, new records will be inserted, but existing records will not be deleted or updated If Delete incoming rows is ON, this option is ignored. If Update only existing records is ON, you will get an error/warning - obviously they cannot function together
Update only existing records When this option is ON the imported rows are updated but not inserted. When OFF rows are updated and inserted If Delete incoming rows is ON, Update only existing records is skipped. When this option is ON, Update only existing products is skipped
Deactivate missing products When ON missing products are deactivated. When OFF no action is taken. When Delete incoming rows is ON, Deactivate missing products is skipped. The Hide deactivated products option is used only when Deactivate missing products is ON
Remove missing rows after import in the destination tables only Deletes rows not present in the import source from the destination table ONLY When Delete incoming rows is ON, this option is ignored
Use strict primary key matching This import affects ONLY records that match the selected primary key If not checked, the provider tries the following: Check ProductId, check ProductNumber, check ProductName - if none of these match, a new record is created
Remove missing rows after import Deletes rows not present in the import source from destination tables & relation tables When Delete incoming rows is ON, this option is ignored
Update only existing products When this option is ON the imported products are updated but not inserted. When OFF products are updated and inserted If Delete incoming rows is ON, Update only existing products is skipped. If Update only existing records is ON, Update only existing products is skipped
Create missing groups Check this box to let the Ecom provider create groups present in the source that do not already exist in DynamicWeb
Delete incoming rows Deletes existing rows present in the import source When Delete incoming rows is ON, the following options are skipped: Update only existing products, Update only existing records, Deactivate missing products, Remove missing rows after import, and Delete products/groups for languages included in input.
Delete products/groups for languages included in input Deletes products and groups only from the languages included in the import When Delete incoming rows is ON, this option is ignored
Discard duplicates When ON, duplicate rows are skipped
Hide deactivated products When Deactivate missing products is ON, this option hides the deactivated products If Delete incoming rows is ON, this option is ignored. If Deactivate missing products is OFF, this option is ignored.
Repositories index update Select any number of indexes to update after the job has been run This may temporarily affect performance.
Disable cache clearing This setting disables cache clearing after import
Persist successful rows and skip failing rows Checking this box allows the activity to do partial imports by skipping problematic records and keeping the successful ones.
Use existing Product ID found by Number in Variant Products When checked, the values in ProductId and ProductVariantID will be used at import to update products in DynamicWeb. A product is only updated if it matches the ProductNumber (in default language) of the imported row and if the DynamicWeb ProductVariantId field is not empty
Ignore empty category field values When checked, the Ecom provider does not write empty category field values to the database

Three of the settings – Delete products / groups for languages included in input, Deactivate missing products & Remove missing rows after import – are resolved in a clear hierarchy, depending on which tables are being imported to:

When “Delete incoming rows” is OFF the hierarchy is the following:

  • IF the current importing table is EcomProducts or EcomGroups and Delete products / groups for languages included in input is ON the products and/or groups are deleted in the languages included in input
  • ELSE IF the table is EcomProducts and Deactivate missing products is ON, the products are deactivated (and also hidden if Hide deactivated products is ON)
  • ELSE IF Remove missing rows after import is ON, the missing rows are deleted

Which tables and columns?

The Ecom Provider lets you access the following tables:

  • EcomProducts
  • EcomGroups
  • EcomManufacturers
  • EcomProductsRelated
  • EcomVariantGroups
  • EcomVariantOptions
  • EcomProductCategoryFieldValue

Here are the most important columns for each table – and a short description of how to treat them:

EcomProducts

Column Description
ProductID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
ProductLanguageID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows.
ProductVariantID If this row contains a value, it will be used as a partial primary key for updating existing rows
ProductDefaultShopID If included in import, this field should be set to the ID of a shop from the EcomShops table. Not validated by database or provider
ProductImageSmall Should contain a partial path to the image file, relative to the files folder (e.g. /Images/Ecom/Products/bike10.jpg)
ProductImageMedium Should contain a partial path to the image file, relative to the files folder (e.g. /Images/Ecom/Products/bike10.jpg)
ProductImageLarge Should contain a partial path to the image file, relative to the files folder (e.g. /Images/Ecom/Products/bike10.jpg)
ProductStockGroupID If included in import, this field should be set to a value from the EcomStockGroups table. Not validated by database or provider
ProductVatGrpID If included in import, this field should be set to a value from the EcomVatGroups table. Not validated by database or provider
ProductManufacturerID If this field is set to a value that already exists in the EcomManufacturers table, a reference to the relevant row will be created. If the value does not already exist, a manufacturer with name of the value will be created, and the reference will be added
ProductActive Should be included, should have value “true” for active products (and “false” for inactive products).
ProductPeriodID If included in import, this field should be set to a value from the EcomPeriods table. Not validated by database or provider
ProductDefaultUnitID If included in import, this field should be set to a value from the EcomProductUnits table. Not validated by database or provider
Groups This field should contain a list of GroupIDs or GroupNames, in the following format: "Group1","Group2","Group3". If the list contains only one group, it should still be enclosed in quotation marks. If one or more values in the list are not found in the EcomGroups table, new groups are created, named for these values
PrimaryGroup For SEO purposes you may want to indicate which group is primary, given that the product exists in multiple groups
GroupSorting This field should contain a list of integers, indicating the sort order of the product in the "Groups" field. The first integer in the list indicates the sort order of the product in the first group in the “Groups” list described above, the second indicates the sort order in the second group, and so on. Field is not mandatory
VariantGroups This field should contain a list of VariantGroupIDs or VariantGroupNames, in the following format: "VariantGroup1","VariantGroup2","VariantGroup3". If the list contains only one VariantGroup, it should still be enclosed in quotation marks. If one or more values in the list are not found in the EcomVariantGroups table, new VariantGroup are created, named for these values
VariantOptions This field should contain a list of VariantOptionIDs. The VariantOptionID is the id of EcomVariantOptions

EcomGroups

Column Description
GroupID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
GroupLanguageID If left blank this field will be assigned the ID of the default language of the solution. Otherwise used as a partial primary key for updating existing rows
GroupSmallImage Should contain a partial path to the image file, relative to the files folder
GroupLargeImage Should contain a partial path to the image file, relative to the files folder
GroupManufacturerID If included in import, this field should be set to a value from the EcomManufacturers table. Not validated by database or provider
GroupVatGroupID If included in import, this field should be set to a value from the EcomVatGroups table. Not validated by database or provider
GroupStockGroupID If included in import, this field should be set to a value from the EcomStockGroups table. Not validated by database or provider
ProductCategoryID If included in import, this field should be set to a value from the EcomProductCategories table. Not validated by database or provider
ParentGroups This field should contain a list of GroupIDs or GroupNames in the following format: "Group1","Group2","Group3". If the list contains only one group, it should still be enclosed in quotation marks. If one or more values in the list are not found in the EcomGroups table, new groups are created, named by these values
ParentGroupsSorting This field should contain a list of numeric values. The value indicates the place in the sort order below the group given in ParentGroups. Therefore the list should have same amount of sorting values as there are values in the list in ParentGroups. First values applies to first group in list ParentGroups. Second value applies to second group in list ParentGroups, and so forth
Shops Even though named "Shops", and even though supported by data model, multiple shops in this list is not supported by the UI (same GroupId applied to multiple shops). This field should always be holding only one ShopId. Apply a value like: "SHOP1"
ShopSorting This field should contain a list of numeric values. The value indicates the place in the sort order below the shop given in Shops. Therefore the list should have same amount of sorting values as there are values in the list in Shops. First value applies to first shop in list Shops. Second value applies to second shop in list Shops, and so forth.

EcomManufacturers

Column Description
ManufacturerID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows

EcomProductsRelated

Column Description
ProductRelatedProductID Obligatory field. Must contain the ID of the product you are creating relations from
ProductRelatedProductRelID Obligatory field. Must contain the ID of the product you are creating relations from
ProductRelatedGroupID This field can contain either an ID or a Name of a group in the EcomProductsRelatedGroups table, or the name of a group to be created. If a matching group is found, the relation is added to that group. If not, the group is created If this field is not included, the relation is added to an automatically generated group
ProductRelatedLanguageID This field can contain either then name or ID of the language of the related product. Will be assigned a value if left blank

EcomVariantGroups

Column Description
VariantGroupID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
VariantGroupLanguageID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows

EcomVariantOptions

Column Description
VariantOptionID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
VariantOptionLanguageID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
VariantOptionGroupID If left blank, this field will be assigned a value. Can contain either the name or the ID of variant groups. If it contains the name of a variant group which does not exist that group will be created

EcomProductCategoryFieldValue

Column Description
FieldValueFieldID If left blank, this field will be assigned a value - otherwise used as a partial primary key, for updating existing rows
FieldValueFieldCategoryID Mandatory, partial primary key. Should correspond the value of the ProductCategoryID (the same as the name of the category in table EcomProductCategory)
FieldValueProductID Mandatory, partial primary key. Should correspond the ProductID of the product
FieldValueProductVariantID Optional, partial primary key. Should correspond the VariantID of the product
FieldValueProductLanguageID Mandatory, partial primary key. Should correspond the LanguageID of the product
FieldValueValue Optional. The actual value of the field value

EcomStockUnit

Column Description
StockUnitId Mandatory, if nothing is mapped to this column, the EcomProvider will insert the ProductDefaultUnitId of the product determined by the StockUnitProductId, StockUnitVariantId and Default language ID
StockUnitQuantity Should correspond to the quantity of the particular StockUnitId
StockUnitProductId May be used to look up DefaultProductUnitId of a product
StockUnitStockLocationId When importing to EcomStockUnits, the EcomProvider will try to match the mapped source column to a StockLocationId in the EcomStockLocation table. If no StockLocationId matches the source, the provider makes a look up in the StockLocationName column instead.
StockUnitVariantId May be used to look up DefaultProductUnitId of a product

Important notes on Product ID

Note

As can be seen in the above column descriptions, id and language columns are added automatically, if they are missing.

Products are matched on:

  1. Id
  2. Product number
  3. Name

In that order! This means that if the ProductId and ProductNumber in source (like an import file) is not found in the destination (EcomProducts), but the ProductName IS found in destination (EcomProducts), then this is considered a match. If you don't want for this behavior to be in effect, make sure that you set the key settings accordingly.

  • Product groups are matched on ID and name, in that order.
  • Relations are matched on ID and name, in that order.
  • Cleanup is done after an activity is completed, if this is activated in settings.
  • The relations columns – such as the “groups” column in the EcomProducts table, the “shops” column in the “EcomGroups” table, etc. – require a comma separated list of values as input.
Important

If you import products, and ProductId is part of the import data, make sure that the ProductId contains Aa-Zz or 0-9 ONLY, and nothing else. Do NOT use space, comma, dot or any special character.

Automatic table sorting

When a job using the Ecom provider as destination is run, the table imports are automatically executed in accordance with the underlying database schema. This means that the Ecom provider checks the foreign keys of the established table mappings to determine if one table should be executed after another, in order to maintain proper data relations.

To top