It is a category manager’s job to try to maximize profit from selling products within a specific category. This may be looking after a broad category such as ‘confectionery’ or they may focus closely on a more specific category, such as ‘snacking’. A category manager will analyze complex data collected on shopper behavior from a range of different sources, and then translate it into meaningful information. The category manager’s duty is to ensure that their company is providing the market with the products that consumers desire.

Retail Category managers love Excel. It is used for almost everything, and they perform much of the analyzing, lookup, data collection and decision making in Excel. When implementing Dynamics 365 we are often faces with large set of excel spreadsheets that needs to be imported. I have seen users import 8 different excel spreadsheets for importing products. This blog post is about how to simplify the process of keeping retail master data in single excel sheet and easily importing and updating products. For this, Dynamics 365 data management framework is used. One of the problems I often se uses are struggling with, is the issue that the source excel spread sheet is a single spreadsheet, but it needs to be imported into several data entities. For a retailer some of the most common master data entities are:

Data entity	Description of data entity
Products V2	Contains Product number, Product name and dimension groups
Released products V2	Contains most fields on the released product
Item – bar code	Contains the item barcodes used for scanning
Default order settings	Contains information like minimum purchase quantity etc.
External item descriptions for vendors	Vendors item numbers and descriptions
Product category assignments	The connection to the retail category hierarchy.

It is possible to create a single excel spreads sheet that overs all of these entities, and in a single run import or update the retail products.

So how to exactly do this?

Create an Excel spreadsheet with exactly the following.

I recommend creating two sheets. First one is a “read me” sheet, that explains the “template” sheet. Use exactly the column names as described here. This will make the mapping between the columns and the data entity automatic. Here I also use color coding to show what entity each column mainly belongs to.

Field	Example Value	Comment	Tables
ITEMNUMBER	1005157	Product number	Released Products, Products
PRODUCTNUMBER	1005157	Product number	Released Products
PRODUCTNAME	Jalla Coffee 500G	Item name	Released Products, Products
PRODUCTSEARCHNAME	4001392 Jalla Coffee FILTER 500G	Seach name	Released Products, Products
SEARCHNAME	Jalla Coffee FILTER 500G	Seach name	Released Products
PRODUCTDESCRIPTION	Jalla Coffee Original is a useful coffee that can be enjoyed on most occasions. A carefully selected mix of coffee types, mainly from Brazil, guarantees a round and full-bodied coffee with long aftertaste	Full item description	Released Products, Products
PRODUCTSUBTYPE	Product	Should always be “product”	Released Products, Products
PRODUCTTYPE	Item	Item or Service	Released Products, Products
STORAGEDIMENSIONGROUPNAME	SiteWhLoc	Name of the storage dimension group	Released Products, Products
ISPURCHASEPRICEAUTOMATICALLYUPDATED	Yes/No	Should last purchase price be updated automatically	Released Products
ISUNITCOSTAUTOMATICALLYUPDATED	Yes/No	Should cost purchase price be updated automatically	Released Products
PRODUCTGROUPID	WHI	WHI(warehouse controlled) or, SRV(service)	Released Products
INVENTORYUNITSYMBOL	PCS	Inventory unit	Released Products
PURCHASEUNITSYMBOL	PCS	Purchase unit	Released Products
SALESUNITSYMBOL	PCS	Sales unit	Released Products
PURCHASEPRICE	0	Latest purchase price in local currency	Released Products
UNITCOST	0	Latest cost price Sin local currency	Released Products
SALESPRICE	0	Default sales price in local currency	Released Products
NETPRODUCTWEIGHT	0,5	Weight of the product	Released Products
PRIMARYVENDORACCOUNTNUMBER	20086	Primary vendor	Released Products
PURCHASESALESTAXITEMGROUPCODE	Middle	Purchase item tax groups	Released Products
SALESSALESTAXITEMGROUPCODE	Middle	Sales item tax groups	Released Products
BUYERGROUPID	P108	Grouping related to buyergroup	Released Products
TRACKINGDIMENSIONGROUPNAME	None	Tracking dimension	Released Products, Products
BASESALESPRICESOURCE	PurchPrice	Base sales prices on purchase price ?	Released Products
DEFAULTORDERTYPE	Purch	Standard verdier	Released Products
ITEMMODELGROUPID	FIFO	item model group	Released Products
PRODUCTCOVERAGEGROUPID	Min/Max	Coverage group	Released Products
COUNTGROUPID	PER	Gcount group	Released Products
PURCHASEPRICEQUANTITY	1	Purchase price quantity	Released Products
UNITCOSTQUANTITY	1	Cost price quantity	Released Products
DEFAULTLEDGERDIMENSIONDISPLAYVALUE	-D30-320—P108	Financial dimensions(=”-D30-320—“&B34)	Released Products
Product Dimension	P108	Just a helping colum	Help column for DefaultLedgerDimension
ProductCategoryHierarchyName	Retail category	Retail hierarcy name	Product category assignments
ProductCAtegoryName	Coffee	Category node	Product category assignments
VendorProductNumber	4001392	Vendors item number	External item descriptions for vendors
VendorProductDescription	Jalla Coffee FILTER 500G	Vendors item name	External item descriptions for vendors
VendorAccountNumber	20086	Vendor number	External item descriptions for vendors
BARCODESETUPID	EAN13	Barcode type	Item – Bar Code, Released products
BARCODE	7041011050007	Barcode	Item – Bar Code
PRODUCTQUANTITYUNITSYMBOL	PCS	barcode unit	Item – Bar Code
ISDEFAULTSCANNEDBARCODE	Yes	Scanning yes/no	Item – Bar Code
PRODUCTQUANTITY	1	Barcode quantity	Item – Bar Code
PURCHASEUNDERDELIVERYPERCENTAGE	20	Purchase under delivery percentage allowed	Released Products
PURCHASEOVERDELIVERYPERCENTAGE	20	Purchase over delivery percentage allowed	Released Products
MINIMUMPROCUREMENTORDERQUANTITY	x	Minimum purchase quantity	Default Order Settings
MAXIMUMPROCUREMENTORDERQUANTITY	x	Maximum purchase quantity	Default Order Settings
STANDARDPROCUREMENTORDERQUANTITY	x	Standard purchase quantity	Default Order Settings
PROCUREMENTQUANTITYMULTIPLES	x	Multiple purchase quantity	Default Order Settings

