Automatically cancel (or delete) Sales Order lines for Supplementary Items

In AX 2012 R3, a new field appeared on the Customer form. The field is labelled ‘Automatic cancel’, and is so innocuous you may well have not even noticed it:

AutomaticCancel

The ‘Automatic cancel’ tick box

There is currently no mention of it on the TechNet help page for the customer form. The only clue to its intended purpose comes from the help text for the field, which reads ‘Automatically cancel supplementary item if the item it is attached to is cancelled’.

It so happened that an implementation I was working on expected to use supplementary items on sales orders, and I could use the functionality apparently offered by this tick-box. So I tested it, and found that it didn’t do anything – or at least it didn’t do what I expected it to do. To explain what I expected it to do, I should mention what supplementary items are.

For any item in Dynamics AX, it is possible to define one or more ‘Supplementary items‘. Supplementary items are items that might typically be added to an order when the ‘main’ item is ordered. In fact, it is possible to define Supplementary sales items and Supplementary purchase items for an item. That is to say, you can define a list of items that is typically sold (or purchased) when the main item is sold (or purchased). For example, if your business is in the habit of selling laptop computers, then you might decide to set a laptop bag, an extended life battery and a travel mouse as the supplementary sales items for a laptop item.

Additionally, supplementary item functionality has often been used in buy-one-get-one-free scenarios, since it is possible to define that the supplementary item (which might normally have its own selling price) can be sold for free when added to an order as a supplementary item.

In either scenario, there have always been a couple of problems with the use of supplementary items. One of those is that if you remove the ‘main’ item from the sales order, the supplementary items remain on the order. You have to remember to also remove any supplementary items that were added. If a customer decides not to buy the laptop, will they still want the laptop bag? Probably not. Worse still, if you offer a buy-one-get-one-free deal, and the ‘buy one’ item is removed from the order, there is the chance that the customer will still get the ‘get one free’ item – for free!

So I expected the ‘Automatic cancel’ tick box to plug this gap… or at least I expected it to handle the scenario where the main item is cancelled, by automatically cancelling the related supplementary items. Unfortunately, I found that this did not happen.

So I logged it with Microsoft support. Based on other recent experiences, I was aware that they probably wouldn’t be able to handle it has a bug fix. But I was also aware of a thing called a DCR (design change request?) by which support agents had recently been able to get me changes to the standard product, so I had some hope. Noting that the field actually lived in the MCRCustTable table (and was therefore probably inherited with the Call center functionality), I make some cheeky requests of my own:

  • The functionality should work on all sales orders, regardless of whether the Call center functionality had been enabled
  • The functionality should work when the order line for the ‘main’ item was deleted (not just when it was cancelled) and in this scenario should result in the automatic deletion of the order line for any supplementary items.

And less than two months later, hotfix KB 3072485 was released. And do you know what, it handled both the cancellation and deletion of the ‘main’ item, and it worked even if Call center was switched off. I could not be more pleased. At my direct request, Microsoft support have arranged for the creation of seven hotfixes this year, two of which are DCRs. I am stunned at this level of service from a giant like Microsoft to a little old consultant like me!

But back to the hotfix. What does it do? If you tick the ‘Automatic cancel’ tick box on any customer, when you add a supplementary item to an order for that customer and you either:

Cancel the order line for the main item

or

Delete the order line for the main item

the same will happen to the order line for the supplementary item.

Perfect! Unfortunately, it doesn’t help with the other problem with supplementary items (there is no way to automatically add them, or even to prompt the user so that they know that supplementary items are defined), but you all have your own solution for that already, right?

Its worth noting that the functionality does not also work on purchase orders. And you’ll also want to know that KB 3072485 did not make it into CU9.

‘Nothing happens’ when posting a Purchase order product receipt

I’ve encountered a problem when posting a product receipt on a purchase order that you need to see to believe. For this reason, I’ve recorded a video which shows the problem and the cause.

The problem is that, when posting a purchase order product receipt, nothing happens. This means the product receipt journal is not created, the status of the items on the order doesn’t update, and perhaps most surprising is that you don’t get an error message.

In my example, we see that a missing ‘Purchase expenditure, un-invoiced’ account on the item’s posting profile is responsible.

‘Field Account must be filled in’ on Accounts Receivable parameters

A quick post; one for the search engines more than anything else. If you’re modifying the Accounts receivable parameters in Dynamics AX, you can get the error ‘Field Account must be filled in’.

FieldAccountMustBeFilledIn

Pesky error message

