In today’s post, we cover the ability to export and import Physical Inventory Order recording lines in Business Central. We’ll start by offering a brief explanation of physical inventory orders, for those who aren’t familiar with their function. We’ll then show you how to perform the export and import and address the hurdles you might meet along the way.
What are physical inventory orders?
In Business Central, physical inventory orders are a newer alternative method of performing stocktakes to the Physical Inventory Journal. They work by having a base record, the physical inventory order, and a series of recordings. These recordings split up the items or areas to count stock in. Each recording is filled in appropriately and once finished, all the data is collated in the physical inventory order. Afterwards, the user in charge of the order must finish and post it. The act of posting will amend stock quantities accordingly. For a more detailed explanation of physical inventory orders, be sure to check out our guide.
Exporting a Phys. Inventory Recording record
After you create a Physical Inventory Order and populate a subsequent recording with the items you wish to count, the user has the option to input quantity data manually onto the system or perform an export.
There could be various reasons you wish to export a recording instead of printing it. If you count and potentially recount stock, exporting the recording lines reduces the amount of paper you get through. Secondly, by exporting and not printing out documents, you mitigate the risk of bad handwriting affecting the accuracy of the count. Lastly, you eradicate the risk of people losing their sheets and their data!
Exporting the recording
To export recording lines, click the action of the same name, in the ribbon:
You’ll download a .txt file with the recording line details on it. It should look a little like this:
Note, ‘X’ and ‘Y’ are items.
It’s imperative to say here that when you click Export Recording Lines, the system only exports a select few of the fields listed. It is also does so in a specific order, irrespective of any personalisation or customisation you’ve made. So as you can see below, I’ve added Time Recorded as the third field and moved the others around:
However, when I come to export the lines, it looks like this:
As you can see, Business Central doesn’t account for how I’ve laid the page out. It formats it in a consistent manner. Whilst consistency is good, it would be nice if they made this clearer!
After exporting the .txt file, the first thing you’re likely to be looking for is where to input the Quantity value. Most of the field mappings are straightforward as you can associate the exported values with the field names. However, this isn’t the case for all. In fact, as I mentioned earlier, the system doesn’t export all of the fields. This means when it comes to importing the file, you have to adjust the file to accommodate for these. This is because the system will error if it’s missing certain bits of information.
Importing the file back in
Now you have an exported .txt file, let’s run through the process of adjusting the data and importing the Physical Inventory Order Recording lines back into Business Central.
The order of fields when it comes to importing
When you import the file back into Business Central, this is the order which you must lay out the data in, from first to last:
- Order No.
- Recording No.
- Line No.
- Item No.
- Variant Code
- Location Code
- Bin Code
- Description (Item description)
- Description 2
- Unit of Measure
- Shelf No
- Person Recorded
- Date Recorded
- Time Recorded
One thing you’ll notice here is that there are more fields here than were exported. So, you must import more field values, even if they are blank, than you originally exported. Where there are additional fields to add, you must do this using commas and speech marks. Where a value is blank, simply put two speech marks back-to-back. Below is an example of a completed file:
If your import is successful, your Physical Inventory Order Recording lines will mirror the data you input:
The rest of the process is standard. So when your happy with the data, click ‘Finish’ on the recording like you would normally.
That marks the end of the process. With that said, I thought it’d be useful to run through some of the typical error messages users come across with this functionality, before closing out.
Errors when importing physical inventory order recordings
Hopefully you shouldn’t come across any errors if you follow this guide through properly. However, it’s worth highlighting the types of messages you might encounter. When it comes to importing the Physical Inventory Order recording lines back in, you may get a message like this:
You may also get a message like this:
Whilst the first message isn’t clear, the second paints a better picture as to what’s wrong. Both of these are errors relating to the alignment of data to particular fields. The second one is suggesting that PCS isn’t compatible with a particular field, one that is expecting a decimal. Obviously in that instance I mistakenly put a unit of measure in the wrong space.
I want to credit user Yolon2023 for explaining this in a Microsoft forum on importing and exporting physical inventory recordings. He helped me initially understand why these error messages occurred.
To get around this, re-export the recording lines. Then follow the bullet point layout of how the fields should be laid out. Remember that you need to add more fields to the .txt file than you started with when you originally exported the file.
Whilst slightly more complicated in its current form than it perhaps should be, I am sure this will be an area Microsoft will continue to develop over time. I should mention, at the time of writing this, I am using version 21 of Business Central. As it stands, we’d recommend managing your Physical Inventory Orders on the system, without utilising the export and import functionality.
As always, we want to thank you for reading! Hopefully this post has ironed out some of the confusion you might have. If there’s an area of Business Central you’d like to see us cover in future guides, make sure to get in contact with us. To never miss one of our posts, make sure to follow us on LinkedIn.