Retail assortments and planned orders extensions

Microsoft have created an excellent description of this in the Assortment management doc-page. Retail assortments are essential to define what products that should be available across retail channels. Assigning a product to an assortment, will assign the product to the stores that have the assortment. This makes it possible to sell the product in the store.

But there is something missing, and that is to use assortments for replenishment and procurement. Retailers want the master planning to only suggest procurement and transfers on products that belongs to the stores assortments. You can control requirement parameters per warehouse, but there is no standard way to use the assortment to control the generation of planned orders.

This blog post is to show how to make a very small extension that allows you to make sure that only products that belongs to a stores assortment will generate planned orders. The solution I will make use of is to look into how the product lifecycle state functionality is working, and extend this with an assortment planning parameter. I have called this parameter “Is lifecycle state active for assortment procurement

What it will do, is to validate if a product is in the assortment of the store, and if it is, then the product will be requirement calculated and will generate planned orders. If the product is not in the assortment of the store, that no planned orders will be generated.

To make this happen, I needed to create 4 extensions. The three first is adding a new field on the product lifecycle form.  For an experienced developer this is easy to create, and no need to spend time on in this this blog-post.

The real fun is how to manage and control the planned order generation. This happens in in an extension to the ReqSetupDim class.  Here there is a lookup to the assortment on the product, and checks if the store have this assortment.  If not, then the masterplanning will not generate any planned orders. I therefore create an extention class and use the new method wrapping/CoC feature to add some additional code.

///
<summary>
/// Contains extension methods for the ReqSetupDim class.
/// </summary>

[ExtensionOf(classStr(ReqSetupDim))]
final class ReqSetupDim_extension
{

    ///
<summary>
    /// Validates if a product should be assortment planned
    /// </summary>

    /// The parm of the ReqSetupDim class.
    /// false if the product is not assortment planned; otherwise, return default value.
    public boolean  mustReqBeCreated(InventDim _inventDimComplete)
    {
        Boolean ret = next mustReqBeCreated(_inventDimComplete);

        if (ret)
        {
            if (inventdim.InventLocationId)
            {
                InventTable                 inventtable;
                EcoResProductLifecycleState ecoResProductLifecycleState;

                //Fetching fields from  inventtable
                select firstonly ProductLifecycleStateId, Product from  inventtable where inventtable.ItemId == this.itemId();

                //validating if the product is active for planning and that also assortment planning is enabled.
                select firstonly RecId from ecoResProductLifecycleState
                        where   ecoResProductLifecycleState.IsActiveForAssortmentPlanning == true
                            &amp;&amp;  ecoResProductLifecycleState.IsActiveForPlanning == true
                            &amp;&amp;  ecoResProductLifecycleState.StateId == inventtable.ProductLifecycleStateId;

                if(ecoResProductLifecycleState)
                {
                    RetailStoreTable                    store;
                    EcoResProduct                       product;
                    RetailAssortmentLookup              assortmentLookupInclude;
                    RetailAssortmentLookup              assortmentLookupExclude;

                    RetailAssortmentLookupChannelGroup  assortmentLookupChannelGroupInclude;
                    RetailAssortmentLookupChannelGroup  assortmentLookupChannelGroupExclude;

                    //Finding OMOperatingUnitID from the inventlocationId
                    while select firstonly OMOperatingUnitID from store
                        where store.inventlocation == inventdim.InventLocationId
                    {
                        //Check if the product is in the assortment of the store in question
                        select RecId from product
                            where product.RecId == inventtable.product
                        exists join assortmentLookupInclude
                            where   assortmentLookupInclude.ProductId == product.RecId
                                &amp;&amp;  assortmentLookupInclude.lineType == RetailAssortmentExcludeIncludeType::Include
                        exists join assortmentLookupChannelGroupInclude
                                where   assortmentLookupChannelGroupInclude.OMOperatingUnitId == store.OMOperatingUnitID
                                    &amp;&amp;  assortmentLookupChannelGroupInclude.AssortmentId == assortmentLookupInclude.AssortmentId
                        notexists join assortmentLookupExclude
                            where   assortmentLookupExclude.ProductId == product.RecId
                                &amp;&amp;  assortmentLookupExclude.lineType == RetailAssortmentExcludeIncludeType::Exclude
                        exists join assortmentLookupChannelGroupExclude
                            where   assortmentLookupChannelGroupExclude.OMOperatingUnitId == store.OMOperatingUnitID
                                &amp;&amp;  assortmentLookupChannelGroupExclude.AssortmentId == assortmentLookupExclude.AssortmentId;

                        if (!product)
                        {
                            ret = false; //The product does NOT belong to the stores assortment, and should not be planned
                        }
                    }
                }
            }
        }
        return ret;
    }
}

