Ecom Provider
The Ecom Provider is a really cool piece of software.
It abstracts 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 provider
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 (Figure 2.1).
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 provider
When used as a destination provider (i.e. to import data into Dynamicweb) you have access to more extensive customization options (Figure 3.1).
Your options are identical to the options you have when using the Dynamicweb Provider:
These options apply to all destination tables:
- You can limit the import to update only existing records. This will skip all rows in the source for which the key does not match a key in a row which already exists in the destination table.
- The Remove missing rows after import setting causes all rows in the tables affected by the activity to be deactivated if they are not present in the imported dataset.
- You can also use the Delete incoming rows setting, in which case the rows with a key matching a key in the dataset are deactivated – this means that you only need to import key data, everything else is ignored.
These options are only relevant when adding data to EcomProducts and EcomGroups:
- Checking Deactivate missing products will cause all products in the existing database, which are not present in the imported dataset, to be deactivated. This setting overrides the ‘Remove missing rows after import’ setting.
Now, if you activate the Remove missing rows after import or Deactivate missing products settings, you can limit their effect to a particular context by using the shop and language dropdowns. This means that you can update select shops or language contexts separately.
If you want to update two languages contexts at the same time, check the delete products/groups for languages included in input checkbox. The effect is identical to selecting two languages in the language dropdown (which you can’t).
Finally, the Dynamicweb Provider supports updating your indexes every time an activity has been run:
- Use the Product index update dropdown to update the old index at runtime
- Use the update index repositories selector to select one or more indexes to rebuild once an activity is run
Which tables & columns?
The Ecom Provider lets you access the following tables:
- EcomProducts
- EcomGroups
- EcomManufacturers
- EcomProductsRelated
- EcomVarianGroups
- 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) |
ProductStockGroupIDb |
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 groups in the "Groups" field. The first integer in the list indicates the sort order of the first group in the “Groups” list described above, the second indicates the sort order of 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 |
This field should contain a list of ShopIDs or ShopNames, in which the current group should be updated/created. This should be in the following format: "Shop1","Shop2","Shop3". If the list contains only one shop, the value should still be enclosed in quotation marks. If one ore more values in the list are not found in the EcomShops table, new shops are created, named for these values. |
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 values 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 thie 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. |
On IDs and automatic generation of column content
As can be seen in the above column descriptions, id and language columns are added automatically, if they are missing.
Products are matched on ID, product number and name, in that order. This means that if ID and product number is missing, but product name is available, the product will be matched on its name.
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.