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 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.
Settings
Check for Empty Batch
This setting specifies that the batch you're importing into must be empty before the file can be imported. It is beneficial when using the API to import and post a journal, as it helps prevent accidentally posting lines that were not part of the import.
The app does not raise an error if the journal contains empty lines—it deletes them instead. It uses Business Central's standard logic to determine whether a line is empty.
Post after Import
This setting lets you specify whether the journal should be posted automatically after importing.
| Value | Description |
|---|---|
| No | The journal will not be posted automatically. This default option allows you to manually review and post the journal. |
| Yes – Save Lines on Error | The journal will be posted automatically after import. If an error occurs during posting, the entire posting is rolled back, but the imported lines remain in the journal. You can then fix the errors and post manually. |
| Yes – Remove Lines on Error | The journal will be posted automatically after import. If an error occurs during posting, the entire import is cancelled, and no lines are saved in the journal. |
| Yes – Line by Line | The journal will be posted automatically after import. The app first attempts to post the entire batch. If an error occurs, it will try to post each line individually. Lines that can be posted will be posted; lines that cannot will remain in the journal for review. A feedback message will indicate how many lines were successfully posted and how many were not. Use this setting when you expect most lines to post and want to ensure as much as possible is posted, even if some lines fail. |
- Importing large journals (e.g., thousands of lines) may cause record locking for other users. Selecting Yes – Save Lines on Error helps reduce this risk. Smaller imports are less likely to cause issues.
- If you post the journal automatically, the app will delete any empty lines before importing. This helps prevent posting errors. The app uses Business Central's standard logic to determine whether a line is empty.
- Use the Check for Empty Batch setting to ensure that only data from the import file is posted.
- Select Yes – Remove Lines on Error when integrating via the API to ensure a clean import and avoid having lines left from a previous import that failed to post.
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.
