Price Lists
This feature is only available in environments where the new sales pricing experience is enabled. If you have not yet enabled the new sales pricing experience, you need to go here.
Business Value
Microsoft added the extended price calculation as an optional feature in 2020 release wave 1. When this feature is enabled, the sales and purchase prices schema gets changed 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 that you do not duplicate prices by checking lines within the current import and existing price lines when importing sales or purchase prices.
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 the 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 aims to allow you to specify your intent so that you do not get unexpected results. For example, if your intention is only 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. If existing prices are found with the same identifiers (Customer, Item, Unit of Measure Code,Starting Date, etc.) then the import gets cancelled. |
| Update | Only updating existing prices in Business Central (e.g. to expire prices). If the price in the file does not exist in Business Central then the import gets cancelled. |
| Update/Add | You will be adding new prices as well as updating existing prices. |
Replace existing document No.
Allows you to group the prices you want to import to control how price lists get created. You can either use the number you import as the price list or replace it with a number from the number series applicable for the source price group.
|Setting Value|Description| |:-||:-| |Yes|The price list code in the file will be replaced with a new number| |No|The price list code from the file will be used on the new price list|
If you specify Yes for this then you can import a new price list using the old price list code and the program will assign a new number from the number series.
If you do not include a price list code in your mapping then this setting has no impact on the import. In this scenario, the program will group the prices by customer, currency, starting/ending date and assign a number automatically.
Status after import
Allows you to specify the status of the imported price lists after the prices get imported. This 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 |
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. By using this setting, your active price list does not need to have an ending date - you can let Excel Importer expire the old price list when you import a new price list.
| Setting Value | Description |
|---|---|
| Yes | The old price list will be expired |
| No | No changes will be made to the old price list |
The old price list will only be expired if you have supplied new prices for all items contained in the only price list.
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 |
| Ending Date |
The captions for the fields Source No. and Asset No. change based on the type of price list.
Grouping Price Lines into Price Lists
You can explicitly group the prices by including a price list code in the file and column mapping when you import prices. Alternatively, you can let Excel Importer group the price lines for you. Excel Importer will group the prices if there is no price list code in the file and column mapping.
| Field |
|---|
| Price List Code - if specified |
| Source Type |
| Source No. |
| Currency Code - if changing defaults is not allowed |
| Starting Date - if changing defaults is not allowed |
| Ending Date - if changing defaults is not allowed |
Examples
Import Sales Prices
Excel Mapping Template
On this template, we have 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 to have this data in the file.
Excel File
In the example, we set up a file to show you how you could create/update your prices using an Excel Sheet that contains 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 get defaulted using a Vlookup.
Imported Sales Prices
The image below show prices that were imported from the Excel file using the Excel Mapping Template shown 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.