Item import worksheet AE078

General description #

The basis of this functionality is so called "Item Import Worksheet"

The worksheet in the header contains a selected item import worksheet template (where user can select a different journal). The workbook itself consists of individual rows. To support variable fields (such as item attributes), there is a "Detail Information" action in the worksheet that is bound to the currently selected workbook line that displays additional imported information. This table includes data imported according to the configuration for "Item Attributes" and "Item Translations", see below "Definition of file structure".

File import #

You can import a file by using the "Import File" action. After selecting this action, the user must select a file corresponding to the defined structure and type.

When the file is selected, the system imports the data. The system validates field data types during import, does not validate business logic. If it encounters a workable error during import, the import is not aborted, but the item import workbook line in the error text column shows any problem found during import. You can only re-import the original file to fix this problem (after it has been repaired in the import file).

Validate data #

This action runs a check of required fields, links, and other data validation requirements. At the end of the check, any errors found are written in each row. At the same time, this action is run automatically before the "Use Data" action. In rare cases of unexpected errors, the error may be displayed as a classic Business Central error.

Required fields that must always be filled in for import:

  • Vendor number_- required only if within the template is active control of sales prices, sales discounts, or both_
  • Vendor Item number
  • Base unit of measure

Required fields that must be filled in for import when new items are inserted:

  • Item category code
  • Inventory Posting Group
  • Gen. Prod. Posting Group
  • VAT Prod. Posting Group

Apply data #

This action is applicable only if the CDS is deactivated, if it is activated it is possible to use the data only through later scheduling. The action attempts to use imported data to create/update the corresponding tables.You can run an action only if there is no import error row in the worksheet. When started, the check is carried out first (the "Verify Data" function starts automatically). In case of any error found, the application is cancelled, and the error is displayed in the appropriate line with an explanation. Once the user deletes the error, you can reuse the data again to recheck all the data. In rare cases of unexpected errors, the error may be displayed as a classic Business Central error.

You can schedule data to be used later outside business hours by using the Schedule Application action, which is available in the import worksheet.When you schedule a job, its category is automatically set up based on inventory settings. When CDS is activated, the Item with CRM synchronization is also paused, and only after successful or unsuccessful importing will synchronization be restarted.

Data write #

Data from an import worksheet is written in multiple tables when applied

  1. Items - Except for price list fields, all fields from the main sheet of the import worksheet are written directly to the item table.
  2. Price lists- From the fields designated for price lists a new line in the newly established price list for a specific vendor is created for each line of import worksheet.

The fields designated for price lists

  • For Sales Price Lists
    • Current Sales Price (LCY)
    • Current Sales Price
    • Sales Currency Code
    • Sales Unit of Measure
    • Current Sales Line Disc. (%)
  • For Purchase Price Lists
    • Current Purchase price
    • Purchasing Currency Code
    • Purchasing Unit of Measure
    • Current Purch. Line Disc. (%)

After you create price list lines from an import worksheet, the system is going through existing price lists

  • For the same vendor (through field Item Import Source No.)
  • Which are not the type Price & Discount
  • Which have a button Allow Updating Defaults switched off
  • Which are valid - have a start date before the working date, end date today, later, or unspecified

From these price lists it will copy all lines that are no longer in the new price list (not imported by the import workbook). The price lists found are marked as invalid and closed after copying (the end date is set to one day back from the working date).

If the item category code is changed, the price list lines with the item number that were created by the item category split, will be closed. These price list lines are recreated in correnspoding price lists regarding the new item category code.

  1. Attributes – from Item worksheet through action Related -> Details you can click through the item attributes. These are then uploaded to the item attribute table (clickable on the item card through Item -> Attributes)

Translations – from Item worksheet through action Related -> Details you can click through the item translations. These are then uploaded to the item translation table (clickable on the item card through Item -> Translations)

Data preprocessing #

Delete all lines #

Using this action removes all lines and detail lines in the current worksheet.

Using this action is more efficient then manually deleting all lines.

Add Item Prefix #

When you use this action, the system traces the prefix set up for the item vendor and, if necessary, adds it to the item number in the item import worksheet. If the prefix is not filled in, or the item number already contains the prefix, the system will not make any changes.

Apply Item Template #

When you use this action, the system traces the set item templates on the relevant item categories and applies them to the corresponding lines of the item import worksheet. If the worksheet contains items in the field for which the template is defined, the original value is overridden without notification. If there is no template for the item category, the system does not take any action.

Close Vendor Items not Listed in Worksheet #

This action runs a check for all Items whose Vendor No. are set to the same value as the selected worksheet.

Open Items, that are not listed in the worksheet and have no Inventory, Qty. on Sales Order and Qty. on Purch. Order are closed (property Blocked is set to Yes).

Closed Items that are listed in the worksheet are opened (property Blocked is set to No).

Items can be excluded from automatic closing/opening using the property Skip Item Import Blocking on Item Category card.

This action can be automatically ran after the "Use Data" action by setting the property Close NonListed Items Autom. after Applying Worksheet in the Inventory setup (category General -> Item Import Worksheet).

Calculate sales prices in LCY with Fixed Exchange Rate #

This action traces journal lines with the specified sales price in a foreign currency, where the currency also has a value defined for the "Fixed exchange rate for price list" field in the currency. Based on the information from the item import worksheet and this fixed rate, it calculates the sales price in LCY and writes it to the item import worksheet.

Setting #

Item Worksheet Template List #

