Item Price Import

As indicated before, managing and maintaining the large Sales and Purchase Price tables can be quite cumbersome. It is quite common that suppliers provide new price lists once or more times each year and that you as a wholesale trader need to update your sales prices as well at the same rate. Furthermore, these lists may contain items that are not present in Microsoft Dynamics 365 Business Central yet. Although the Configuration Packages provides the opportunity to import such data, this functionality is not very flexible.

Price & Item Management comes with a flexible import functionality that can not only import purchase and sales prices at the same time, but also create items if the item is new. It is also not required to have your own Item No. in the import document, as the import function will also recognize the item based on the Vendor Item No. and Vendor No.

Furthermore, this function circumvents several issues you may encounter with differences in the formatting of the dates and numbers between the import files and Microsoft Dynamics 365 Business Central, because this function is based on a csv-format (comma separated).

Item Price Import Setup

You can open the Item Price Import Setup from the Price and Item Management Setup page.

Import

Update Unit Cost on Item card Specifies whether to (attempt to) update the Unit Cost field on the Item card when processing the lines from the Item Price Import.

Options:

• Never
• If viable

Note

If viable only applies when you can also manually update the Unit Cost on the Item card, this option will also update the Unit Cost. For example when there are Item Ledger Entries related to the Item the Unit Cost can't be updated.

Skip update Unit Price Item Card If True then the Unit Price on the Item Card will not be updated when submitting the Item Price Import record.

Import Version

With the Import Version you can set the version of the imported fields and .csv format. The version differentiate in functionality, available & order of the imported fields.

The different versions are specified further below.

Version 1.0

Imports Item prices & discounts. Also creates Items when needed. Works for old & New Sales Pricing Experience.

Important

Fields and features may differentiate based on the activation of the New Sales Pricing Experience

Available fields & order

• Sales Price List [^1]
• Sales Price Assign-To Type [^1]
• Sales Price Assign-To No. [^1]
• Purchase Price List [^1]
• Purchase Price Assign-To Type [^1]
• Purchase Price Assign-To No. [^1]
• Item Vendor No.
• Vendor Item No.
• Item No.
• Description
• Minimum Quantity
• Direct Unit Cost
• Unit Price
• Starting Date
• Ending Date
• Tariff No.
• Country/Region of Origin Code
• Net Weight
• Currency Code
• Base Unit Cost
• Sales Line Discount %
• Purchase Line Discount %
• Variant Code
• Variant Description
• Factor
• Price List Unit of Measure Code
• Free Field 1 [^1]
• Free Field 2 [^1]
• Free Field 3 [^1]
• Free Field 4 [^1]
• Free Field 5 [^1]

[^1]: Only available when the New Sales Pricing Experience is enabled

Version 2.0

Import Item & Item Discount Groups prices & discounts. Also creates Items when needed. Works only for the New Sales Pricing Experience.

Available fields & order

• Sales Price List
• Sales Price Assign-To Type
• Sales Price Assign-To No.
• Purchase Price List
• Purchase Price Assign-To Type
• Purchase Price Assign-To No.
• Starting Date
• Ending Date
• Product Type
• Product No.
• Description
• Minimum Quantity
• Price List Unit of Measure Code
• Direct Unit Cost
• Unit Price
• Sales Line Discount %
• Purchase Line Discount %
• Currency Code
• Variant Code
• Variant Description
• Item Vendor No.
• Vendor Item No.
• Tariff No.
• Country/Region of Origin Code
• Net Weight
• Base Unit Cost
• Factor
• Free Field 1
• Free Field 2
• Free Field 3
• Free Field 4
• Free Field 5

Item Price Import Free Fields

With the Item Price Import Free Fields, you can set up your own fields to be imported, validated & submitted with the Item Price Import.

In the Item Price Import Setup you can configure five free fields. For each free field you will need to configure:

• Free Field Table No.: Choose from the following available tables
  • Item
  • Price List
  • Price List Line