When this happens, any changes you want to make in the parameters form cannot be saved. The cause is quite straight forward; you have configured an active credit card processor at Accounts receivable -> Setup -> Payment -> Payment services. The solution, therefore, can be found on the ‘Credit card’ tab of the Accounts receivable parameters form. Enter an offset account in the ‘Credit card payment posting’ area:

CreditCardPaymentPostingOffsetAccount

Add an ‘Account’ here

Setup for Call Center gets a little bit harder with CU9

I’ve called this post ‘Setup for Call Center gets a little bit harder with CU9’. I could equally have said ‘Call Center gets extra functionality in CU9’. You can spin it either way, depending on whether you want the extra functionality, or not.

I am half-way through writing another post, which starts by describing how simple it is to enable Call center functionality on standard AX sales orders. I’ll steal these words for this post.

The bare minimum setup to enable the relevant Call center functionality is:

If you need help with any of that, I have previously covered this setup. Refer here, and stop when you get to “you’ve done enough to enable Retail functionality on standard AX Sales orders”.

And then I installed CU9, and I’m not able to add lines to sales orders any more. Creating sales order lines produces an infolog which reads ‘Mode of delivery is not valid for Item number…’. Excuse me? What?

ModeOfDeliveryNotValid

Mode of delivery is not valid for Item number

Actually, I shouldn’t have been too surprised. I was actually testing the functionality because a colleague complained AX wasn’t throwing this error. You see, some of us want this behaviour. But what is it all about? Well, there have been some clues on the ‘Mode of delivery’ setup form since AX 2012 R2:

ModeOfDeliverySetupForm

The Mode of Delivery setup form

If you have the ‘Retail Headquarters’ configuration key enabled, there are fast tabs on the Mode of Delivery form for Retail channel, Product and Address. These allow you to restrict when a Mode of Delivery can be selected. It’s possible to say that this mode of delivery can only be used by these stores. And it’s also possible to say that this mode of delivery can only deliver to these countries (or states). Using the ‘Product’ tab, you can also define which products can be used with a specific Mode of Delivery. Until R3 CU9, this ONLY worked with traditional retail functionality (e.g. on the PoS). But CU9 contains hotfix KB 3026775 – ‘When creating a sales order in Call Center, a warning does not appear when a delivery mode and address exclusion combination is entered‘.

As far as I can tell, if you are using call center there is no way to disable the functionality. So we’d better embrace it and find out what we need to do to make it work. And the first step is easy. Add the countries to which you deliver to the mode of delivery:

ModeOfDeliveryCountry

Add countries to the mode of delivery

That was easy! The next step is obvious too. Add your call center to the mode of delivery. Except perhaps you can’t:

ModeOfDeliveryCantAddCallCenter

The call center cannot be selected

If your call center is not available to select, it might because you have been listening to me, and you’ve only done the bear minimum of setup to enable the call center functionality. The dialogue box in the screenshot above gives you a clue. Your call center should be in a Retail Organisational Hierarchy. You’d better refer to this post that I already mentioned (and leave that page open – I have a feeling we’re going to need it again). Once you’ve assigned your call center to the organisational hierarchy, you can add it to the mode of delivery:

ModeOfDeliveryAddCallCenter

Adding the call center to a mode of delivery

The last thing we need to do is add our products to the mode of delivery. If you look at that fast tab, you’ll see it doesn’t mention anything to do with ‘Item number’. You’re going to need a Retail Product Hierarchy, and you’re going to need to add all the items (that you want to sell) to that hierarchy. If you weren’t planning on using a Retail Product Hierarchy, this might be a little annoying. But you don’t have to create a complicated hierarchy. You could just have one level, and assign every item to it. And you never know, you may actually find this functionality useful – do you really want to be able to select ‘Customer collect’ on an order with a delivery address that is two continents away!? Anyway, once you’ve done it, it’s very easy to add ‘All’ items (which are in the product hierarchy) to the mode of delivery:

ModeOfDeliveryAddProduct

You could add All products to the mode of delivery

Now I have to make a confession at this point. I have spent so long playing with Call center to see what setup is required and what isn’t, that I have run out of ‘clean’ R3 environments. My assumption is that I don’t need to create catalogues and assortments and all manner of other retail things – but I don’t actually have access to an environment where I haven’t done that already. Certainly for this example, I haven’t added a Price group to my Call center, and I haven’t added my Call center to a catalogue. I will get my hands on a clean environment at some point, and at that time I will update this post, but until then you’ll just have to let me know.

So, have we now done enough? Not quite. Go to your Call center channel -> Setup -> Modes of delivery. No mode of delivery is listed. How can that be?

