How to import item standard costs AX 2012

Actually you can use this method for all item cost prices, not just standard cost items. The only difference is that Standard costs must reference a costing version with a Costing type = ‘Standard cost’, and item’s which don’t’ use standard costs must use a costing version with a Costing type ‘Planned cost’

Note. A standard cost must be activated for each Site before an item may be received into that Site. You will notice a ‘Copy’ function on the costing version form, but this must be used to copy from one costing version costs on one site to another costing version for the cost at a different site, or from active costs to pending costs for another site.

Therefore there are three possible import and activation processes:

  1. Import a standard cost for each site (referencing the Site Inventory Dimension Number on each row of the import spreadsheet).
  2. Import the costs for a site. Activate the costs for that site. Copy the costs to pending costs for the other sites and activate the pending costs for the other sites.
  3. Create a cost version for each site. Import pending costs for one site/cost version. Copy those costs to other sites/cost versions. Activate all of the sites/cost version costs.

Option 2) is described in detail, as follows ….

Costing versions

Inventory and warehouse management > Setup > Costing > Costing versions:


Create ‘Standard cost’ costing versions as required.

Note. You may find it convenient to have a ‘Copy’ version in addition to the version used to activate the costs.

Standard costs

Standard costs are established for each item by import or BOM calculation.

Standard costs are seen at Product information management > Released products > Manage costs > Item price:


Note. This form displays Pending and Active cost prices.

You won’t import active costs, you’ll import pending costs and activate them.

Excel import

Unfortunately you need to have System admin rights to import into a table using the excel add-in. If you want to give this function to ‘normal’ users you’ll have to create a customised Data import export framework ‘Target entity’.

Open Excel, connect to Microsoft Dynamics AX and setup an import template for the InventItemPriceSim table like:


Note that ‘Site’ on the table is mapped to ‘Dimension No.’ in the spreadsheet.

Add additional rows and ‘Publish data’:


Check your imported data

Inventory and warehouse management > Setup > Costing > Costing versions > Price > Item price:


Activate the costs

Inventory and warehouse management > Setup > Costing > Costing versions > Activate


Copy to other sites

Inventory and warehouse management > Setup > Costing > Costing versions > Copy > Copy item prices:


Complete the ‘Select’ filter query and Copy from and copy to options, like:


Click OK, and check new cost prices:


Repeat for each Site.

Activate the costs (as above).


Advertisements

Data import into a simple table Dynamics AX 2012 R3

I was reading a post on the Microsoft Dynamics AX community web-site (at https://community.dynamics.com/ax/default.aspx and a reply by André Arnaud de Calavon reminded me that there’s a ‘Table’ option available when you setup a Data import export framework ‘Target entity’

Here’s an example.

Go to Data import export framework > Setup > Target entities. Click New and select Table for the Entity type:

Select your Application module and give the Entity a name, then enter your target table name in the Staging table, like:

(The drop-list on Staging table doesn’t seem to work for me, no matter, that’s one of few table names in Dynamics AX that I actually know).

Note that Entity class and Target entity are blank.

As normal with the DIEF, go to Data import export framework > Common > Processing group, create a new processing group and then click on the Entities button. Enter/select your Entity and Source format:

Then click ‘Generate source file’ to run the wizard, Select the fields you want to import and Click Generate sample file, and save that somewhere as a .csv file:

Then edit that file and put in some import data:

You should be way ahead of me by now. Save that file (don’t forget to close out of Excel) and set it as the Sample file path, and Click on Generate source mapping.

If you want, click on Preview source file to check that all is well.

Now back on the Processing group click ‘Get staging data’. Then OK:

Then Run, and OK again:

I get:

Notice, ‘Inserted in target’.

Let’s go take a look:

That’s really neat. Thanks André.

A user friendly data import framework in Dynamics AX 2012 R3

I guess that most of us by now have used the data import export framework for imports while setting up a new Dynamics AX implementation, but there are some imports that the business will want to perform on an on-going basis long after the implementation team have celebrated a successful go-live, and moved on to their next project.

One such example is purchase prices.

Typically to do an import you’ll fire up the Data import export framework, create a processing group, select your target, and run the wizard to create a source file.

For the purchase price import we import into a trade agreement journal and the wizard will give us a source file like this:

Now, it’s perfectly possible to create an input file in that format, but your users probably already have a purchase price spreadsheet, and it’s going to be in a different format – something like:

Now we’re going to have to do a couple of compromises. We’re importing into a journal, and to get all of the lines added to the same journal, we’re going to need to define the journal number on each line. We need the Vendor code and Currency on each line, and unless all our items have the same unit of measure, we’re going to need that on each line too (but in my case all of these items have a purchase unit of ‘ea’. That’ll give us:

Right. Let’s start cooking. First save the Excel spreadsheet as a CSV file (don’t forget to close the spreadsheet after saving the CSV file – the DIEF won’t import from a CSV file if the spreadsheet’s still open. Then from your processing group setup a new Source data format of CSV+4 (because we’re starting the import at Row 4)

Now we’re going to customise the input fields. Click ‘Entity attributes’ on the Select entities for processing group form. You’ll be used to seeing setups like this:

But you can create your own mapping. In a moment we’re going to set default values for all the fields we’re not importing, so here specify the fields in your import file and organise your setup so that they’re specified into the correct columns. In my example that means I end up with this:

Now we need to map these input fields to the target fields. Back on the Select entities for processing group form click ‘Modify source mapping’. Adjust the mapping details until you’ve mapped all of the fields you want to import:

Now obviously we have to set default values for those fields not present in the import file. Add a new row select a target field and tick ‘Auto default’. Then click ‘Default values’ and enter your value, like:

When you’re done you should have:

Note. For the Line number I used Auto-generated, not default. It’s a key field for the journal line.

On the Select entities for processing group form you can use the Validate button to check that all is well, and you can preview the source data file:

So to re-cap, our data import format is:

And once imported into the Price/discount agreement journal we have:

Obviously this technique can be used for any ‘business as usual’ data import.