• Free Field Field No.: The field which you want to import

The Caption, Data Type and Length of the field are shown after you’ve setup the Field No. The Data Type _and _Length of the field will be validated on the Validate and Submit step of the import. Note: please restart the browser / Business Central session to enable the captions of the Free Fields in the Item Price Import page. In the Item Price Import page, the configured free fields are visible with their corresponding caption. Fields not setup are shown with the caption “Not Used”. The free fields are included in the export template file and are processed with the Import, Validate and Submit actions.

Import of the Item Prices

To use this functionality, navigate to Item Price Import. It is advised to start with an export, even though the table may be empty, to create a csv-export file in which it is clear what the order of the columns in the import file should be. To do this select Actions > Functions > Export.

A file under the name ItemPricesExport.csv is saved in your local downloads folder. When you open this document in Excel you will find the following columns which are used for the import:

With this mapping you can convert your data to this lay-out. Once you have done this, you need to save the file as a csv-file. You do not need to delete the first row containing the column headers as the import will automatically discard these.

Note: records available in the page (filtered) are included in the export.

Consider the following scenario in which there are 4 records in an import file:

In the first 3 records there is no Item No., but Vendor No. and Vendor Item No. are given. Furthermore, for all rows there is a Direct Unit Cost and Unit Price and a Starting Date. Furthermore, there is also the possibility to import / adjust the Tariff No., Country/Region of Origin Code and/or Net weight of the items.

To import a file, select Actions > Import from the Item Price Import page.

A pop-up appears where there are several options to choose:

In Update Price you can select Unit Cost, Unit Price or Both.

• If you select Unit Cost the Unit Cost on a record in the Purchase Price table is updated or inserted. If the Update Unit Cost on Item Card setting is set to If viable, then the Unit Cost on the Item card will be updated as well. The Unit Cost on the Item card can only be modified if the Microsoft Dynamics 365 Business Central logic allows that. For example, the Unit Cost of items with Item ledger entries present cannot be modified.

• If you select Unit Price both the“Unit Price on the Item card as well as a record in the Sales Price”table is updated or inserted. This Sales Price record will be a record for all Customers.

• If you select Both, obviously both options above will be executed.

If Create Items (and Variants) is selected, new items and if available the variants will be created for records in the import file for which no items can be found in the database. In that case both the Item No. in the import must have been empty or not existing as well as the combination Vendor No. and Vendor Item No. could not be traced to an existing item.

If Create Items (and Variants) is selected, you can choose out of 3 options how the new item should be numbered in the field New Item Nos.

• In case Item No. is selected, the value in the column “Item No.” in the import file is used as the new Item No.

• In case Vendor Item No. is selected, the value in the column Vendor Item No. in the import file is used as the new Item No.

• In case Automatic is selected, a new Item No. is generated based on the number series properties you can select in the next field Automatic Item Nos.

In the last field Config. Template you can select the configuration template that will be used for the creation of new items. This template will be used for all new items in the same import file.

After filling out this pop-up, you can select OK, after which the following pop-up appears:

By selecting Choose, Explorer will be opened where you can select the file that you wish to import:

By selecting Open the import will start and after this has been done a message will appear, indicating how many lines have been imported:

The import of the example file shown above will result in the situation, presented below. For lines 1 and 2 the Item No. was found based on the Vendor No. and Vendor Item No. present on the Item card. Line 4 contained an item present in the database. Line 3 apparently contains a new item. In this page you can still make changes before these lines are submitted. You can select _Process > Valid_ate to check whether the data are valid. If not, an error message will appear in the fact box for the invalid lines. You can resolve the error messages or delete the line to proceed. If these errors are not resolved these lines will be skipped.

After the data have been checked, adjusted where necessary and revalidated you can select Process > Submit to submit the lines.

