Show TOC

 Fields for Import

 

You can import inventory records from Microsoft Excel to the following documents (the number for each document type is used below for reference)

  • [1] Inventory counting documents

  • [2] Inventory posting documents

  • [3] Inventory opening balance documents

To import your counting results from Excel into SAP Business One, you must define the Excel fields based on a template, which is defined in the Import from Excel window (for more information, see Importing Counting Results from Microsoft Excel). These fields must follow certain rules; otherwise, the import will fail.

Note Note

The fields for importing into inventory posting documents are the same for a single counter.

End of the note.

The table below lists the fields available for import and the rules:

Document

Field

Description

1 & 2 & 3

Item Code

  • Mandatory

  • Requirement: Exists in the company.

Note: Whether or not inactive items can be imported depends on your setting of the Allow Inclusion of Inactive Items in Document checkbox in the Document Settings window. For more information, see Document Settings: Per Document Tab.

1 & 2 & 3

Item Description

Optional

Note: If the item description does not match the item code, the document records the specified item description; nevertheless, the master data of the particular item is not updated with the specified item description.

1 & 2 & 3

Warehouse Code

  • Mandatory if the warehouse is not managed by bin locations.

    Optional if the warehouse is managed by bin locations.

  • Requirements:

    • Exists in the company.

    • Is assigned to the item.

    • Is active.

1 & 2 & 3

Bin Location Code

  • Mandatory if the warehouse is managed by bin locations.

  • Requirements:

    • Exists in the company.

    • Is active.

    • Matches the warehouse if the warehouse code is defined.

1 & 2

Counted Quantity

Optional

Requirements:

  • The field value must be a positive number or zero. If you keep the field empty, it equals zero.

  • This field is the UoM counted quantity for a UoM item.

Note: The value is subject to the following display settings on the Display tab of the General Settings window:

  • Decimal place for quantities

  • Separator (decimal separator)

  • Thousands separator

3

Opening Balance

Optional

Notes:

  • Although you can import zeroes for this field, you will not be able to add the document if the field value remains as zero.

  • The value is measured in the inventory UoM.

  • The value is subject to the same display settings as the Counted Quantity value.

2 & 3

Price

  • Optional

  • Requirement: The value must be positive or, if the system setting allows, zero.

    The value can be zero only if the corresponding checkbox is selected on the respective Per Document tab of the Document Settings window:

    • Inventory posting documents: Allow Inbound Posting with Zero Price

    • Inventory opening balance documents: Allow Positive Inventory Opening Balances with Zero Price

Notes:

  • The value is subject to the following display settings on the Display tab of the General Settings window:

    • Decimal place for prices

    • Separator (decimal separator)

    • Thousands separator

  • If the total variance (counted quantity - in-warehouse quantity) or total opening balance is positive or equal to zero, the system takes an empty Price field for zero.

  • If the total variance or total opening balance is negative, the price value in the Excel file is ignored, with only one exception (explained below). Instead, the system displays an estimated value in the Actual Price field before you add the document, and this value may not be equal to the current item cost. After you add the document, the field is updated with the accurate item cost and the correct total inventory value is posted to the journal entry.

    Exception (Relevant only to inventory posting documents): For items managed by the serial/batch valuation method, if both issuing and receiving transactions exist in a row (for example, some item units are issued while some other item units are received into the bin location), you can edit the Price field. However, this price is used for receiving transactions only; issuing transactions still use the item cost for valuation.

  • If the import template does not have the Price field, the price source selected in the header of the document is taken for the price.

2 & 3

Price Currency

  • Optional: If the field is blank while the price is specified, the local currency is taken as the price currency.

  • Requirement: Exists in the company.

1 & 2 & 3

Bar Code

  • Optional

  • Requirements:

    • Is linked with the specified UoM code.

    • Is linked with the item's inventory counting UoM code if no UoM code is specified.

1 & 2

UoM Code

  • Optional

  • Requirements:

    • Exists in the company.

    • Belongs to the UoM group that is assigned to the item.

1 & 2 & 3

Batch Number

Optional

Note: If the item is managed by batches, but you have not specified the batch information, you cannot add inventory posting documents or inventory opening balance documents.

1 & 2 & 3

Serial Number

  • Optional, and you can select only one of the three numbers for import.

  • Requirements:

    • If your company requires one of these numbers to be unique for the item, you must specify the required number and the number must not be repeated. This requirement is subject to the setting of the Unique Serial Numbers by field (Start of the navigation path Main Menu Next navigation step Administration Next navigation step System Initialization Next navigation step General Settings Next navigation step Inventory End of the navigation path). For more information, see General Settings: Inventory Tab.

    • Different team counters cannot select the same serial number, manufacturer serial number, or lot number for the same item. (This restriction does not apply to individual counters.)

Note: If the item is managed by serial numbers, you can add the inventory posting document or inventory opening balance document only if each unit of the item is assigned a number - no more, no less. However, missing numbers do not prevent you from adding the inventory counting document.

Manufacturer Serial No.

Lot Number

1 (multiple counters)

Counter Type

  • Optional

  • Values:

    • Employee

    • User

  • Requirement: If the same counter ID exists for an employee and a user, you must specify this field.

1 (multiple counters)

Counter ID

  • Mandatory

  • Values:

    • User code

    • Employee No.

  • Requirements:

    • Exists in the company.

    • Is active if the counter is an employee.

Note: The counter can be a locked user.

1 (multiple counters)

Individual / Team Counter

  • Optional

  • Values:

    • T (for team counters)

    • I (for individual counters)

  • Requirements:

    • The value of each counter must correspond to your specification in the Inventory Counting document. For example, if you specify I for a team counter in a row, the data in this row is not imported.

    • If a counter is simultaneously an individual counter and a team counter, you must specify this field.

3

Account Code

  • Mandatory

  • Requirement: Already exists in the company.

More Information

Importing Counting Results from Microsoft Excel

Example of Preparing Import Data

Inventory Counting Window

Inventory Posting Window

Inventory Opening Balances Window