CallCenterHasNoModeOfDelivery

The call center has no mode of delivery

There’s a periodic job in the Retail module called ‘Process delivery modes’. We need to run it. And then, it all starts to work. Once again, I can create sales orders which have access to all the great call center functionality.

You might consider setting the ‘Process delivery modes’ job to run in batch with a recurrence. If, for example, you add a new item to the product hierarchy, the job will need to run before the item can be used with your modes of delivery.

So, I hope this is a ‘heads-up’ to anyone using call center to supplement sales order functionality and that is thinking of upgrading to CU9.

Finally, please, if you see this error, will you comment below. I can’t be sure if the environment I am using is a bit of a mess, or if I need to do more setup! I can make it appear by clicking the ‘Recalculate’ button on the ‘Sell’ tab of the sales order.

DataValidationExceptionSaleslineitemcouldnotbefound

DataValidationException: Sales line item could not be found

 

New in R3 CU9 – Display a list of open work on the mobile device

CU9 for Dynamics AX 2012 R3 was released last week. It contains a good number of fixes, more than a dozen of which I really need for AX implementations I am involved in. But it feels to me that, when compared to last year’s CU8, it does not contain so much new functionality. Many of the new features are in the Warehousing module, and although two of them are on my ‘must have’ list, they have been available already as hotfixes in their own right.

One enhancement, however, was available first in CU9, and is the topic of this blog post (it has subsequently been made available via hotfix KB.3062537). It is the ability to create a menu of open work on the mobile device, allowing the operator themselves to choose what to do next. To configure it, head into the ‘Mobile device menu item’ setup form. The eagle-eyed amongst you will notice two new buttons in the action pane (both greyed out) labelled ‘Edit query’ and ‘Field list’:

Two new buttons appear on the menu item setup form

Two new buttons appear on the ‘mobile device menu item’ setup form

If you create a new menu item, and set ‘Mode’ to ‘Indirect’, you’ll find a new ‘Activity code’, called ‘Display open work list’, is available:

WorkListMenuItem

A new Activity code is available

Selecting this activity code causes the two new buttons to become available. Let’s have a look at this on the mobile device, and then we’ll come back and look at how we can configure it. In the screenshot below, you can see seven instances of work are shown, along with a little information about each:

WorkList

The work list on the mobile device

The operator can select some work to do, by clicking on the relevant Work ID. They can also scroll the further down the list (only the first 7 of 14 work items are shown in my screenshot), and they can click on any column heading which is highlighted in grey to re-sort the list.

To configure what is displayed to the operator, we have four options:

1. The operator must already have permission to access any work shown by means of another menu item. This means if a user has access only to menu items which allow ‘Inbound’ type activities (Purchase order receiving and put away, for example) then they will not see any work related to replenishments or sales order picking. You don’t need to do anything particularly to configure this – it just works based on what menu items they already have access to. What’s more, these menu items must have ‘Directed by’ set to ‘User directed’. i.e. they must already be configured to allow the user to select what work to do next. If the operator has access to pick Sales orders, but the menu item is configured to System directed (i.e. it is configured so that AX is telling the user what to do next), they are not going to be able to come into this work list and decide for themselves what Sales order to pick next.

2. As with other menu items, you can select which class work that appears on the list must have (and selecting no class means the list is not filtered by work class):

WorkClasses

Menu item work classes

3. The ‘Field list’ button allows you to define which columns are shown on the screen of the mobile device. Up to eight fields can be shown. The first is always ‘Work ID’, and is mandatory. The other fields that you can select come from WHSWorkTable and WHSLoadTable. WHSWorkTable contains the Work header, and can be seen on the ‘All work’ list page. WHSLoadTable contains the Load header, and can be seen on the ‘All loads’ list page. Any field that you think is useful from the Work or Load can be shown on the list. In addition, two ‘display method’ type fields can be added. These show either the location on the first Pick line of the work header, or the location of the last Put line. Since these two fields are display methods, they cannot be filtered or sorted on. The data actually comes from the work lines, and any intermediate locations (e.g. staging locations) cannot be displayed.

FieldList

Select which fields should be displayed

4. The ‘Edit query’ button has two uses. It can be used to filter the records shown in the list. Any field on the Work and Load headers can be used. It also allows you to define the default sorting order of the list (and if you do nothing, the list will be sorted by Work ID in ascending order).