The following screenshot displays the item list, filtered for the items in the scenario, after submitting. You can see that the Unit Cost and Unit Price have been adjusted for these items except for the Unit Cost of Item 1928-S, where it was not allowed.

If you navigate to the Sales Prices from a random item and adjust the filter settings to the items in the scenario, you will also see that records are generated for these items as well:

Import of the Item Variant Codes

While importing the Price Lists, it is also possible to import Item Variants. To do this, enter the Variant Code in the import file.

If the Create Items (and Variants) field is checked in the Import Item Prices screen, the Item Variants will be created automatically. After importing the Item Variants, the result is as follows for item 1920-S:

Process discounts with the Item & Price Import for Sales & Purchase

It is now also possible to specify a discount % per line when importing a price list. To do this, use the Sales Line Discount % and Purchase Line Discount % fields in the import file.

Skip update of Unit Price on Import

In some cases, you don't want to update Unit Price on the Item card. In this case, you can turn off updating Unit Price in the Price and Item Management Setup.

Use factor for calculating Unit Price based on Unit Cost

If you want to automatically calculate the Unit Price based on the Unit Cost, you can use a factor for the calculation. The Unit Price will be calculated by Unit Cost x Factor

Prerequisites for the calculation:

• Factor <> 0
• Unit Cost <> 0
• Update Price = Both

You can calculate the Unit Price directly on import of the price list or afterwards it will always be calculated on validation or submitting the entries.

This can be set up by the Calculate Unit Price directly with Factor option on the request form if the Import Item Prices.

If you want to use a Rounding Method during the calculation you can set up a Rounding Method in the Price and Item Management Setup.

Close Old Price Records

When new prices are imported, chances are that there are old prices records, that have a starting date prior to the new records and an open end date:

In case the prices in the new agreements are higher, the system will still select the old price in the sales or purchase document. To prevent this, the old records should receive an end date. For this, two batches are included in the Item Price Import screen:

• Close Previous Purchase Prices
• Close Previous Sales Prices

These batches can be selected from the Actions > Process menu:

When the batch is executed, the system will search for records that have a blank ending date. If a record is found, it then searches for records with:

• the same Minimum Quantity
• the same Item No.
• the same Sales Type & Code
• the same Unit of Measure Code
• new Starting Date

In case a record is found in the Price List, the record with the older starting date will be updated with an ending date that is one day before the starting date of the newer record:

New Sales Pricing Experience

Microsoft Dynamics 365 Business Central has a new feature: New Sales Pricing Experience. This feature uses Price Lists for the setup of Purchase and Sales Prices.

For more information see the link below: New Sales Pricing Experience.

This feature is automatically enabled from update 26.0 (2025). If you want to use this feature ahead of time you can enable this feature in the Feature Management Page.

Note

After you enable this feature for all users, you cannot turn it off again. Please first experience this feature in a sandbox environment.

When this feature is enabled it automatically affects the Item Price Import functionality of the Price & Item Management app.

The important changes are listed below.

1. Purchase Price List and Sales Price List fields are included in the pop-up of the Import Item Prices. For the Purchase Price List the price list must apply for a Vendor and the Sales Price must apply for All customers.

1. The fields Purchase Price List and Sales Price List are included in the import / export file and the Item Price Import page.

2. The fields Purchase Price Assign-To Type, Purchase Price Assign-To No., Sales Price Assign-To Type and Sales Price Assign-To No. are included in the import / export file and the Item Price Import page.

3. The Purchase Price List and Sales Price List are mandatory fields during the validate & submit of the import

4. Factboxes Purchase Price List and Sales Price List are added to the Item Price Import page. These factboxes provide more details of the purchase and sales price list of the selected line.

5. After the submit of the import the price lines are added to the price list specified in the import lines.

6. Close Previous Purchase Prices & Close Previous Sales Prices merged into one function: Close Previous Prices

1. Feature Free fields enabled

2. Import Version Version 2.0 available

---

Last update: August 13, 2024