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:
| Journal | Details |
|---|---|
| Item Journal | Use Excel Importer to import opening entries for new companies in Business Central or perform bulk item adjustments. |
| Physical Inventory Journals | Use Excel Importer to import your physical inventory recording into your physical inventory journal. We have provided more information about this import here. |
| Reclassification Journal | Use 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 Journal | You can use Excel Importer to revalue specific Item Ledger Entries. |
| Output Journals | Used to import output when this is not recorded directly in Business Central. |
| Consumption Journals | Used to import consumption when this is not recorded directly in Business Central. |
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.
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
- 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.
- Import the file containing the actual quantities that are counted.
- Validate the variances using the test report available in the journal.
- If you disagree with the variances, you can check the file, make corrections and re-import the file.
- When you have validated the variances, you can post the journal.
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:
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.
Excel File
Example Excel Mapping Template
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.
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:
Excel File
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.