I also have code to restrict creation of manual purchase orders, and where simular code can be used, but let’s hope that Microsoft can further extend standard Dynamics 365 with assortment based procurement planning.

Copy with pride, and let’s hope next year will give us 365 more opertunities.

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

 

 

 

 

 

 

 

D365F&O Retail: Combining important retail statement batch jobs

The Retail statement functionality in D365F&O is the process that puts everything together and makes sure transactions from POS flows into D365F&O HQ. Microsoft have made some improvements to the statement functionality that you can read here : https://docs.microsoft.com/en-us/dynamics365/unified-operations/retail/statement-posting-eod. I wanted to show how to combine these 3 processes into a single batch job.

The following drawing is an oversimplification of the process, but here the process starts with the opening of a shift in the POS (with start amount declaration), and then start selling in POS. Each time the job P-0001 upload channel transaction is executed, the transactions are fetched from the channel databases, and imported to D365F&O. If you are using shift-based statements, a statement will be calculated when the shift is closed. Using shift-based closing can be tricky, but I highly recommend doing this! After the statement is calculated and there are no issues, the statement will be posted, and an invoiced sales order is created. Then you have all your inventory and financial transactions in place.

 

What I often do see, is that customers are using 3 separate batch jobs for this. The results in the user experience that the retail statement form contains many calculated statements waiting for statement posting. Some customers say they only want to see statements where there are issues (like cash differences after shift is closed).

By combining the batch jobs into a sequenced batch job, then the calculated statements will be posted right again, instead of waiting until the post statement batch job is executed. Here is how to set this up:

1. Manually create a new “blank” batch job

 

2. Click on “View Tasks”.

3. Add the following 4 classes:

RetailCDXScheduleRunner – Upload channel transaction (also called P-job)

RetailTransactionSalesTransMark_Multi – Post inventory

RetailEodStatementCalculateBatchScheduler– Calculate statement

RetailEodStatementPostBatchScheduler – Post statement

Here I choose to include upload of transactions, post inventory, calculate statement and post statement into a single batch-job.

Also remember to ignore task failures.

And remember to click on the “parameters” to set the parameters on each task, like what organization notes that should be included.

On each batch task I also add conditions, so that the previous step needs to be completed before the batch-job starts on the next.

Then I have 1 single batch job, and when executing it spawns subsequent tasks nicely.

The benefit of this is that when you are opening the statements workspace you mostly see statements where there are cash differences, or where the issues on master data.

Take case and post your retail statements.

 

 

 

Report your bugs, free-riders!

Microsoft Dynamics 365 is the fastest innovation and most agile business software in the world. A very feature rich solution with a packed very fast moving roadmap. We see new possibilities and features coming monthly in platform update, fall/spring releases. if you look at the entire platform-stack including windows, office, and platform (power* apps) new features being made available on a daily basis. Being first and fast have changed and challenged the Dynamics 365 ecosystem. Mostly for the good.

But we have to recognize that it is people (and highly productive) behind this innovation tsunami. In such an environment there are thousands of elements that must to fit together. If you look towards the number of combinations on how you can use and setup Dynamics 365, I would assume that this is millions of combinations in the core product. And when adding office and power* apps, combinations just increases exponentially.

People are people, and there is a limitation to the numbers of combinations that can be tested, both from a manual and automated testing scenario. This leads to scenarios that there is no capacity to test everything before the product is released. It is not possible to test all of the millions of combinations, and I know that even Microsoft do not have unlimited people and resources to cover every test scenario.

This evidently results in issues and bugs that will be found when implementing Dynamics 365, and these needs to be reported to Microsoft support so that the fixes becomes part of the future solution.

Searching, testing, reporting a solution takes time and do cost money! Each time I find a bug, I report this to Microsoft so that all of the community can benefit of a fix. But as some have recognized that reporting issues/bug is requiring effort and resources. You report the bug, analyze the issue, report the issue, Microsoft provides hotfixes, the hotfix needs to be validated and testing and then deployed to the environment. This takes time, but is necessary!

With this blog post I urge both partners and customers to report your findings to Microsoft, so that all the rest of us can benefit that we are an ecosystem together. As I hope most of you know, we are quickly moving towards Dynamics 10, that is often referred to as the “ever-green” solution. This means that there are ONE version, that all customers are using, and that follows the Microsoft roadmap. When one customer reports an issue, and it is fixed, then all benefit from this.

Then there is the issue with the “free-riders”. These are the people that recognize the issue, find workarounds and DON’T take the investment in time and resources of reporting the issue. They know and see the issue, but choose to live with it or ignore it. Then in many cases, Microsoft if not even aware of any issue, and the issues just continues to be present in future releases. The best way is to report what you see to Microsoft support or to Microsoft ideas. Then Microsoft can take action on it, because they know of it.

