MSDyn365FO Retail

Retail category managers, Simplify your import of released products in #Dyn365FO

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

 

 

 

 

 

 

 

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

3 comments on “Retail category managers, Simplify your import of released products in #Dyn365FO

  1. Pingback: D365Tour Press Review – December 2018 - Microsoft Dynamics AX Community

  2. Giridhar Dindukurthy

    Nice Post

    Like

Leave a Reply to dynamicssoftwarepostmaster Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: