Export and Import Physical Inventory Order Recordings

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:

This image shows the two key actions we will discuss in this post: Export and Import Recording Lines on a Physical Inventory Order Recording.

You’ll download a .txt file with the recording line details on it. It should look a little like this:

This image shows a .txt file, which happens after clicking Export Recording Lines on a Physical Inventory Order Recording.

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:

This image displays how I've adjusted the layout of fields on the Phys. Inventory Order Recording page. I've done this to show that whatever changes I make to the layout here, it will not alter how the system formats and exports the data. It will always layout the fields in the same manner, regardless of what you do.

However, when I come to export the lines, it looks like this:

Following on from the previous image, this displays how the fields are not exported in a way to match how I've laid them out in Business Central. They are exported in the same way each time, regardless of any personalisations or customisations you make in the system.

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
  • Quantity
  • 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:

This image displays the format a user must import a Physical Inventory Order Recording line back into Business Central in. I've used commas and speech marks to separate and identify field values.

If your import is successful, your Physical Inventory Order Recording lines will mirror the data you input:

This image is the result of importing the Physical Inventory Order Recording back into Business Central. The recording lines have their quantity value added and have the Recorded checkmark ticked.

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:

This image displays the first of two error messages I am showing, which can occur when you click Import Recording Lines. This message says "The Element <Quantity> is expected by Min Occurs value: Once. Element received: <PhysInvtRecordLine>".

You may also get a message like this:

This image shows the second error message "The value PCS can't be evaluated into type Decimal". Again, this occurs when you try to import Physical Inventory Order Recordings into Business Central without formatting the .txt file appropriately.

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.

Closing remarks

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.

Leave a Comment

Scroll to Top