Two more fields exist on the menu item setup form. The first allows you to define how many work headers should be shown on each ‘page’. If there is more work than can fit onto the page, the operator will be able to click on ‘Previous’ and ‘Next’ buttons to scroll through the list. This field, in combination with the number of columns that you choose to display, allows you to configure the data that is displayed to fit onto the screen of your mobile device, although Microsoft themselves recommend displays which are at least 7″ in size.

The ‘Allow users to filter by work transaction type’ tick box adds a drop-down above the list, which allows operators themselves to filter the list (beyond any filtering you may have already created for them):

FilterWorkList

The work list can be filtered by the operator

When the user chooses some work to do, the mobile device switches to the relevant menu item (that the user already has access to) to complete the work. If the user has more than one menu item which could do the work, they are asked which they would like to use:

ChooseMenuItem

The user may be asked to select which menu item to use

Although not particularly suitable on the traditional hand-held barcode reader type devices, I am sure that users in some warehouses will find this functionality useful: where system directed cannot be used, it eliminates the need for printing Work lists and scanning Work IDs. As is the case throughout the Warehouse management module, so much of this functionality is configurable. In that way, it is limited partly by your imagination. In my example, I showed only and small number of fields on the list. It is possible to allow the operators to see (and perhaps crucially, sort or filter on) any number of fields. For example, the scheduled ship time (from the Load) can be used to ensure orders which are to be delivered first are picked first. The Sales order number can be shown, which, to my knowledge, allows the operator to select work by sales id for the first time.

If you want to read more about it, the TechNet site has been updated here (under ‘Setup a menu item for activities and queries’). And the product team have written about it on the AX SCM blog.

Trade agreement ‘Find next’ on Call center Sales orders

On Sales Price (and Discount) Trade Agreements, AX will look for applicable trade agreement lines in a specific sequence (as described here. Basically, it looks for a “Customer = Table and Item = Table” record first, and “Customer = All, Item = All” last). If ‘Find next’ is ticked on the trade agreement line, AX will keep looking for a trade agreement line with a better price (or discount) for the customer. If ‘Find next’ is not ticked, when AX finds a trade agreement line it stops looking.

If you’ve a price defined for an individual customer and a price for All customers, it is possible to un-tick ‘Find next’ to ensure AX will always use the individual customer price in preference to the All customer price, even if the individual customer price is higher. In this screenshot, trade agreement lines are created for an Item. ‘All’ customers get a nice low price of 10.00 USD. Customer ACBE alone gets a price of 15.00 USD. Notice that ‘Find next’ is not ticked on the trade agreement line.

Trade agreements for 'All' customers and for customer ACBE

Trade agreements for ‘All’ customers and for customer ACBE

Here is a standard Sales order for Customer ACBE for the Item. The price defaults to 15.00:

The price the customer gets on a standard sales order is 15.00

The price the customer gets on a standard sales order is 15.00

I thought I was on pretty strong ground when it came to this behaviour….. and then I enabled Call center functionality and brought Retail Pricing into play. Although AX still refers to the trade agreement sales prices, it seems the behaviour when ‘Find next’ is un-ticked (that I described above) is being ignored: Retail sales orders always find the ‘best’ price that the customer could get – as though ‘Find next’ was ticked on all trade agreement lines. Here, on this Retail sales order, the price defaults to 10.00, even though no changes have been made to the setup of the Trade agreements:

The price the customer gets on a retail sales order is 10.00

The price the customer gets on a retail sales order is 10.00

ACBE is getting the ‘All’ customer price, even though he has his own price which should always apply.

I wanted to investigate using Retail pricing on sales orders, but also had the requirement to honour the ‘Find next’ setting. More in hope than in expectation, I logged the behaviour I’d seen with Microsoft support. I fully expected to be told that this is how the retail pricing engine worked, so when I got back the news that this bug had been fixed I was stunned!

After installing the hotfix, Customer ACBE gets the price 15.00 on the Retail sales order:

The price the customer gets on a retail sales order after the hot fix is installed is 15.00

The price the customer gets on a retail sales order after the hot fix is installed is 15.00

The relevant hotfix (KB 3064046) is a kernel update only. This must mean the retail pricing engine sits outside the AX application. It also means that you’ll have to take this modified behaviour (should you decide to update your kernel), whether you want it or not! After installing KB 3064046, the Kernel version of the becomes 6.3.1000.2594. Therefore, assume that any kernel version equal to or higher than this exhibits the modified behaviour I describe. The hotfix is available now on LCS.

Dynamics AX and Power BI

Update – 8th October 2015. Please find my most recent verdict on using Power BI with Dynamics AX here.

