Earlier in the year, I took a look at Power BI, and tried to find out what it could do with AX data. My ambition was to use Power BI’s natural language search to build reports on AX data. Natural language search (called Q&A in Power BI) allows you to create reports by typing english sentences – such as ‘Sales by Month’.
In the end it didn’t go quite as well as I’d hoped. I couldn’t get the PowerBI.com website to look at my on premise SQL database, and I couldn’t get it to look at the standard AX Cubes. But the post did end on an optimistic note – support for the functionality I was looking for was either in development or being planned.
The link above describes how to install the Power BI Personal Gateway, but the first thing I notice is that the Personal Gateway is only available if you have a Power BI Pro subscription. This cost £9.99/month, and will be required for whoever create the reports, and anyone who views them. Its not loads of money, but I don’t particularly want to spend anything for this exercise. Luckily, a free 60-day trial of Power BI Pro is available, so I’ll go for that. To download the Personal Gateway, I login to PowerBI.com and click the Downloads icon in the top right:
Download the Power BI Personal Gateway
There are some limitations about where you can install the Personal Gateway. It must be on a 64bit Windows operating system, and you’ll probably want to install it on a device which is left powered on (at least, in real use you will. For this test, I installed it into the CU9 Demo VM). Whilst running the install, its worth being signed in as a user with Windows Administrator permissions. If you do this, the Personal Gateway will install as a Windows Service, and will run even if no-one is signed onto the computer.
The install is an easy ‘Next’, ‘Next’, ‘Next’, and when its finished you’re asked to sign-in to PowerBI.com. Use the same login that you used when you downloaded the Personal Gateway, and if you get it right you’ll be asked to provide the windows credentials that the Personal Gateway will use:
Provide the details of a windows (or domain) account
When it’s complete, I’m asked to sign-in to PowerBI.com to configure my datasources. We can’t simply go to PowerBI.com and ask to connect to our on premise database. First, we have to create a report which uses that database, and upload it to PowerBI.com. I’ll use Power BI Desktop for this. You can download it from the same place that you downloaded the Personal Gateway. In Power BI Desktop, SQL Server is listed as one of the data sources:
The SQL Server data source
I enter the details which allow Power BI Designer to find my SQL database:
Enter the location of the SQL database
At the next screen, I have to say if I want to use Windows login or SQL login to access the database, and then I’m presented with all the tables (and views) in my AX database. Since I know the views CustTableCube and CustInvoiceTransExpanded are used by the AX cubes, and provides a nice set of data for reporting, I’ve used those to create this report which shows Invoice value by Zip Code in the USMF company:
A map showing sales by zip code
When I’m done, I publish the report to PowerBI.com. And when I head to PowerBI.com, I see I’ve a new Dataset. If I click the three dots next to the Dataset, I’m able to Refresh it, or Schedule a refresh:
Refresh a dataset
When I attempt a Refresh, I am informed that I need Power BI Pro, and asked if I want to create a trial:
Power BI Pro is required
And the first time I refresh, I have to confirm that I want to use Windows authentication:
Confirm authentication
And after that, my data can refresh. I also have the option to schedule a refresh (up to eight times per day), and as long as my Personal Gateway is running, the data will refresh for me. Result!
But can I use natural language to create a report? Sure I can:
A report created by Power BI Q&A
The report above was created by typing ‘Show Value by Customer in a column chart’. I had to add ‘in a column chart’, because it wanted to use a horizontal bar chart, and I didn’t like it. All they need to do now is integrate Cortana, and I’ll be in seventh heaven:
“Cortana, show me contribution by customer group”
And that is not a dream – work has already started. You can even see it demonstrated by Joe Belfiore in the Ignite 2015 Keynote (skip forward to 00:45:30).
This is all very nice, but I live in the AX world. Why should I be bothered? I’ll leave you with one more thought. If you look hard enough, you’ll see news is also coming out about tighter integration between Power BI and the yet-to-be-release AX 7, and that’s enough to keep me interested.
Recently, Microsoft released an updated Demonstration VM for Dynamics AX 2012 R3 CU9. You can find it here on PartnerSource, and here on CustomerSource. For the last VM (CU8), I converted both the Virtual Hard Disk files (VHDs) and Virtual Machine Generation to the latest version supported by Hyper-V. I also made some other tweaks which might help with performance. Doing it again now for the CU9 VM, I thought I’d write down the steps. I’ve put them here for everyone to read, but this is a really long post, and gets quite technical (in a non-AX way) in places. If this doesn’t sound like something you want to do, I’d stop reading now. It might be that I’m the only person who finds this interesting or useful. If that’s the case, at least I’ve got it written down for next time!
The download for the Demo VM is spread over 38 files (each 1GB in size), so go ahead and download those, and come back when you’re finished.
Downloading the VM using File Transfer Manager
Once the downloads are complete, you need to make sure all the files you’ve downloaded are in the same folder, and run the executable:
Run the executable file
Although it opens an InstallShield Wizard, all it really does is unpack one of the 38 files you downloaded (and display a licence agreement, which I assume is the real reason for this step!). It offers to ‘Install’ it into C:\Program Files. I would change it to the location which you downloaded the 38 files to. And once its done, you’ll have two executables in your folder. Now, run the one with ‘part001’ in the name:
Run the second executable to uncompress the files
It will trigger the unpacking of the 38 files, one by one. You can set the same folder you’ve used before as the destination for this step. Be aware that whereever you do it, you need nearly 110GB of free space. It will take a long time to complete – almost as long as it took me to download the 38 files in the first place.
When it’s done, you’ll have a word document, and a folder containing the VHDs and configuration of the Virtual Machine.
The unpacked files
The VHDs are the ‘virtual hard disk’ files used by the VM. They are the VM’s C: and E: drives. C: contains Windows and the applications. E: contains the SQL database files.
At this point, you can delete the file you downloaded called ‘AX2012R3CU9DemoVMA.exe’. You’ll not need it again. You can also delete all of the files with the word ‘part’ in the name. Or, if you think you’ll need them again (maybe you’ll kill this instance of the VM one day, and want to start again), I normally copy them off to an external drive. Either way, we don’t need them now.
Before we boot it up, we’re going to convert the VHD files to the VHDX format. After we’ve booted it up (and made an important change within the VM) we’re going to convert the VM to Generation 2. If you’re hoping to squeeze better performance from your VM because of this, there is a crumb of hope for you. The VHDX format includes support for something called ‘4K logical sectors’. This is something that is only supported in the latest releases of Windows, and offers the potential for a performance increase.
I’m going to use a command line tool to convert the VHDs, and to make it easier, I’m going to move the whole of the AX2012R3A folder to the root of a drive, in this case my C: drive:
The files are moved to C:\
The tool we’ll use is called Convert-VHD, and you need to open PowerShell to run it:
PowerShell from the Start Menu
The command I will use is:
Convert-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhd" -DestinationPath "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhdx"
If you did not put your ‘AX2012R3A’ folder into the root of the C: drive, you’ll need to modify this. This step will require around 115GB free for the new VHDX files that get created. I have room for them on my C: drive, so I created them in the same place as my VHD files. If you don’t have room, modify the DestinationPath to create them on a different drive.
Put the command into the PowerShell window (hint – Paste is available by clicking the blue PowerShell icon at the top left of the window) and hit Enter:
Paste the command into the PowerShell window
The conversion will begin:
Converting the VHD
There is another command I will run, to set the sectors on the disk to the magical 4K size. The command is below. Just put it into the PowerShell window after conversion is complete:
Set-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhdx" -PhysicalSectorSizeBytes 4096
Then repeat for the second VHD. The commands are:
Convert-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhd" -DestinationPath "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhdx"
and
Set-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhdx" -PhysicalSectorSizeBytes 4096
You can now delete the old VHD files, but leave the VHDX files alone!
At this point, I’m going to move the AX2012R3A folder to location I want it to live when I’m using the VM. I have a folder in my Documents called ‘My Virtual Machines’ and I’m going to put it there:
I also don’t like the name AX2012R3A, since no doubt the CU10 Demo VM will have the same name, and I won’t know which is which. So I rename it AX2012R3CU9. Finally, I’m not going to use the VM configuration provided by Microsoft. It is the XML file that is in the ‘Virtual Machines’ folder, and I’ll delete it that file. The configuration already won’t work, because it is looking for VHD files (and we don’t have any). It also wants a network card called ‘AX2012R3’, and that isn’t what mine is called.
Instead, we’ll create a new configuration now. But first, we need an Internal network card to give to the VM. Open Hyper-V Manager, and then ‘Virtual Switch Manager’. (If Hyper-V Manager is not installed on your PC, you can add it by ‘Turn Windows features on or off’). If you already have an ‘Internal’ switch, that’s fine. If not, hit ‘Create Virtual Switch’. Call it ‘Internal’, and set the type to ‘Internal network’ then click Apply and OK.
Create a new Internal switch
Now we’re ready to create the VM. Click ‘Action’ -> ‘New’ -> Virtual Machine. Run through the Wizard. I’m going to call mine ‘AX2012R3CU9’, and I specify I want to save it in my ‘My Virtual Machines’ folder that I created:
Specify the location for your VM
On the next screen, select ‘Generation 1’. This is very important. You might think we can just go ahead and create a Generation 2 VM now. We can’t. The boot disk is not configured for Generation 2 and it won’t boot. On the next screen, give it as much RAM as you can afford. It wants 24GB (24,576 MB). I only have 16GB in my laptop, so I can’t give it that. I give it 12GB (12,288 MB). At ‘Configure Networking’, give it the Internal switch that you have. At ‘Connect Virtual Hard Disk’, choose ‘Use an existing..’ and point it to the VHDX for the OS:
Attach the hard disk for the operating system
Then we’re done. Well, not quite. But we’re done with the Wizard. But we still need to add the second VHDX file. Select your new VM, and choose ‘Settings’. On ‘IDE Controller 1’, there is a DVD Drive. I don’t need that, so I’ll Remove it and hit Apply:
Remove the DVD Drive
On IDE Controller 1, I then add a Hard Drive. I point it at my Data VHDX, and click Apply:
Add the Data Hard Disk
When I’ve added the second Hard Disk, I’m ready to boot up the VM. Close the ‘Settings’ window, and Start the VM.
Once it has booted (and after you’ve logged in), I suggest going to Control Panel -> Network and Internet -> Network Connections, and re-naming the connection that exists to ‘Internal’. Later, I add an ‘External’ card, and knowing which is which might be useful one day. Whilst we’re in here, check the IP address that the Internal adapter has. If it begins with 169., it’s not got the right address. There’s a utility in the root of C: drive on the VM which will fix that. Browse to the utility (it’s called setnetworkip), right-click on it and choose ‘Run with PowerShell’. The utility will look for network adapters with an address in the 169. range and assign the correct address (in the 10.20.12.x range) to it. The network adapter should remember the address it’s been assigned, so you shouldn’t need to run this utility again.
Now at this point, we start the conversion of the VM to Generation 2. Generation 2 VMs are only supported by Windows 8.1 (or Windows Server 2012 R2, and I assume Windows 10). If you don’t have Hyper-V running on those operating systems, don’t do the conversion to Generation 2 part of this blog post.
We’re going to use a utility that can be found here, on Part 10 of a blog post written by a Microsoft program manager on the Hyper-V team. On Part 8 of that blog, he describes that we have to disable something called the ‘Recovery environment’ on the Demo VM. You’re already logged into the Demo VM. Open Command Prompt as an Administrator:
Open the command prompt as an Administrator
Enter this command to disable the recovery environment:
reagentc /disable
Hopefully I’ll remember to enable that again later, after we’re done. Now, we’re ready to start the conversion, so shutdown the Demo VM. If you do it right, Hyper-V will report the ‘State’ of the VM as ‘Off’. Once that is done, close Hyper-V Manager.
Now you need to download a utility called ‘Convert-VMGeneration.ps1’. You can get it from Part 10 of that blog. I save it to the root of C: drive.
The utility will copy the OS VHDX into a VHDX which is formatted for Generation 2. To do this, it needs to create an image of the Generation 1 VHDX file. That means at one time it needs three times the space of the OS VHDX. I don’t have enough space for that on my C: drive, so I use a parameter (the ‘WIM’ parameter) to tell it to create the image on my D: drive. I have space on my D: drive, and when the utility is finished with the image it will delete it. So my command to launch the utility looks like this:
I’ve told it the name of the VM I wish to convert, and I’ve told it the ‘Path’ to put my new VM in. The ‘WIM’ parameter tells it to temporarily create a file on my D: drive, and the final parameter is the name of my new VM (it has an underscore in it to differentiate it from the old VM).
Again, we run the utility in PowerShell, which you open as an Administrator. Use this command to move to the root of C: drive (which is where I saved the utility):
cd /
Then paste in the command:
The utility is ready to be launched
When it starts, there is a lot going on very quickly. You might notice Windows Explorer opens, with some odd drive letters. The utility has mapped the Demo VMs drives – it’s nothing to be alarmed about. After that though, it stops, waiting for your input. It is telling you that it is about to erase the data on a disk, and wants you to check its got the right one. It has created a new disk for your Generation 2 OS VHDX and wants you to check that this is the disk it is erasing. In my screenshot, it says it will erase Disk 3, and that I should open Disk Management to check what Disk 3 is:
Check before you let it erase a disk
You’re on your own at this point. If it has got it wrong and you lose something important, I can’t help you. If you’re not comfortable, answer ‘N’ and skip this part of the blog. I checked in Disk Management and I can see that all my important data is on Disk 0 and Disk 1, so I answer ‘Y’.
After some time (I think more than an hour), the utility reports that it is finished:
The creation of the Generation 2 VM is complete
The new VM appears alongside the original in Hyper-V Manager. I have to do a little tidying. I delete the original VM. The utility has only converted the boot disk. The E: drive is attached to the new VM, but in its old location. So I remove the second disk from the VM, move the Data VHDX file to the location I want, and then add the second disk back to the VM. I also give the VM 4 processors, instead of 1.
When the VM has booted, remember to open the command prompt as an Administrator and re-enabled the Recovery Environment:
reagentc /enable
If you were skipping the ‘Convert to Generation 2’ part, this is where you pick up again, because unfortunately, we’re not done there (although you should find AX works fine, and you can use the VM). The VM Profile Manager makes a welcome return in the CU9 Demo VM, and by default it is set to give you the full demo experience. Personally, I don’t need any of the Retail POS stuff, so I open the VM Profile Manager, and on the ‘Settings’ tab I set the profile to ‘Minimum’.
Shut down the VM, so that we can check the VM Profile Manager loads a smaller set of applications when we re-boot. But whilst it’s shut down, I create a second switch in ‘Virtual Switch Manager’. This time, it’s an external one, and will allow the VM to communicate to the internet. When you create the switch, select whichever network adapter you want it to use to access the internet. I normally choose my wireless adapter, since it is much more likely that I’ll have a wireless connection than a cabled one. Add this new ‘External’ adapter to the VM, and power it up.
This time when I boot, I can see a whole bunch of windows services (e.g. ten with Retail Commerce Data Exchange in the name) which haven’t started. Good! Go to Control Panel -> Network and Internet -> Network Connections and rename the new network connection ‘External’.
I think we’re nearly done now. I do one more thing, which may help with performance. There are a couple of SQL Trace Flags I like to turn on. They are T4136 & T4199. If you want to know why I turn them on, look here. To enable them, open SQL Server 2014 Configuration Manager. On the left, click on ‘SQL Server Services’. On the right, find ‘SQL Server (MSSQLSERVER)’. Right-click on it and choose ‘Properties’. Head to the ‘Startup Parameters’ tab. Use the ‘Add’ button to add each flag in turn, using the exact format shown in the screenshot (a dash in front, and no spaces):
Add the SQL Trace Flags
They’ll take effect the next time you reboot the VM.
So that’s pretty much it. There are other things I do, which I won’t write about here. You might put the latest SQL service pack and CU on (SQL Server 2014 RTM is on the VM). You might install the latest windows updates. I set the Time Zone and other language settings in Windows to United Kingdom. I turn off a lot of stuff in the AX Licence Configuration that I will never use (like some Russian functionality, US Payroll stuff, the Public sector functionality) or functionality that barges its way onto many forms (e.g. Catch weight). You might also want to think about disabling WMSII (since it’s not supported with Warehouse management).
Well, that was far too long a post. Its my notes for the next time I do this really, but if it helps one or two others then that’s even better.
This post is nothing to do with Dynamics AX, but I thought I record how my upgrade to Windows 10 went. I said to myself that I wouldn’t install it straight away, since I’d prefer others to go through any early adopter problems. But impatience got the better of me!
On the 29th July, my PC started downloading the update (nearly 3GB):
Windows 10 is downloaded
When that was complete, it started asking if I wanted to install it. Oh, go on then! The first thing to do is accept the licence agreement:
Boring licence agreement
After a few seconds, Windows update announced it was ready to start:
Ready to upgrade
Clicking ‘Start the upgrade’ causes my PC to begin shutdown, triggering the familiar ‘Configuring update….. Do not turn off your computer’ message. After a minute or two, the upgrade process asked me to ‘Sit back and relax’. Never one to argue with that sort of instruction, I made a cup of tea.
Upgrading…. relax
After about 15 minutes, it appeared to be finished:
Upgrade done
It asked if I wanted to accept some default settings. I wanted to see what that meant, so I went and clicked ‘Customise settings’. It turned out that I didn’t change anything, but at least I know what I was agreeing to. And then it was done.
Did I have any problems? The graphics card I have wanted two reboots, I don’t know why. I didn’t have to do anything else (but I had already read its driver had Windows 10 problems, and had updated it the day before).
CU9 for Dynamics AX 2012 R3 contains several updates for functionality in the Warehouse management module. One of them is KB 3051622 “Warehouse Management (WHS) Replenishment performance issue”. If you’re using replenishments in Dynamics AX 2012 R3, it’s worth checking out the LCS page for this fix, because it goes into some detail about the changes that this hotfix brings.
One change relates to the use of the ‘Replenish only fixed locations’ tick box on the replenishment template line. I can’t imagine how you would be using replenishments and NOT have this field ticked, so I think it will be relevant to everyone using replenishments. With this hotfix installed, when ‘Replenish only fixed locations’ is ticked, the ‘Select locations’ button becomes greyed out:
If ‘Replenish only fixed locations’ is ticked, the ‘Select locations’ button is unavailable
To compensate, the ‘Fixed product locations’ and ‘Locations’ tables are added to the ‘Select products’ query:
The Product query is extended to the ‘Fixed product locations’ and ‘Locations’ tables
(And, if you’re using Product variants, then the ‘Fixed product variant locations’ table is added to the query).
This, according to the description of the hotfix, improves the performance of the replenishment functionality. In my test environment, which has CU9, I haven’t noticed a problem with the performance of the replenishment job. But I have seen a customer’s test environment (with an extraordinarily large number of items and locations/fixed locations) where the replenishment job was taking over half an hour to create a replenishment for ONE item. So I have high hopes for the performance part of this fix!
An important point is described on the hotfix’s LCS page: “Note: When you apply this hotfix, any existing replenishment template lines that are configured with “Replenish only fixed locations” checked must be deleted and recreated to prevent errors during processing.”
This is because the query that was saved against each replenishment template line is now invalid. If you don’t recreate the query, when you run the replenishment job, you’ll get the error “Error executing code: Wrong argument type for function.” and no replenishment work will be created:
The replenishment job will throw an error
To eliminate the error, the advice says to delete the replenishment template line and recreate it. I find that you don’t have to delete the line, but you do have to delete the query. The easiest way to do this on one line is to un-tick and re-tick the ‘Replenish only fixed locations’ tick box. Then you can re-configure the query and you’re away.
If you managed to import your replenishment template lines, then you’ll want to delete them, adjust the import to build an ever so slightly different query, and reimport them. If you created your replenishment template lines by hand.. well, I hope you don’t have too many. But either way, you’ll need to factor this exercise in to any CU9 upgrade plans that you have.
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:
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.
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.
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’.
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:
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?
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:
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:
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:
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:
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:
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?
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.
DataValidationException: Sales line item could not be found
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 ‘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:
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:
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):
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.
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):
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:
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.
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
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
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
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 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.
guyterry's Dynamics AX blog 