Skip to main content

Alternate Item Import Manager - Knowledgebase / SalesPad / Inventory - Cavallo Technical Support

Alternate Item Import Manager

Authors list

Alternate Items functionality allows access to a vendor catalog without adding the items into inventory. In order to use this functionality, the vendor’s catalog must be imported into the SalesPad® Alternate Items table via the Import Manager module.

  1. Go to Modules > Security Editor
  2. Filter to and select Import Manager for the user group that will be importing alternate items
  3. To allow searching for alternate items in the Inventory Lookup module, filter to and select Inventory Lookup, and set Can Access Alternate Item Catalog to True
  4. To allow searching for Alternate Items when accessing Inventory Lookup from the sales document screen, filter to and select Sales Inventory Lookup and set Can Access Alternate Item Catalog to True
    Note: This sub-setting is available in versions later than 4.0.1169; prior versions do not require security to search for alternate/3rd party items when accessing Inventory Lookup from the sales document screen

To search for alternate items by default in Inventory Lookup (the “Search Alternate Items” setting is False by default):

  1. Go to Modules > Settings
  2. Filter to alternate items and set Search Alternate Items to True
Importing the Vendor’s Catalog

The vendor file must be imported into the spAlternateItems table in the SalesPad database. The following is the table definition:

Field Name: Field Type:

ID int
Item_Number char(31)
Sec_Item_Number char(31)
Mfg_Item_Number char(31)
Mfg char(25)
Source_Name char(25)
Source_Item_Number char(31)
Item_Desc char(101)
Long_Desc nvarchar(1000)
Item_Class char(11)
Is_New bit
Last_Update datetime
Est_Cost numeric(19, 5)
Mfg_Cost numeric(19, 5)
Retail_Price numeric(19, 5)
Qty_Onhand int
Upc_Code char(15)
Delivery_Notes char(30)
Item_Notes nvarchar(2000)
UOFM char(9)

Note: Item_Number, Item_Desc, Est_Cost, Retail_Price, and UOFM are required fields that pull onto an order.

Alternate Item Import

  1. Go to Setup/Utilities > Import Manager
  2. Under Import Definitions (left side), click Add
  3. Enter the Name of the import definition on the window that appears and click OK
  4. Select AlternateItem from the Table dropdown
  5. Enter the Delimiter type you are using
  6. Click SaveYhw1KolIR69x5lezGNVy3vjt9PDYNwHR.gif?access_token=snx3vr-srnnhldvjc-bf86f62360276d3c1e75c54b5b2fb3c6cb880cc0
  7. Under Field Mappings (right side), click Add
  8. Select a Table Field from the dropdown. The Table Field is the name of the SalesPad field where the data will appear
  9. Enter the Import Field. The Import Field is the name of the column on the sheet that you'll be importing

Example: Table Field: The item numbers we will be importing are under the Item No column in the Excel sheet, so we want the Item No column to be matched with the Item_Number Table Field. See below for an example of the Field Mappings, followed by the Excel sheet used for reference just below that:

kkX3hFSckRj1LWYXsMtvecV1ggaQ5sIU.png?access_token=snx3vm-vhqfdlunpu-debf7f1a90919c0a844760c611605ee29a5991db

Note: special characters such as '.' or ',' will cause the mappings to not import successfully.

Repeat these steps until you have mapped all the fields from your Excel or CSV file and click Save

Note: To clear previously imported items before running the import, click the button next to Run Import "CLEAR Table Items on Import". This feature will remain on until you click it again, changing it to "LEAVE Table Items on Import".

  1. Click Run Import
  2. Select the file type and locate the file to import. Click OK

Note: If you are using Excel, the worksheet you are importing must be named import:

Once the items have been imported, they are stored in the table spAlternateItems.

The Alternate Item Import uses the following stored procedures:

  • sppUpdateAlternateItem
  • sppUpdateAlternateItems
  • sppUpdateImport

The Alternate Item Import uses the following tables:

  • spImport
  • spImportField – Stores the list of field mappings
  • spAlternateItems – Stores the alternate items

The Alternate Item Import uses the following views:

  • spvImport
  • spvImportField
  • spvAlternateItem
Searching Alternate Items
  1. Go to Inventory > Inventory Lookup
  2. Make sure the Search Alternate Items box is checked. Doing so enables the Alternate Items tab next to the Inventory Items tab
  3. Enter search criteria and click Search or hit Enter. The Alternate Items tab will rename to "Alternate Items (X items found)"

Note: You can also search in the Item # field with the mfg item number for your alternate/3rd party items.

Helpful Unhelpful