The template excel spread sheet columns should contain exactly the columns as listed above:

Then start building the excel spread sheet (this is the time consuming part). This can also be regarded as the “master file” for products. And mass update and mass import of products is done using this file. Remember that you can add more columns and also include calculated fields. Like in this case, the default dimension (used for financial dimension have the formula like =”-D30-320—“&B34 making sure that cell B34 is merged into the financial dimension.

Create the data management import project.

In the data management workspace, create a import project, and use the “+ Add file”, and select the excel file by using the “upload and add”. Then select all the entities and what page in the excel spread sheet that should be imported. – Select file – Select entity name – Select sheet lookup – Then repeat by select entity name and sheet lookup until all date entities needed are selected

After done this correctly you should have an import project with the following entities:

You should also click on the “view map” symbol if there are a warning, and just delete the lines where there are no mapping generated. Like what I have done here to the “Products V2” entity.

The mapping will be done automatically for you, and will only select the fields that is relevant for each data entity.

Your data entity is now ready to be used. I recommend to use the data management workspace, and select the import project and then “run project”

Then for each data entity I upload exactly the same excel spreadsheet :

And then click on the “import”. If there are any errors, then fix them in the excel sheet or make changes to the staging. What we then have accomplished is to have a single excel spreadsheet that the category manager can maintain and work with, and it can uploaded(several times) into the import project. For trade agreement sales and purchase prices I normally recommend creating a separate excel spread sheet

Then the excel loving category managers will be happy, and they can import thousands of products in a very short time

零售类别管家，在＃Dyn365FO中简化您对已发布产品的导入 / Retail category managers, Simplify your import of released products in #Dyn365FO

Create an Excel spreadsheet with exactly the following.

Create the data management import project.

与本文相关的文章

Hi，您需要填写昵称和邮箱！