This blog series demonstrate the data setup through uploading data from excel templates. Here are some background about excel templates and data upload:
- An excel data template is generated for each table in Calem. Customers can download the archive of all the templates at http://support.calemeam.com/view.php?id=4046. A customer account is required to download the templates.
- The Data Upload Guide has information for some tables that should not be uploaded including transaction tables that are generated in Calem. Customers can reference the Data Upload Guide at http://support.calemeam.com/view.php?id=3983. A customer account is required to download the Guide.
- The excel templates can be used to create new data or update existing data. So, you can use the same excel file to create data and make changes by uploading the file more than once.
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:
- Class (class_id): Vendor
- Vendor flag (is_vendor): 1
- Manufacturer flag (is_manufacturer): 1
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:
- The sample excel template: cm_company.VendorManufacturers.xls.
- The generated excel template: cm_company.xls.
- The sample excel includes some fields of the company templates.
- The generated template includes all the standard fields in Calem.
Here are the processes to use sample and generated templates:
- Start with the sample template. Populate the fields for each manufacturer and vendor.
- If there are fields you need to add that are not in the sample template, search the fields in the generated template. If found, copy them to the sample template and populate the fields. Otherwise, contact your Calem support for help.
- If you have added custom fields, just add the custom fields to the sample templates and populate the custom fields.
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.
- The sample excel template for global items: cm_in.Global_Items.xls.
- The generated excel template for global items: cm_in.xls.
Use item reference template to define manufacturer and/or vendor numbers for the item number you established.
- The sample excel template for item references: cm_in_ref.sample.xls. The generated excel template has the same fields (so, it is not included).
- The excel template to upload equivalent items: cm_in_eq.Equivalents.xls.
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.
- The sample excel template for storerooms: cm_in_store.Inventory_Store_Warehouse.xls
- The sample excel template for locators: cm_in_locator.Store_Locators.xls
Step 4. Upload Site Inventory
Site inventory defines the items tracked in sites. Upload site inventory first. Two excel templates are included:
- The sample excel template for site inventory: cm_in_site.Site_Inventory.xls.
- The generated excel template for site inventory: cm_in_site.xls
- The sample excel template for physical count transaction upload: Inventory_Physical_Count.001.xls.
- Menu path: Inventory module | Transactions | Import Physical Count
- The sample excel template for serialized inventory physical count upload: Physical_Count.001.SerializedParts.xls.
Step 5. Unit Price
Unit price can be imported by an excel file. 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:
- The report of Site Inventory List should include three columns: Item, Site and Avg. Price().
- Export the report to an excel.
- Remove all columns excepting the 3 columns above.
- Rename the three columns as below:
- Item to Item No. (in_no)
- Site to Site (site)
- Avg. price() to Unit cost () (unit_cost_c)
- Here is the updated excel file that you can copy column names from.
Step 6. 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
- Vendor Setup
- Bulk data update via export and import
- Data Upload Part 2: Asset and Location
- Data Upload Part 3: Preventive Maintenance
- Data Upload Part 4: Vendor and Part Prices
- User Guide and Admin Guide (customer account required)
- Calem Enterprise Training Site
- Calem Enterprise Blogs
- Calem Enterprise demo