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.

Advertisements
12 comments
  1. Johan Karlsson said:

    HI is this only applicable on AX 2012 R3, the R2 version does not seem to have that service group.

  2. Johan Karlsson said:

    Tested on a R3 and it worked out great:)

  3. marcomeinders said:

    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.

      • Michaela said:

        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.

  4. David W Ruston said:

    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
    Setup
    Services and Application Integration Framework
    Inbound Ports

    We then found PriceDiscServices in the list and ‘Activated’ it using the button provided on the form. The integration then operated as expected.

  5. Michael Johnson said:

    Is there a way to use this to update Base Price, Purch Price and Purch Net Price?

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: