NOTE: Inventory Manager (DC) release coming soon. If devices have auto updates enabled, you'll get a version mismatch with your console app, forcing you to upgrade the console app. Disable auto updates on all devices. See documentation Here

Start a conversation

Import Manager

Overview

The Import Manager allows you to import information from external files (such as Excel spreadsheets) to tables in SalesPad by creating an import definition and a field mapping. For example, Import Manager can be used to import 3rd party catalogs into inventory and customer special pricing into the customer card.

NOTE: Microsoft Excel spreadsheets are preferred over CSV files.

Usage
Import Definitions

In Import Definitions, Click the Add button and, after assigning a name to the import definition, select the desired table.

These are the tables available in Import Manager:

  • Alternate Item
  • Contact
  • Custom Cost
  • Custom Price
  • Customer Item Number
  • Inventory Adjustment
  • Inventory By Loc
  • Item List Price
  • Item Master
  • Item Price List
  • Item Promo
  • Item Promo Detail
  • Item Promo Offer
  • Prospect (CRM)
  • Purchase Line Item
  • Sales Line Item
  • Vendor Item
  • Service Transfer
  • Service Transfer Line
Field Mappings

Once the definition has a table assigned to it, the user can proceed with the field mappings. This process is required to map a table’s fields (left column) with the user’s worksheet’s fields (right column). Note that field names do not have to match.

All the fields that are defined by the user in the mappings have to be present in the spreadsheet. On the contrary, the spreadsheet could have extra columns that are not defined in the mappings.

Note: The Line_Seq field must be specified within the Excel document, starting with 16384, and adding 16384 for each subsequent line item.

  • Alternate Item
    • Item_Number
  • Custom Price
    • Customer_Num
    • Item_Number
    • Qty_From
    • UOfM
    • Begin_Date
  • Inventory Adjustment
    • Item_Number
  • Inventory By Loc
    • Item_Number
    • Location
    • Item List Price
    • Item_Number
    • Item Master
    • Item_Number
  • Item List Price
    • Item_Number
  • Item Master
    • Item_Number
  • Item Price List
    • Item_Number
    • Currency_ID
    • Price_Level
    • UOfM
    • To_Qty
  • Sales Line Item *only for Import Manager. Does not apply to the Sales Line Excel Import plugin
    • Component_Seq_Num
    • Line_Num
    • Sales_Doc_Num
    • Sales_Doc_Type
  • Vendor Item
    • Item_Number
    • Vendor_ID
    • Item_Vendor_Type
  • Purchase Line Item
    • PO_Number
    • PO_Type
    • Job_Number
    • Line_Seq
  • Prospect
    • Prospect_Name
  • Customer Item Number
    • Item_Number
    • Customer_Num
  • Service Transfer
    • Order_Document_ID
  • Service Transfer Line
    • Order_Document_ID
    • Line_Item_Seq
Workbook

SalesPad Import Manager allows users to import data of the following file types:

  • CSV files (.csv)
    • The delimiter used in the file needs to be specified in the Import Definition
  • Excel 97-2003 Workbook (.xls)
  • Excel Workbook (.xlsx)
  • Excel Binary Worksheet (.xlsb)
  • Excel Macro-Enabled Worksheet (.xlsm)

The spreadsheet should be set up so that the first row contains the columns, with the subsequent rows to be the data. There are no restrictions on how the sheet should be named. Note that if the workbook contains more than one sheet, the Import Manager will read data only from the first. Once the Import Definition, Field Mappings, and Excel spreadsheet are set up, the user can proceed with the import by clicking Run. A summary window will show the user the results of the import, and which rows (if any) could not be imported.

Tables
Alternate Items

The Alternate Items feature allows users to import 3rd party catalog items onto the Alternate Items tab of Inventory Lookup. The user has the option to clear the table from any existing data before proceeding with the import. More step-by-step information on using Import Manager can be found in the SalesPad 3rd party Catalog and Import Manager document.

Custom Price

Custom Price allows users to add special pricing to items, which is usually defined in the customer card. Again, the user has the option to clear the table from any existing data before proceeding with the import.

Inventory Adjustment

Inventory Adjustment allows users to modify inventory without creating purchase orders. This import creates GP Item Transaction Entries that need to be posted afterward. The import creates one Transaction Entry per row. It is highly suggested that the user assigns a batch number for the import to ease the posting process.

Required fields for this table depends on the company’s GP setup and the type of item the user is importing. For example, if the company does not run multi-bin, the import definition does not need to have Bin. If the imported item is serialized or lot-tracked, the excel table must have values for Item_Tracking_Option and Serial_Lot.

These are the possible values for Item_Tracking_Option (with their relative GP numerical representation):

  1. ‘None’
  2. ‘Serial Numbers’
  3. ‘Lot Numbers’
Inventory By Loc

Inventory By Loc allows users to add or adjust order points/quantities, levels, and policies for inventory items into GP. This is usually done in the "Item Resource Planning Maintenance" window in GP, or the Item Maintenance > Vendor Information screen in SalesPad.

Note: There are some fields that require the data in form of text value instead of the GP numerical representation. These are the values with their relative numerical representation:

Replenishment_Level
  1. “Order Point Qty”
  2. “Order-Up-To Level”
  3. “Vendor EOQ”
Order_Policy:
  1. “Not Planned”
  2. “Lot for Lot”
  3. “Fixed Order Qty”
  4. “Period Order Qty”
  5. “Order Point”
  6. “Manually Planned”
Replenishment_Method:
  1. “Make”
  2. “Buy”
  3. “Make or Buy”
Item List Price

Item List Price allows users to change the list price on items available in the inventory. This is usually done in Item Properties > Item Maintenance in SalesPad.

