By Clay Li on Monday, 24 July 2017
Category: Tutorial

Data Upload Part 1: Inventory Setup

​This blog series demonstrate the data setup through uploading data from excel templates.  Here are some background about excel templates and data upload:

This is the first part of the data upload tutorial. Inventory setup is covered. This blog serves as the detailed walk-through for the steps defined in Calem Setup 3: Inventory Setup. 


Step 1. Upload Manufacturer and Vendors 

Manufacturers and Vendors are companies (table name: cm_company) in Calem. They have the following attributes:


If both vendor and manufacturer flags are set, the company is both a vendor and a manufacturer. A default manufacturer site or vendor site of the same name as the company is created.

Two excel files are included here:


Here are the processes to use sample and generated templates:


Step 2. Upload Items and References

Two excel templates are included for items. Start with the sample template, and use the generated template when needed.

Use item reference template to define manufacturer and/or vendor numbers for the item number you established.


Step 3. Upload Storerooms and Locators

Storerooms are the places where you store parts. Locators are the codes of aisle, shelf and bin inside a storeroom. Upload storerooms first, then locators.


Step 4. Upload Site Inventory

Site inventory defines the items tracked in sites. Upload site inventory first. Two excel templates are included:

 Step 5. Populate Stock

There are two options to populate stock: a) Physical Count transaction; b) receive transaction.

5.1 Physical Count Transaction

Physical count transactions may be used to build out stock with item quantity and the locators where the parts are physically stored. If you are uploading serialized inventory, use the serialized template below. Serialized inventory are for items that are tracked by their serial numbers. Unless required by your business, use the first template (not the serialized template).

 5.2 Receiving Transaction

Receiving transactions may be used to build out stock with item quantity, unit price, and the locators where the parts are physically stored. There are limitation to receiving transaction uploading: a) Serialized inventory receiving is not supported; b) Receiving to other objects (work order, asset and cost center) are not supported.

 Step 6. Unit Price

Unit price can be imported by an excel file. The unit price upload does not support store-based pricing. Unit price uploading is not necessary if receiving transactions are used to populate the stock. 

Here is a sample: Inventory_Avg_Unit_Price.xls. You may export site inventory list (Inventory | Report | Site Inventory List) and morph into into the sample unit price file:

 Step 7. Vendor Prices
Vendor prices setup include vendors, vendor authorization for sites, and items supplied by vendors. See the blog Vendor Setup for more information. Vendor and part prices can be uploaded from excel files - see this blog.

 Additional Resources