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 Alternate Item 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.
Import ManagerAvailable Tables |
|
| Alternate Item AltSellDef (Cross/Upsell) AltSellLine (Cross/Upsell) Case Tracker Case Case Tracker Detail Contact (CRM) Custom Cost Custom Price Customer Item Number Inventory Adjustment Inventory By Loc Inventory Serial Number Item Barcode Item List Price Item Master Item Price List |
Item Promo Item Promo Detail Item Promo Offer Item Restrictions Customer Item Restrictions Detail Item Restrictions Group Kit Component Prospect (CRM) Purchase Line Item Sales Document Sales Document Tracking Number Sales Line Item Service Transfer Service Transfer Line Vendor Item |
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. Conversely, 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.
Required Fields by Table |
|
|
|
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 Alternate Items Catalog and Import Manager document.
Cross/UpSell
The AltSellDef and AltSellLine Imports provide a convenient way to create cross or upsell entries and related details including Pitch, Start and End Dates. Additional details on the Cross/Upsell module can be found here.
Case Tracker Case
Case Tracker can be used for a range of purposes in a business, and is often used to facilitate Returns, track issues and resolutions against products, and more.
Case Tracker Detail
Case Tracker Details, such as items included in a case, can be imported or updated using this Import.
Custom Cost
Import Vendor Special Cost and related Customer and Item records using the Vendor Custom Cost import. Special Costing is typically maintained on a Vendor Card.
Custom Price
Custom Price Import 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.
Customer Item Number
Customer Items allows users to add Customer Item Definitions. This can be useful when a customer maintains their own part numbers. With proper configuration, Customer Items entered onto sales transactions will convert to the appropriate Sales Inventory Item Number automatically.
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):
| Text Value |
GP Numerical Value |
| None or None (Kit) |
1 |
| Serial Numbers |
2 |
| Lot Numbers |
3 |
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:
| Text Value |
GP Numerical Value |
| Order Point Qty |
1 |
| Order-Up-To Level |
2 |
| Vendor EOQ |
3 |
Order_Policy:
| Text Value |
GP Numerical Value |
| Not Planned |
1 |
| Lot for Lot |
2 |
| Fixed Order Qty |
3 |
| Period Order Qty |
4 |
| Order Point |
5 |
| Manually Planned |
6 |
Replenishment_Method:
| Text Value |
GP Numerical Value |
| Make |
1 |
| Buy |
2 |
| Make or Buy |
3 |
Inventory By Serial
Import Serialized Inventory Items and related serial number and transactional data.
Item Barcode
Import Item Number and Barcode records to the Barcode Maintenance module in SalesPad Desktop. For more details see Barcode Maintenance.
Note: These barcodes are separate from those entered in Inventory Manager's Item Barcode Maintenance module. Barcodes entered in the SalesPad Desktop Module will not appear in Inventory Manager. Similarly, barcodes created in Inventory Manager will not appear in SalesPad.
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 that already exist within Dynamics 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
| Text Value |
GP Numerical Value |
| Not Available |
1 |
| Whole |
2 |
| Whole and Fractional |
3 |
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
| Text Value |
GP Numerical Value |
| None or None (Kit) |
1 |
| Serial Numbers |
2 |
| Lot Numbers |
3 |
Item_Type
| Text Value |
GP Numerical Value |
| Sales Inventory |
1 |
| Discontinued |
2 |
| Kit |
3 |
| Misc Charge |
4 |
| Services |
5 |
| Flat Fee |
6 |
These fields require the actual GP numerical representation. Here are the possible values:
Price_Method
| Text Value |
GP Numerical Value |
| Currency Amount |
1 |
| % of List Price |
2 |
| % Markup - Current Cost |
3 |
| % Markup - Standard Cost |
4 |
| % Margin - Current Cost |
5 |
| % Margin - Standard Cost |
6 |
Valuation_Method
| Text Value |
GP Numerical Value |
| None |
0 |
| FIFO Perpetual |
1 |
| LIFO Perpetual |
2 |
| Average Perpetual |
3 |
| FIFO Periodic |
4 |
| LIFO Periodic |
5 |
Sales_Tax_Option
| Text Value |
GP Numerical Value |
| Taxable |
1 |
| Nontaxable |
2 |
| Based on Customer |
3 |
ABC_Code
| Text Value |
GP Numerical Value |
| None |
0 |
| A |
1 |
| B |
2 |
| C |
3 |
Purchase_Tax_Option
| Text Value |
GP Numerical Value |
| Taxable |
1 |
| Nontaxable |
2 |
| Based on Vendor |
3 |
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.
Item Promo / Item Promo Detail / Item Promo Offer
Import and assign Sales Document Promotions using these imports mappings. Each reflects a level of detail within the Sales Document Promotions module.
Item Restrictions Customer / Detail / Group
Import and update Item Restrictions for customers, items, and groups.
Kit Component
Use this Import Mapping to add or update the values of components of a Kit-type Inventory Item. More information on Kits can be found here.
Prospect
The Prospects table allows users to import CRM 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 does not require data in the source file and will be generated from the corresponding Prospect record generated.
Purchase Line Item
Purchase Line Item allows users to import line items onto an existing Purchase Order. View the Purchase Line Excel Import documentation for additional details.
Sales Document
Sales Document is considered an Advanced Import Definition and can be used to generate new Sales Documents for customers. Line items included in the mapping can be imported as well.
Sales Document Tracking Number
This import can be used to bulk-import and assign Tracking Numbers to Sales Documents. Tracking Numbers are written to the SOP10107 Sales Document Work/History Table.
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.
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:
| Text Value |
GP Numerical Value |
| Open |
0 |
| Picked |
1 |
| Packed |
2 |
| Partial_Ship |
3 |
| Shipped |
4 |
| Partially_Received |
5 |
| Received |
6 |
| Locked |
99 |
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. Many of these fields can be found on the Dynamics GP Item Vendors Maintenance window.
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.
- Open the file in a spreadsheet program
- Click on “File” and choose “Save As”
- Under Save as type choose CSV (Comma delimited). Click Save.
- 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 Clear Import Table - When True, users can opt to clear and replace previously-imported records to the table
- 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.
- Pre Import Script - C# Script that executes before the Import Manager records are validated and imported
- Pre Load Existing Object Script - C# Script that executes before the Import Manager determines if an import record already exists in the SalesPad environment