Sage 500 Inventory Management – Bulk Item Import

Sage 500 Inventory Management – Bulk Item Import

Today, we will talk about configuring inventory management in Sage 500. Specifically, importing items and configuring them in bulk.

One of the common tasks of setting up a new warehouse involves configuring what items are part of the warehouse. Typically, one would just use the maintenance screens to add the item and then follow it up with adding the inventory to associate the item with a warehouse.

IM Import Utility

This simple task becomes challenging when you have many items to import and configure. Sage 500 provides a good option with the IM Import utility to import items and configure them in bulk. Before you start the import, you do need to prepare the data.

This utility imports information in a structured or delimited file format and expects the data to match the prescribed layout. Also, you need to understand the steps involved in importing items. Finally, we will go through some of the limitations of the utility so you know what you can expect, before you decide to use it.

Data Preparation

Before you can start the import, you need to know what needs to be imported. There are three steps involved in the configuration

  • Import the items to the item table
  • Import the items and tie them to the warehouse
  • Set the starting balances of the items in the warehouse

Each of the steps will require you to prepare one or more data files that follow the Sage 500 IM Import Utility format as defined in the help section.

Prepare Item Data File

The first file you will need is the items file. This files contains fields that will define the item in the Maintain Items screen. However, if your items are already defined and all you need to do is setup a different warehouse with the items, you can skip this step.

The item setup involves setting up the following six categories of information records for the item. Each record type has a specific record type identifier.

  • Item Record
  • Landed Cost Factor Record
  • Item Substitution Record
  • Item Unit Of Measure Record
  • Kit Component Record
  • Vendor Item Record

Once you have prepared the item data file with the associated record types, you can import it using the IM Import Utility – Item option This option allows you to first test and verify the integrity and structure of the import data file. Once satisfied, you can the import the items.

A word of Warning: The IM Import Utility is designed to import new items. It cannot update existing items you already have defined. So be careful and make sure that the data is reviewed and each item is configured the way you want it.

Prepare Inventory Data File

The inventory data file is used to map an item to warehouse & then setup inventory bin information. The following two record types can be included in the data files:

  • Inventory Records
  • Inventory Bin List Records

Once you have prepared the inventory data files, you can import it using the IM Import Utility – Inventory option. This option, just like the other options allows verifying the import data files before importing them.

Prepare Starting Balances Data File

The starting balance data file is used to setup starting balances of newly imported items. Beginning balance import file include the following three type of records:

  • Batch Records
  • Transaction Records
  • Bin Distribution Records

The file, once setup can be imported similar to the other record files.

Error Reporting & Correction

During the import of the files, the first step is to check the data file. This performs the transaction in temporary tables. Any issues with the data files are then reported and can be printed out as part of the error report. You can review the error reports and correct issues. Once you are satisfied that no errors exist, you can import the files into the database.

In Conclusion

Setting up inventory information in Sage 500 can be done manually, one item at a time or in bulk using the IM Import utility. If you need assistance with this or any other Sage 500 related configuration, automation, integration or other projects, we can help.

No Comments

Sorry, the comment form is closed at this time.