Setup import template
DYCE Price List Import allows you to import price lists from different vendors into Microsoft Dynamics 365 Business Central very easily. The price list can be filtered during import and individual fields can be filled or replaced with specified values. The filter and field mapping are defined in an Import Template. A template can be created for each supplier and price list. This allows to process different, extensive price lists with just a few clicks. The import templates are Data Exchange Definitions.
The Item Templates contain DYCE-specific fields so they can be easily predefined for new master data records.
Data Exchange Definitions
A new Data Exchange Definition can be created in the page with the same name. The page can be opened using the search (Alt+Q). Select Import Price List in the Type field after creating a new Data Exchange Definition. Otherwise, the Data Exchange Definition cannot be used to import price lists. The selection predefines some fields and creates a Line Definition.
- The File Type is set to Variable text.
- In the Line Definition, existing lines are deleted and a new one is created for importing the price list.
- A Field Mapping with the Table ID of the Import Price List (70112401) and the codeunit for processing files in CSV format (Reading/Writing XMLport - 70112401).
Please check if the File Encoding and Column Separator match the file to be imported (CSV format). After Import Price List is selected, the fields may not be editable until the page has been refreshed or closed and reopened.
Column definition
The Column Definition specifies which column of the import file contains which information. A detailed description of how to set up Data Exchange Definitions can be found here.
| Column No. | Name | Data Format | Data Type | Data Formatting Culture |
|---|---|---|---|---|
| 2 | manufacturer item no. | text | en-US | |
| 3 | Valid from | date | dd.MM.yyy | en-US |
| 4 | Purchase price | decimal | en-US |
Field Mapping
The Field Mapping can be opened in the Line Definitions fast tab via the Field Mapping action (action group Manage).
If the File Type Import Price List has not been set in the Data Exchange Definitions, no field mapping has been created yet. Change the type to continue.
The Field Mapping specifies which column of the import file should be imported to which field in the Import Price List page.
| Column No. | Column Caption | Field ID | Field Caption |
|---|---|---|---|
| 2 | manufacturer item no. | 5 | Manufacturer Item No. |
| 3 | Valid from | 13 | Valid from |
| 4 | Purchase price | 9 | Unit Cost |
Import rules
Not all lines in a price list are relevant. In order not to have to edit the import file or the imported lines in a cumbersome way, Filter can be applied. Information, such as the Vendor No., is usually missing in import files because the supplier cannot provide it. It can be added by defining a Fixed Value. Filters and Fixed Values can be used in combination to replace values depending on the content of the import file.
The Import Rules field shows for each line in the Field Mapping page how many filters and fixed values are stored. Clicking on the number in the Import Rules field opens the Edit Import Rules page. In this page filters and fixed values can be deposited.
Filters
Filters are applied to the contents of the columns in the import file. In order to filter on the content in a column, it must be entered in the field mapping. Clicking on the number of import rules in the line will open the Edit Import Rules page. The Filter field is used to enter the filter criterion, by which the import file is to be filtered. The filters can be designed the same way any filter is used in Microsoft Dynamics 365 Business Central.
If there is more than one filter (multiple lines), a logical "OR" is applied. This applies to multiple filters in one line as well as in different lines.
If a column in the import file is to be filtered and the column is not to be imported, the Filters only field is used (Field ID: 21).
| Column No. | Column caption | Field ID | Field Caption | filter |
|---|---|---|---|---|
| 8 | Description | 6 | Description | Business* |
| 8 | Description | 6 | Description | Azure* |
All lines starting with Business or Azure in the description will be imported.
Fixed values
Fixed Values are used to automatically fill in missing information in the Import Price List page. Fixed values are inserted in the Import Rules the same way filters are.
| Column No. | Column Caption | Field ID | Field Cation | Fixed Value |
|---|---|---|---|---|
| 8 | Description | 2 | VendorNo. | 10523 |
During import, the value 10523 is automatically inserted in Vendor No. in the Import Price List. The specification of the Column No. is a technical necessity. Which column is chosen has no effect here.
Combination of Filters and Fixed Values
Filters and Fixed Values can be used in combination. The filter is applied first. All lines of the import file that match the filter are imported. The Fixed Value is then applied exclusively to these lines. This makes it possible to set different values in the same field for subsets of the imported lines.
| Column no. | Column name | Field ID | Field name | Filter | Fixed value |
|---|---|---|---|---|---|
| 8 | Description | 8 | Item Template Code | Business* | ITEM BC |
| 8 | Description | 8 | Item Template Code | Azure* | ITEM AZ |
All lines starting with Business or Azure in the description will be imported. In all lines that start with Business in the description, the value ITEM BC is set as Item Template Code in the Import Price List page. In all lines that start with Azure in the description, the value ITEM AZ is set as Item Template Code in the Import Price List page.
If a line falls within the filter range of multiple import rules, the Fixed Value from the last applied import rule wins (e.g. Filter 1: Business*, Filter 2: B*).
Permissions
All permissions necessary to use DYCE Price List Import are contained in the DYCEPRICELISTADMIN (DYCE Price List Import Admin) permission set.