Articles

Importing Stores Inventory Items

This document describes the procedures for importing stores inventory information into Escape prior to system startup. Districts buying Escape generally have existing inventory records on some computerized format.

This write-up discusses plans for converting those records to import into Escape from their existing system. Source conversion files are commadelimited ASCII files used to convert your data to the Escape format. The rest of this document outlines the rules and format of the records to be imported. Also described is the program used to import stores inventory data, IMPRTINV.EXE.

NOTE: The IMPRTINV.EXE was changed on 4/22/99. This documentation has been updated to reflect the latest changes to this utility. The fields TRACK_ITEM and CURR_ISSUED were removed.

Stores Inventory Import Rules

During the conversion process, the records may be changed to take advantage of several circumstances. The person(s) that will be using the system should be thinking about how they want the records to "look" when they are loaded into the Escape system. What's wrong with the way your information is stored in the existing system? What would you like to change?

When creating the source file for import, follow the following rules:

• The file must be in ASCII, comma delimited format. NO packed or binary information!
• Date Fields are to be YYYYMMDD format, eight characters in length. For example, December 1st 1990, would be 19901201.
• String (text) fields must be enclosed in quotes.
• Real fields must include the decimal. For example, if the order price has been defined like this:
  
  • ORDER_PRICE    real number     99999.999
  • Then a price of $3.435 would be formatted like this: 00003.435
• Each record must end with ASCII Carriage Return (CR) and Line Feed (LF) characters. Trailing spaces can be stripped before placing the CR and LF.

TIP: Call us for assistance in matching your data to our format.

Conversion Considerations

Item Description

This field describes the inventory item in detail. It can contain up to ten lines of description and each line is 40 characters long.

Sort Name

If sort name is blank, then it will be set to the first twelve characters of the Item Description.

Category, Warehouse, Location, Department, Bid Categories

Most mainframe systems use unfriendly numbering schemes for these fields. Escape allows alphabetic codes for these fields. For example, the Category, Packaged Supplies may have a code of 0934 or some other number that with Escape could be PS. Why not convert all of these to alphabetic codes that relate to the items? GS for General Supplies, etc. You might even fill in a default value for all items, using the value for the category containing the most items.

Quantity

If you will be importing the on hand quantity, you must perform mass update procedures to create BALANCE FORWARD transactions in Escape. These mass update procedures are performed in the Purchasing module after the import is complete. Please see the section If You Import Quantity for details.

WARNING: If you do not perform the mass update procedure, when you recalc, all quantities will be deleted.

Department Code

Used to organize items by the department that orders and issues them. For example, General Stores, Food Service, Maintenance. This allows you to limituser access by department. If your current system does not have department codes, set this field value to PUR.

Warehouse.

If this field is blank, Escape defaults it to WHS, Warehouse.

Bid Categories

Used for specialized bid reports in Escape. You may or may not have bid categories. If not, leave them blank.

About Issue and Order Units and Ratios

What are these? A classic problem in inventory management is buying in one quantity and issuing in another. You purchase by the case, and issue by the ream, etc. Where this causes havoc is discussing the whole thing with your sites - and with your warehouse!

Escape's inventory records store both issue and order quantities, based on the order to issue ratio field. An example: You order paper by the case, and each case contains 12 reams. In the ORDER:ISSUE RATIO field, the order quantity of 1 equals an issue quantity of 12. The system can then always report how many you have on hand in both order and issue units of measure.

Now, how does ORDER:ISSUE RATIO relate to the fields - ORDER UNIT OF MEASURE and ISSUE UNIT OF MEASURE. While Escape uses ORDER:ISSUE RATIO to do the calculations, these two fields are actually descriptions of what you order and issue. You may enter any description of the unit of measure in this field (up to a maximum of eight characters) which you feel will describe what you order and issue. Going back to the above paper example, you would probably want to enter "CASE" in the ORDER UNIT OF MEASURE and "REAM" in the ISSUE UNIT OF MEASURE. This makes it very clear to everyone involved how you order and issue paper.

NOTE: If the order:issue ratio is blank, Escape will set it to 1:1. If order price is blank and there is a issue price, the order price is set to the issue price multiplied by the Issue/Order Ratio. For example, the issue price is $12 and the ratio is 10:1, then the order price will be set to $120. Conversely, if issue price is blank and there is a value in order price, the issue price is set to the order price divided by the Issue/Order Ratio. For example, if the order price is $10 and the ratio is 1:1, then the issue price will be set to $10.

Import File Layout

The fields below are listed in the order required, with the associated length. You may utilize any or all of the fields specified below - when not using a field, simply delimit with a comma.

For your convenience, we have a column designating the column heading if you are creating or editing this file in Microsoft Excel.

Sample Import Data File

To download a sample standard format import data file containing 1 record, right click on the link and choose Save as. IMPORTIN.CSV (Notepad) or IMPORTIN.XLS (Excel)

The record contains a rubber band inventory item, including order, unit and issue information. Using this file as an example, you can build your own import file. You may wish to edit the sample file, then import it into your database as a test.

How to Run the Import Process

Escape provides a program, IMPRTINV.EXE, used to import stores inventory data using the formats described above. To use the program:

1. Copy IMPRTINV.EXE into the \ESCAPE\UTIL subdirectory.
2. Copy the data file into the \ESCAPE\PUR subdirectory.
3. Rename the data file to INVITEMS.CSV
4. From the \ESCAPE\PUR directory, run the program by typing:

   \ESCAPE\UTIL\IMPRTINV.EXE

   The program will create stores inventory records for each entry in the data file.

If You Import Quantity ...

If you import the QUANTITY into Escape, you must create a BALANCE FORWARD transaction, so that Escape will have a "starting" quantity on hand in case you need to recalculate the on hand quantity. To create balance forward transactions for all imported items, perform the following steps:

Step 1 - Copy On Hand Quantities to PHYS CNT field

Go to the menu choice View Inventory Items. Pull up a list of all items you would like to work with now. Then, choose the menu choice. For each of the items in the list, it will copy the contents of the field ON HAND QUANTITY to the field PHYS CNT. So, obtain a list of inventory items on your screen, then choose Copy on Hand Qty to Phys Cnt menu choice (proc #123). When you choose this menu choice, a small dialog box will appear.

Press ESCape to return to the list or press F3 to begin the process. If you press F3, the number of items that are being read and updated appears on the bottom line of your screen. When the process is finished, a message will appear on your screen. Press ESCape to return to the list.

Step 2 - Edit Quantities in the PHYS CNT Field

If you find that there are any quantities that were incorrect in the import, you can change them now by entering the correct quantity in the PHYS CNT field at the bottom of Page 1 of the inventory item. Press F3 to save each item and return to the list.

Step 3 - Mass Update Inventory Items in Escape

Escape has a menu choice to mass update inventory items on your screen list. It is important to understand that the batch update process updates every item in your screen list, copying the value from the PHYS CNT field, even if the value is "0." In the case it is "0," the new on hand quantity of the item will be "0."

With only the items on your list, that you have made sure the PHYS CNT field matches the true quantity on the shelf, choose the menu choice Update On Hand Quantities (proc #122). A dialog box will appear on your screen. Enter in the effective date of the count and press F3 to start processing.

As the items are processed, a count will appear on screen. When the process is complete, a message will appear. Press ESCape to return to the list.

To view the balance forward transaction created by the process in Step 2, edit an inventory item and choose Transactions or press F9. The most recent transaction appears at the top of the list and that should be the balance forward transaction. A balance forward transaction will be created for each item in the list.

• Print this page
• Email a link