Price Lists
This feature is only available in environments where the new sales pricing experience is enabled. If you are yet to enable the new sales pricing experience, you need to go here.
Business Value
Microsoft added the extended price calculation as an optional feature in the 2020 release wave 1. When this feature is enabled, the sales and purchase prices schema changes to a shared price list header and price list line table. Updating these tables with configuration packages becomes more complex due to validations that are part of the feature and often require multiple iterations of updating the headers and lines to achieve the desired result.
Feature Details
This feature allows you to import all types of prices. You can use this feature to import new prices and update existing prices. The import allows both Price List Lines and Price List Headers to be created or updated. The logic ensures you do not duplicate prices by checking lines within the current and existing price lines when importing sales or purchase prices. The feature also supports automatically grouping your prices into different price lists based on configuration.
You can import prices from the sales, purchase and job price list pages using the Import from Excel action. This action is available in lists and cards.
If you run the import from a price list card, the program will check that all imported prices relate to that price list header. If you want to import multiple price lists, you should trigger the import from the price list.
Settings
Update Method
This setting allows you to specify your intent so that you do not get unexpected results. For example, if you only intend to add new prices, the import will ensure that the latest imported prices do not exist already.
| Setting Value | Description |
|---|---|
| Add | Only adding new prices. The import gets cancelled if existing prices exist with the same price line identifiers (Customer, Item, Unit of Measure Code, Starting Date, etc.). |
| Update | Only update existing prices in Business Central (e.g. to expire prices or correct errors). If the price line in the file does not exist in Business Central, the import gets cancelled. |
| Update/Add | You will add and update new prices. |
- Add refers to price list line records being inserted. It is possible to add price list lines without creating new price lists. Therefore, if you want to add prices to an existing price list, select Add or Add/Update.
- Update refers to modifying existing records. You cannot use this option to add records to an existing price list.
Status after import
This setting allows you to specify the status of the imported price lists after the prices get imported. This setting works with the standard functionality that prevents changes to active price lists by changing the status before importing the lines and then updating the status at the end of the import.
| Setting Value |
|---|
| Draft |
| Active |
| Inactive |
To configure Business Central to prevent changes to active price lists, open the Sales & Receivables Setup and deselect Allow Editing Active Price. Note that this is a standard feature and not part of Excel Importer.
Price List Grouping
The app uses this setting to determine how to group prices into the same Price List.
The app includes Starting Date and Currency Code in any groupings listed below.
We recommend using this setting to organise your price lists because it makes it easier to manage your prices.
Source Type/No.
Prices are grouped by Customer, Customer Group, Vendor or Job. Use this setting if you want the app to put all prices relating to the same Source into the same price list.
Source Type/No./Item Category
Prices are grouped by Customer, Customer Group, Vendor or Job and the item category. You can only import items using this setting. Use this setting if you need a different price list for every item category.
Item Vendor
Prices are grouped by the Item Vendor. Use this setting to group your prices based on the vendors of the items. Some organisations structure their prices using this method when they want to adjust customer prices based on vendor price changes.
- You can only use this grouping method for sales prices. On the purchase side, you will group the cost prices by Source Type/No. (Vendor).
- If you want to have different prices per customer, you have to configure the price lists, or else you will receive this error: You cannot import a Price List Line to an existing price list when the line does not respect the defaults on the header.
Manual
The app lets you decide how to group your prices into price lists using the Price List Code in the file. If the file does not contain a Price List Code, the app will import all prices into the same Price List.
If you manually group your prices, the app restricts the values you can select for the Expire Old Prices setting.
Expire old prices
This setting allows you to expire old prices by updating the ending date to the day preceding the starting date of the new price. If you use this setting, your active price list does not need an ending date - you can let Excel Importer expire the old price list when you import a new one. One of the reasons for the expiration of old prices is that Business Central, by default, will find the best price, so you run the risk of using the old, lower price if you still need to expire the prices.
| Setting Value | Description |
|---|---|
| Price Lists | The app will expire the old price list(s) that have the same price list grouping. |
| Lines | The app will expire old price list lines with the same line identifiers but an earlier starting date. |
| No | The app will not expire the old price list. |
- When you import new prices, the app will expire the previous prices with an earlier starting date.
- If you add prices to an existing price list, the app does not expire prices in that same price list. However, the app will still try to expire previous versions of the price list (based on the Price List Grouping or line identifiers.)
Price Lists
This option is our recommended method for expiring price lists. However, this option cannot be used when you group your prices manually.
Lines
We recommend only using this option if you manually group your prices.
- This option only applies to old prices related to imported prices. Therefore, if the related price header does not allow updating defaults when expiring a price, the app will update the header to allow updating defaults.
- The old price stays the same if your new price list contains fewer prices than a previous price (e.g., a product is no longer sold).
Validation on expiring price lists
The app does not allow you to expire prices when it finds unexpired prices for the same Price List Grouping being imported. This is to prevent unexpected results. The app considers any price to be unexpired if it has either a blank ending date or an ending date after the starting date of the price being imported. The app does not check Inactive Price Lists but includes Draft prices.
Update Description
This setting allows you to control whether the app will automatically update the description of imported price lists and price lists for which the app expires.
| Setting Value | Description |
|---|---|
| Yes | The app automatically updates the description. Note: If the description is provided in the file, the app will ignore it. |
| No | The app does not update the description - you must provide this value in your file. |
Replace existing Document No.
With this setting, you can specify that the app must replace the Price List Code from the imported file with a number generated from the number series applicable to the source price group (Customers, vendors, etc.). You can only use this setting when the Price List Grouping setting is Manual
| Setting Value | Description |
|---|---|
| Yes | The app will replace the price list code in the file with a new number. You can only select this option if the Update Method is Add - it will not work with Update/Add. |
| No | The app will not change the price list code in the file. |
- You cannot replace the price list code when you run the import from the Sales or Purchase Price list cards because those actions are intended to support adding/updating on that price list only.
Preventing Duplicate Prices
Excel Importer includes additional validation to prevent duplicate prices. The validation checks that each line in the file is unique and that the line does not already exist in Business Central (if you are only adding records).
Price List Line Identifiers
Excel Importer uses the combination of the following fields to identify the price list line as unique:
| Field Name |
|---|
| Source No. (Customer/Vendor/Job No.) |
| Asset No. (Item/Resource/G/L Account No.) |
| Variant Code |
| Unit of Measure Code |
| Work Type Code |
| Minimum Quantity |
| Currency Code |
| Starting Date |
The captions for the fields Source No. and Asset No. change based on the type of price list.
Examples
Import Sales Prices
Excel Mapping Template
In this template, we specified that the first data row is 6. This value corresponds to the layout of the Excel file.
Notice how we specified constant values for "Assign-to Type", "Defines", and "Product Type". You can do this when the values are constant, and then you do not need this data in the file.
Excel File
In the example, we set up a file to show you how to create/update your prices using an Excel Sheet containing formulae. We created a header section where you can enter parameters (Customer, Wholesale Discount %, Line Discount %, Freight Factor, Effective Date). The data in the lines mostly comes from the header section or a calculation using values from the header section. The description, cost, and base price are defaulted to using a Vlookup.
Imported Sales Prices
The image below shows prices imported from the Excel file using the Excel Mapping Template above.
Excel Importer automatically creates the price list description if you do not supply the description in the file. The price list description was auto-generated in this example.