So, I urge my fellow community friends to not be a Free-Rider, but report your issues. This will ensure that we all can share the resource/time burden among us, and we also improve and strengthen Dynamics 365, that we all will benefit from.

PS! Dynamics 365 is the BEST business application in the world!

Vote on Dynamics 365 ideas

Do you know that you can influence the direction of Dynamics 365? But you may be unsure as to whether it really will make a difference. Microsoft have a site where the community can add ideas and vote on them. Go to https://experience.dynamics.com/ideas/ and create your ideas. If the idea is valid and they get enough votes, Microsoft will act and include them in their product backlog. But equally important is the ability to vote on other’s ideas.

  • Voting is the most important way to make the community voice heard on the issues that concerns the roadmap for Dynamics 365.
  • Voting gives you an opportunity to be part of the priority that affects Dynamics 365.
  • If YOU don’t Vote Others will make the decisions for YOU!

As we speak, there are 1673 ideas for Microsoft Dynamics 365 for Finance and Operations and 212 ideas for Microsoft Dynamics 365 for Retail. Microsoft employees are some of most actives to add ideas to their site.

The ideas portal allows you to see as the ideas more from an idea to being part of the product:

An important unofficial note is that for an idea to be moved from “New” to “Under Review” it requires at least 10 votes. Also discussions is possible on the ideas, and to add additional substance to the requirements.

You can also keep track of your own ideas and votes you have submitted.

If I have a few minutes of spare time, I like to go in and look at the new ideas submitted and read them. When there are ideas I like, I vote on them.

The more we use this channel to give ideas and feedback, the more important it will be. So please go in and vote at https://experience.dynamics.com/ideas/

(And if you find some of mine, please give it a vote )

MSDYN365FO: Automate repetitive tasks – the easy way

Here the other day, I got the task of posting a few thousand Retail Kit orders / BOM-Journals because they failed at the first time. I started, and managed to manually post 50 journals before my fingers got cramps and I started to feel dizzy. I could not multiselect the journals and post them, so I had to manually click “post” on each journal.

I surely sent a SR to Microsoft explaining that this should be easier in standard, and that SR is in process. But it will probably end up as a “As-Designed” state, or “post it to ideas.microsoft.com”.

But there is an easier low-tech way to solving this. Just install a Mouse-ghost app, and it will repeat the task for you. So I used the app “Mouse Recorder Premium” to post all the 1300 journals, and it went smoothly. Just record the clicks and then repeat for a 1000 times.

To make sure I did not “lock” my PC while this was performing, I started the task in a Hyper-V VM, and then it can run in the background.

That’s today’s small trick to get rid of repetitive tasks

Measure sales per Retail Category in Power BI

Drill down on sales per category, employee, and department is key essentials for Retailers. Doing this gives a more specific view of what’s generating sales and what isn’t. Having insights into top categories or departments might help make decisions about purchasing and marketing. A good point of sale comes with reporting and analytics, so you can quickly get the data you need, whenever you need it — without manual calculations.

Power BI is a must have for all retailers, and this blogpost is about creating a retail category hierarchy in power BI.

If you have worked with Retail Categories, you know that there exists a “parent-child” relationship between the categories as illustrated from the following data in the Contoso demodata set.

In power BI it is possible to also create such hierarchies, but it requires some minor changes to reflect this. My inspiration came from Power BI Tutorial: Flatten Parent Child Hierarchy. I will not go through how I build a retail power BI analysis, but I can share that I use ODATA entities, and here is the entities I’m using:

More information on the data model is available in DOCS her.

The “trick” is to create a new column named “Path“, and a column named CategoryL[X] for each level in the hierarchy, that for the RetailProductHierarchyCategories looks like this:

Here are the column formulas

Path = PATH(RetailProductHierarchyCategories[CategoryName];RetailProductHierarchyCategories[ParentCategoryName])

CategoryL2 = PATHITEM(RetailProductHierarchyCategories[Path];2)

CategoryL3 = PATHITEM(RetailProductHierarchyCategories[Path];3)

CategoryL4 = PATHITEM(RetailProductHierarchyCategories[Path];4)

CategoryL5 = PATHITEM(RetailProductHierarchyCategories[Path];5)

…etc

Then I create a new hierarchy column for, where I specify

And I use the Hierarchy Slicer that is available in the power BI marketplace.

In power BI I then get a Retail Category slicer, and can filter and measure sales per category in power BI

Microsoft are in process of aligning ourselves with future of Power BI and create the new version of Retail Channel Performance with New Common Data Service for Analytics capability coming to Power BI https://powerbi.microsoft.com/en-us/cds-analytics/

Keep on rocking #MSDYN365FO!