Updating Your Pricing Journals Through Excel
If Dynamics AX is the body of all your businesses data, then some may consider pricing to be the heart and soul, because making sure that everything is priced correctly is vital. And just like your own heart, you need to keep it pricing healthy and fit by adjusting it to meet the customer’s wishes and whimsy’s so that you are the most competitive in the marketplace. This doesn’t have to be a huge data entry chore though because Dynamics AX allows you to update your pricing journals through Excel.
Now you have an easy way to make sure that The Price Is Right without having to dress up in silly clothes.
How To Do It…
Before we start doing this, there is one configuration step that you need to perform. Excel updates the pricing through the PriceDiscService web service, and you need to make sure that it’s deployed. To do this, open up AOT and find the PriceDiscService service within the Service Groups.
Then right-mouse-click on the service group, and click on the Deploy Service Group menu item.
After a minute you will receive a notice that the web service has been deployed, and you can exit from AOT.
How It Works…
Now when you are within a pricing journal, click on the Edit In Excel menu item in the menu bar.
This will transfer all of the data from the pricing journal over to Excel for you.
Now you can quickly update your pricing data using all of the Excel functions. For example, to apply a date range to all of your price list items, just update the first row, select the remaining rows, and press CTRL+D.
Now all of the dates have been updated.
Also, as a bonus there is a lot more supplementary information in the price list that you don’t normally see. One example is the base price. You can use this to calculate the price lists line amount through a formula.
And then just paste the prices into the spreadsheet.
When you have updated the price, just click on the Publish menu button within the Update group of the Dynamics AX ribbon bar.
When it has updated, you can look in the Dynamics AX Status tab and see that the records have been updated.
But more importantly you can see that the prices have been updated within your journal.
Now that is super easy.
Reblogged this on A Tinkerers Notebook.
HI is this only applicable on AX 2012 R3, the R2 version does not seem to have that service group.
Johan, this is a new R3 feature – sorry to tease everyone on the R2 release.
Tested on a R3 and it worked out great:)
Am I right that it is not possible to create additional journal lines, but only adjust existing lines?
Marco, no – you can add additional lines as well. Although in R3 this is a lot easier because the Excel integration is built into the pricing journals, making this obsolete. Murray.
Can you advise how to add the additional lines, so for example I have clicked on edit in excel to add in a “To Date” to existing prices, but now want to upload at the same time some additional prices, I have added in the lines but when I hit publish I receive the error “object reference is not set to an instance of an object”
Michaela, I don’t think that there is an easy way to add price lines through Excel – I came across the same problem that you did. But… have you tried the Add Lines function within the price lists. This allows you to go out and add products in bulk using the product hierarchies. Once you have added in all of the products that you want there, you can then open up the price list in Excel and update all of the details. I just went through this example while creating the Procurement Guide (http://www.blindsquirrelpublishing.com/collections/bare-bones-configuration-guides/products/m09-configuring-procurement-and-sourcing-within-dynamics-ax-2012) so it’s fresh in my memory. Murray.
Hi Murray – On the latest AX2012 R3 CU8 VM we found that we had to do a similar action to the one you described above but without needing to go into the AOT.
Open System Administration
Services and Application Integration Framework
We then found PriceDiscServices in the list and ‘Activated’ it using the button provided on the form. The integration then operated as expected.
David, thanks for that tip – it looks like I took the more scenic route. I need to remember your way because it’s more direct 🙂 Murray.
Is there a way to use this to update Base Price, Purch Price and Purch Net Price?
Michael, I would do this by creating an Excel template against the released product records.