Item import worksheet templates form the basis of the entire functionality. There can be any number of templates on the system that are manageable by user. On each template, you can specify its code, name, type of imported file (Excel xlsx and CSV supported variants), separator (for CSV variant), and number of rows from the beginning of the file to skip when imported (file header).

At the same time, you can set up a default currency code for the purchase price in the item import worksheet templates to use if no currency code is specified in the import file.

You can deactivate price and discount import in the template. If neither import is active, you do not have to fill in the vendor number in the worksheet.

In addition, the template settings determine whether prices and discounts are divided into multiple price lists according to the item category when imported.

On this page, an action Item Worksheet Template Setup navigates to the setup page.

Item Import Worksheet Field Setup #

The following options can be set on the new page Item Import Worksheet Field Setup:

  • Import Empty Value - Specifies, whether empty values should be updated. If this check box is selected and the selected column is not filled in the imported file, the value is also deleted from the item data. When importing empty attribute values and translations, the attribute and translation mappings are removed.
  • Import Type - Specifies, whether the value should only be imported into newly created Items, or updated into existing ones as well. The default value is "Import and Update".

File Structure Definition #

There is an "File Structure Definition" action in the Item Worksheet Template Lists. Use this action to set what and in what order the imported file contains. The definition can theoretically contain an unlimited number of columns. For each imported data, you must specify its order (in which column it is in the imported file) and in what structure it should be stored. It is not necessary to list all columns. A supported structure is an item table (then a selection from each field in the item import worksheet), item attribute table (then a selection from existing item attributes) and language table for item translations. Only languages marked as Use for Item Import can be used for import.

Currencies #

A new field "Fixed Exchange Rate for Price Lists" has been added in Currencies. If this field is filled in, it is used in the " Calculate sales prices in LCY with Fixed Exchange Rate". At the same time, when you change this field, the user is asked whether to recalculate all existing valid prices (as of the work date) that were created from the original fixed rate.

Inventory Setup #

There are new fields in Inventory Setup that specify the behaviour of data importing and processing.

  • "Sales Price Source Type"
    • Specifies how to identify the sales price list of prices managed by automatic import.
    • Currently only supported type "Vendor"
      • When you set up as "Vendor" sales price lists are sorted in price list header by vendor number.
  • "Close NonListed Items Autom. After Applying worksheet"
    • If set up, when you apply the item import worksheet, all items from the same vendor are reviewed and traced, and then the items which are not in the currently imported worksheet and closed. If it is not set up, you can manually run this action by using the "Close Vendor Items not Listed in Worksheet" function from the item import worksheet.
  • "Create only for existing Stockkeeping Unit Templates"
    • If this field is active, then inventory units are created only for those combinations for which existing inventory unit item import templates.
  • "Prefix to Item Category"
    • If it is active, then when processing the data (founding/updating the goods), this prefix is written to the item category.
    • If the item category contains a different value, that value is overwritten without warning.
  • "Item Category to Item Discount Group"
    • If active, the item category is written to the item discount groups when the data is processed (item creation/update).
    • If the item discount group contains a different value, that value is overwritten without notice.
  • "Job queue category"
    • If it is filled in, the selected category is set when you create a job queue entry and schedule a workbook to be applied through an action on the item import workbook page.
  • "Use Item Suffix"
    • If it is active, the suffix from the item import workbook is automatically added to the item number when you create it.
    • If it is not active, the suffix field cannot be filled.
  • "Item Suffix Split Character"
    • If filled in, this character is used between the item number and the suffix.
    • Once there is an item card with a suffix, or a record in the import workbook with the suffix, the value cannot be changed.
  • "Default Base/Sales/Purchase Unit of Measure
    • Specifies the default unit of measure in cases where the imported file does not have a unit of measure.

Vendors #

New field "Item No. Prefix" was added to the Vendor card. If this field is filled in, the vendor item number must always begin with this prefix. You cannot change the prefix to a different value if there are already items that do not match the prefix. Before the application of the item import worksheet, you can add a prefix by using the "Add Item Prefix" function.

Items #

New field exists on the Item card

  • "Fixed Sales Prices"
    • If this option is active, sales prices for items with existing prices are not imported. If prices for items do not yet exist, they are imported regardless of this flag.

Item categories #

New fields exist in Item Categories card

  • "Fixed Sales Prices"
    • The change of this flag is written through to individual item cards when prompted by user, which then affects the sales prices import.
  • "Skip Item Import Blocking"
    • If this field is active, items in this category are not automatically blocked when you use the "Close Vendor Items Not Listed in Worksheet" function.
  • "Item Config Template Code"
    • If it is filled in, this template is used when you use the "Apply Item Template" action in the item import worksheet. If it is not filled in, no template will be applied to the item.

Item Imports SKU Templates #

New functionality "Item Imports SKU Templates" was added.This table specifies the item templates used to create new inventory units. Additionally, if the "Create only for existing Stockkeeping Unit Templates" flag is active in the inventory setup. then stock units are created only for the combinations listed in this table.

Added field Vendor Change Notification on Item Card to Inventory Setup #

Vendor Change Notification on Item Card triggers a notification regarding the modification of the vendor on the Item Card. This notification serves as a reminder to users that changing the vendor does not update the vendor's price list for the specific item. The notification will only appear if a valid price list for the item was previously imported. The field, named Vendor Change Notification on Item Card, can be set to True or False to enable or disable the notification functionality.