Item Price List

Item Price allows users to add or change item price lists. Price lists imported must refer to item numbers already existing in GP, with values for Price Method and Price Group already set in the Item Master table.

If the company is not running multicurrency, the user will still need to have the Currency ID column in the spreadsheet, because it is part of the primary key. In this case the user would not need to specify any value and could simply leave the cells blank.

Note : These fields require the actual GP numerical representation. These are the possible values:

UofM_Sales_Option

  • 1 (Not Available)
  • 2 (Whole)
  • 3 (Whole and Fractional)
Item Master

Item Master allows users to access Item Maintenance and update items available in the inventory. This tool can be also used to create new items in bulk.

Note : There are some fields that require the data in form of text value instead of the GP numerical representation. These are the values with their relative numerical representation:

Item_Tracking_Option
  1. “None” or “None (Kit)”
  2. “Serial Numbers”
  3. “Lot Numbers”
Item_Type
  1. “Sales Inventory”
  2. “Discontinued”
  3. “Kit”
  4. “Misc Charge”
  5. “Services”
  6. “Flat Fee”

These fields require the actual GP numerical representation. Here are the possible values:

Price_Method
  • 1 (Currency Amount)
  • 2 (% of List Price)
  • 3 (% Markup - Current Cost)
  • 4 (% Markup - Standard Cost)
  • 5 (% Margin - Current Cost)
  • 6 (% Margin - Standard Cost)
Valuation_Method
  • 0 (None)
  • 1 (FIFO Perpetual)
  • 2 (LIFO Perpetual)
  • 3 (Average Perpetual)
  • 4 (FIFO Periodic)
  • 5 (LIFO Periodic)
SALES_TAX_OPTION
  • 1 (Taxable)
  • 2 (Nontaxable)
  • 3 (Base on customer)
ABC_Code
  • 0 (None)

? 1 (A)

? 2 (B)

? 3 (C)

Purchase_Tax_Option
  • 1 (Taxable)
  • 2 (Nontaxable)
  • 3 (Base on vendor)

Note : The mapping definition also includes a field name called “Purchase_Tax_Options” (ending with –s). This field is available for backward compatibility of SalesPad, and it can be ignored since it is not used by Import Manager.

Sales Line Item

Sales Line Item allows users to import line items onto an existing Sales Document. Look at the SalesPad Sales Line Excel Import document for more details.

Vendor Item

Vendor Item allows users to assign vendors to an item or update information such as base and default purchasing unit of measure, minimum order, maximum order, and order multiple.

Prospect

The Prospects table allows users to import prospects along with prospect contacts into two SalesPad tables, spProspect and spContact. For this, each field name contains an identifier on the field mapping name for Prospect and Contact to show what table the field is associated with and will be imported into. This allows users to import prospects and contacts as part of one line on a spreadsheet without having to do multiple imports.

Clicking the Add All button will add all Prospect fields, but not the Contact fields.

To add a Contact field, click the Add button, then select the Contact field from the dropdown in the Table Field column. You can also add Prospect fields using this method.

In the spProspect table, there is a field for Prospect_Class_ID which is defined as an integer and linked to the table spProspectClass. The import manager allows users to import the string value for Prospect_Class_ID, and will update the Prospect_Class_ID field in the tables appropriately. If the Prospect Class that is imported does not exist, the class will be created and imported into spProspectClass.

The only required field for Prospect import is Prospect_Name. If the name associated with the imported line already exists, it will not be imported a second time.

Note: If your import for Contact.Contact_ID contains repeated values, you will overwrite existing data. This field is not required and will be generated from the corresponding Prospect record generated. 

Customer Items

Customer Items allows users to add Customer Item Definitions.

Service Transfer (In-Transit Transfer)

The Service Transfer Import allows users to import Service Transfers (In-Transit Transfers) along with Service Transfer lines. For this, each field name contains an identifier on the field mapping name for Transfer (ITT) and Transfer Line (ITTLine) to show which object the field is associated with and will be imported into. This allows users to import Service Transfers and Service Transfer Lines as part of one line on a spreadsheet, without having to do multiple imports. 

Note: When importing multiple lines, repeat header information for each line.

Clicking the Add All button will add all Service Transfer fields but not the Service Transfer Line fields.

To add a Service Transfer Line field, click the Add button, then select the Service Transfer Line field from the dropdown in the Table Field column (denoted by the ITTLine prefix).

The ITT.Status field requires the actual GP numerical representation. Here are the possible values:

  • 0 (Open)
  • 1 (Picked)
  • 2 (Packed)
  • 3 (Partial_Ship)
  • 4 (Shipped)
  • 5 (Partially_Received)
  • 6 (Received)
  • 99 (Locked)
How to Create a CSV File

A CSV is a comma separated values file, which allows data to be saved in a table structured format, ideal for spreadsheets. CSVs look like a regular spreadsheet but have a .csv extension.

  1. Open the file in a spreadsheet program
  2. Click on “File” and choose “Save As”

  3. Under Save as type choose CSV (Comma delimited). Click Save.
  4. A message that says “Some features in your workbook might be lost if you save it as CSV (Comma delimited).” Click Yes to continue and save. This message is a warning that some features, such as formatting, may be lost by saving as a CSV file.
Security

To access Import Manager, Import Manager must be enabled in the Security Editor.

Sub-securities include the following: 

  • Can Delete Import Definitions - When True, users can delete import definitions in Import Manager. Defaults to True.
  • Can Edit Import Definitions - When True, users can edit import definitions in Import Manager. Defaults to True. 
  • Can Run Imports - When True, users can run imports in Import Manager. Defaults to True.
Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. SalesPad Support

  2. Posted
  3. Updated

Comments