Skip to main content

Item Journals

Business Value

Allows you to import Item Journals with item tracking information (lot and serial number information), which is very handy on your initial inventory take-on.

Feature Details

You can find the Import from Excel action in the following journals:

JournalDetails
Item JournalUse Excel Importer to import opening entries for new companies in Business Central or perform bulk item adjustments.
Physical Inventory JournalsUse Excel Importer to import your physical inventory recording into your physical inventory journal. We have provided more information about this import here.
Reclassification JournalUse Excel Importer to import bulk reclassification journals. Many customers use this feature to change expiry dates or inventory to different variants if there are quality issues.
Revaluation JournalYou can use Excel Importer to revalue specific Item Ledger Entries.
Output JournalsUsed to import output when this is not recorded directly in Business Central.
Consumption JournalsUsed to import consumption when this is not recorded directly in Business Central.
info

Output and Consumption journals are only available with the Premium experience in Business Central.

Item Tracking (Lot and Serial Number)

This import supports importing lot and serial tracking details for items that require item tracking.

note

As of Business Central Wave 1 2023, you can now update tracking information directly in the journal based on a setting specified on the journal batch. This app supports the new method and the old method.

Document Numbers

You can import your document numbers or specify a number series for the batch and let Business Central assign the numbers for you.

Physical Inventory Journal

Many organisations record the count details in Excel when performing a physical inventory count. The source of this data could either be from manual entry or scanning devices that are not integrated into Business Central. Alternatively, the count source might be external - for example, when a third party manages your inventory in a specific location. If the third party performs a stock count and provides you with a file, you could import this file to check to update the physical inventory.

High Level Process

  1. In an empty Physical Inventory Journal batch, use the Calculate Inventory action to calculate the expected inventory. Here is a link to the Microsoft help.
  2. Import the file containing the actual quantities that are counted.
  3. Validate the variances using the test report available in the journal.
  4. If you disagree with the variances, you can check the file, make corrections and re-import the file.
  5. When you have validated the variances, you can post the journal.
caution

It would be best to avoid processing transactions while the count is in progress - specifically for items that have item tracking, as the program uses the expected inventory to work out which lots/serials must get adjusted out of/into inventory.

Handling Items not on Inventory

Suppose an item not in inventory gets identified during the counting process. Excel Importer can handle this scenario by adding additional lines for items in a count file but not already in the journal. When this happens, you will get the following message:

ItemJournal-PhysInventoryExcelFile

Select:

  • Yes to add only the item specified in the message.
  • Yes to all to add all items from the file that are not already in the journal.
  • No to skip the item specified in the message.
  • No to all to skip all items found in the file that are not already in the journal.
  • Cancel if you want to stop the import and check the file.

Physical Inventory Journal Example 1

The page below shows a journal created after performing step 1 above.

ItemJournal-PhysInventoryExcelFile

Excel File

ItemJournal-PhysInventoryExcelFile

Example Excel Mapping Template

ItemJournal-PhysInventoryExcelFile

note

When setting up a new template, you will notice a relatively short list of fields available for selection. This is because the journal is pre-populated, and we only want to update the physical inventory quantities.

Result

After the file gets imported, you will notice the following:

  • The physical quantities have been updated on all lines previously in the journal.
  • The lines found in the file that were not previously in the journal have been added.
  • The item tracking has been updated as required.

ItemJournal-PhysInventoryExcelFile

Physical Inventory Journal Example 2

This example follows the previous example. The file has slightly changed quantities, and the expected quantities in Business Central are different to start with. After performing step one, we have the following:

ItemJournal-PhysInventoryExcelFile

Excel File

ItemJournal-PhysInventoryExcelFile

Result

After the file gets imported, you will notice the following:

  • The physical quantities have been updated on all lines that were previously in the journal.
  • The lines found in the file that were not previously in the journal have been added.
  • The item tracking has been updated as required. Notice how there are now two lines for item 1000. This is because one of the lots increased in quantity and one decreased.

ItemJournal-PhysInventoryExcelFile

See Also