Just before Christmas last year, Microsoft announced significant new features under the ‘Power BI’ name. Functionality called Q&A caught my eye. It allows you to type in a question to create a report (or dashboard) and is, frankly, astonishing. Although this had been around for nearly a year, I think it was the first time that it became available to those without an Office 365 subscription. It wasn’t until March this year that those of us in Europe were able to get their hands on it, and I’ve finally got round to finding out what it can (or can’t) do with Dynamics AX.

So what is this new Power BI? The main part is the PowerBI.com website, which you can use to create, host and share your reports. There is also a tool called Power BI Designer, which you can install locally to create reports. These reports can be uploaded to PowerBI.com for sharing. Power BI is FREE (but please check this link for reasons why you might need to pay for it.)

On the blog of the Microsoft Dynamics AX Solution Archtecture team, Clay Wesener has written a series of five blogs about PowerBI and Dynamics AX. Part 1 is here. When I read them, I was surprised to find they didn’t go down the route I was expecting (and more about that next). I also heard that people were finding it was not possible to connect Power BI to their AX data. ‘How hard can it be?’, I thought. ‘We have a SQL database and some SSAS cubes which are ripe for mining’. And this is where Clay’s blog took a different route.

So, to find out just how hard it could be, I thought I’d install Power BI Designer, point it at my SQL database and we’re away. Well, almost. Whilst Power BI Designer can happily connect to the AX database, and you can upload these files to PowerBI.com, there is currently no support for refreshing the data on reports created this way. And equally disappointing – we cannot use the Q&A functionality on Power BI Designer reports. That’s a shame, not least because I feel like we need a screenshot. So here’s a map I created, showing cities which appear in invoice addresses in the AX demo data:

Cities wth sales in the AX Contoso data set

Cities wth sales in the AX Contoso data set

Note – because its a report created using Power BI Designer, there’s no facility here to build reports using natural language questions. And unfortunately, should I want this map to update next month with the latest sales data, I’d have to upload the Power BI Designer file again.

So lets forget Power BI Designer for now. Remember I said you can create reports directly on PowerBI.com? Lets try connecting PowerBI.com directly to the AX database. Nope, that’s not going to work either. To be fair, the idea is somewhat problematic. PowerBI.com is sat somewhere on the internet. My (fictional) AX database is sat in the basement of the building, and I’ve got lots of firewalls making sure my AX data doesn’t go anywhere near the internet!

But PowerBI.com does have a solution for connecting to an on-premise SQL Server Analysis Services database. That’s great – the AX Role Centers and BI use SSAS. You just need to install a Connector. The connector sits on your network waits for PowerBI.com to ask it for data from your cubes. (And you might need to install Azure Active Directory Sync, but only if your users don’t login to PowerBI.com with their work email address).

That’s the answer then! It’s not all of my AX data, but it’s a lot of the juicy stuff, and the cubes are already built for neat reporting. Unfortunately, not! The Connector currently only supports SSAS running in something called Tabular mode. You don’t need to understand what that is; you just need to know that the SSAS database created by AX is ‘multidimensional’, which is different. Oh, and the Connector also does not support Q&A functionality.

So back to the blog written by the AX Solution Architecture team. What does that say to do? The first solution only works for Power BI running on Office 365. It uses something called an OData feed, which allows AX to publish data via a URL. You put this data into Excel, and save this workbook to Office 365. If you install something called Microsoft Data Management Gateway, Office 365 is able to refresh the data from Dynamics AX automatically. I don’t have an Office 365 account – so I can’t use that. (Although if I did, I would be able to use Q&A on my AX data).

One final solution offered by the Solution Architecture team does not require Office 365, and goes back to the SSAS Connector I mentioned two or three paragraphs above. I already said it can’t be used with the AX Cubes, and that is still true. A solution which involves creating a Tabular database and copying data into it from the multidimensional database is proposed. Whilst not impossible, it’s more than I’m prepared to do for this blog post!

So we’ve got to the end, and I still don’t have my AX data in PowerBI.com, and I’m miles away from being able to do Q&A reports.

Should we just forget it then? Well, no. Power BI is amazing and powerful and does much more than just connect to AX data. And it is currently being updated monthly with new features. For example, this month, Google Analytics was added as a data source to Power BI Designer. Yes – include your website stats alongside (or even within) your reports!

Microsoft have already said that work on connecting PowerBI.com to on-premise SQL data has started. They have said that refreshing of reports created in Power BI Designer is planned. Adding support for on-premise, multi-dimensional SSAS databases is also planned. So my advice for now is to hang on and wait. But in the mean time, use the data sources that Power BI does support to get the hang of it.