Overview

SalesPad Desktop 5.4.2 introduced new ways to import serial and lot item data from spreadsheets via Import Manager.  Users can now import receipts for serial-tracked items, mass update serial item user defined fields (UDFs), and mass update lot item attributes and UDFs.  The inventory adjustment import can now be used to assign UDF values for serial items that are being adjusted into the system.

This article assumes that the reader has some familiarity with Import Manager.  For general information on how to use Import Manager, refer to this article.

Table Of Contents

• Purchase Receipt Inventory Serial Num Import
• Inventory Adjustment Import Updates For Serial Item UDFs 
• Inventory Serial Num Mass Update Import
• Inventory Lot Num Mass Update Import

Purchase Receipt Inventory Serial Num Import

Import Manager has a new option that allows receiving serial-tracked items via Purchase Order (PO) receiving, In-Transit Transfer (ITT) receiving, and standalone/PO-less receiving.  The import will look at the Receipt Type, PO Number, and ITT Doc Num fields to deduce the receiving type for each row in the spreadsheet, so a single spreadsheet can perform all three types of receiving if desired.

This import is similar to the Receiving screen in SalesPad and the PO and ITT Receiving modules in Inventory Manager in that it has the following features and restrictions:

• This import automatically creates new receipt documents and line items as needed, so users do not need to manually create receipts ahead of time

• To receive items for a PO or ITT, the PO or ITT document must already exist in the system and be in a receivable state

• Updating a serial number on a pending receipt requires removing and re-inserting it

• Removing a serial number from a pending receipt must be done manually via the Serial/Lot Receiving plugin in Receipt Transaction Entry in SalesPad Desktop, or in GP

• Serial numbers can be auto-generated when receiving items for a PO or standalone receipt by putting “AUTO ASSIGN” in the Serial Number column and then specifying the quantity of serial numbers to generate in the Serial Lot Qty column

Note that this import option only supports receiving serial-tracked items.  It does not support importing receipts for lot-tracked or non-tracked items.

Setup

In Import Manager, create a new import definition and select PurchaseReceiptInventorySerialNum as the table.  Then create field mappings to map SalesPad fields to columns in the spreadsheet.

The SalesPad fields include fields from 4 different tables: Receipt, Receipt Line, Receipt Serial, and Serial User Fields.  The Serial User Fields table is optional and does not require any mappings unless users want to import Inventory Serial Num user fields.  The other tables have a variety of required fields.

If a field exists in multiple tables, each version of that field should be mapped to the same column in the spreadsheet.  For example, Location is a field on the Receipt Line and Receipt Serial tables.  If a user wants to map that field to a spreadsheet column called Site ID, he or she should create 2 field mappings to map Location on each table to the same Site ID column in the spreadsheet.

Required Fields For All Receiving Types

• ReceiptSerial.Serial_Lot_Number

• ReceiptLine.Item_Number

• ReceiptSerial.Item_Number

• ReceiptLine.Location

• ReceiptSerial.Location

• ReceiptSerial.Bin (only required for multibin companies)

• Receipt.Receipt_Number

• ReceiptLine.Receipt_Number

• ReceiptSerial.Receipt_Number

• ReceiptLine.Line_Seq

• ReceiptSerial.Line_Seq

The Serial (Lot) Number, Item Number, Location, and Bin fields are straightforward.  To receive an item, the system must know what is being received and where it is being received.  The Bin field is only required for multibin companies.

The Receipt Number and Line Seq fields are required due to a design philosophy of Import Manager that each import definition must require its database primary keys.  While spreadsheets must include column headers for Receipt Number and Line Seq, it is fine and even expected for those columns to not have any values.  If a row has a blank Receipt Number, the import will create a new purchase receipt.  If multiple rows have a blank Receipt Number, the import will group together similar rows and put them on the same new purchase receipt instead of creating a purchase receipt for each row.  Rows will be grouped together on a single receipt if they share the following traits:

• The rows use the same receiving type: PO receiving, ITT receiving, or standalone/PO-less receiving

• If they use PO receiving, the rows must have the same PO Number and Vendor ID

• If they use ITT receiving, the rows must have the ITT Doc Num

• If they use standalone/PO-less receiving, the rows must have the same Vendor ID

Note that receipt header level fields (the fields which start with “Receipt.”) are create-only.  For example, if two rows have a blank Receipt Number, the same Vendor ID and PO Number, but have different Vendor Doc Nums, the import will use the Vendor Doc Num from the first row.  This also applies to purchase receipt user fields.

Additional Required Fields for PO Receiving

• Receipt.Vendor_ID

• ReceiptLine.Vendor_ID

• ReceiptSerial.Vendor_ID

• ReceiptLine.PO_Number

• ReceiptLine.PO_Line_Seq

The PO Number and PO Line Seq fields specify which purchase order line item is being received.  PO Number is the purchase order’s main identifier and is a highly visible field in SalesPad and GP.  PO Line Seq is part of the primary key for purchase order line items and is less visible.  PO Line Seq can be viewed in SalesPad by going to Purchase Order Search, selecting a purchase order, and adding the Line Seq column to the Line Items grid.

Additional Required Fields for ITT Receiving

• ReceiptLine.ITT_Doc_Num

• ReceiptLine.ITT_Line_Num

The ITT Doc Num and ITT Line Num fields specify which in-transit transfer line item is being received.  ITT Doc Num refers to the Order Document ID field (also known as Document Number or Transfer Number in GP) on the in-transit transfer.  ITT Line Num is part of the primary key for ITT line items and is less visible.  ITT Line Num can be viewed in SalesPad by going to In-Transit Transfer Search, selecting an ITT document, and adding the Line Item Seq column to the Line Items grid.  Note that the grid also has fields for Line Seq and Service Line Seq, but they are different and cannot be used as the ITT Line Num.

Additional Required Fields for PO-less Vendor Receiving

• Receipt.Vendor_ID

• ReceiptLine.Vendor_ID

• ReceiptSerial.Vendor_ID

A vendor must be specified so that the system knows from which vendor the items are being received.  This vendor will be assigned on the generated receipt, and the receipt lines will not be tied to PO lines.

Additional Required Fields for Shipment/Invoice Receipts

• Receipt.Receipt_Type

• Receipt.Vendor_Doc_Num

Receipt Type is normally not required.  It defaults to In-Transit Inventory if the spreadsheet row specifies a value for the ITT Doc Num column, or Shipment if ITT Doc Num is blank or missing from the spreadsheet.  However, if a user wants to enter a Shipment/Invoice receipt, they must specify that Receipt Type in the spreadsheet.  Because Receipt Type is stored as an integer in the database, a row must have the number “3” in the Receipt Type column to create a Shipment/Invoice receipt, instead of specifying “Shipment/Invoice” directly.  The supported receipt types and their integer values are:

Integer to use in spreadsheets	Receipt Type
1	Shipment
3	Shipment/Invoice
8	In-Transit Inventory

Additional Required Fields for Inventory Serial Num User Fields

• SerialUserFields.Item_Number

• SerialUserFields.Serial_Num

Inventory Serial Num UDFs can be supplied via the SerialUserFields table.  Because Import Manager’s design philosophy dictates that each table requires its primary keys, Item Number and Serial Num must be included and mapped to the correct spreadsheet columns for Inventory Serial Num user fields to import.  All available serial item UDFs will be available on the SerialUserFields table for importing.

Notable Optional Fields

• Receipt.Batch

• ReceiptSerial.Serial_Lot_Qty

• Purchase Receipt User Fields

• Purchase Receipt Line Item User Fields

Import Manager automatically populates many other fields on the generated receipt and line items from the item master, vendor, purchase order line item, and/or ITT line item records.  However, there are a few additional fields which are worth noting.

The purchase receipt’s batch will default to “SalesPad RC” for vendor-based receiving or “SalesPad ITT RC” for ITT receiving.  Users can manually specify a batch via Receipt’s Batch field.

Users can use the Serial Lot Qty field when auto-generating serial numbers to specify the number of numbers to generate.  Import Manager ignores the Serial Lot Qty field when a row manually specifies a serial number.  The serial number column for the record in the spreadsheet must be “AUTO ASSIGN” to trigger this serial number auto-generation.

Purchase Receipt user fields are available for import alongside other receipt fields, while Purchase Receipt Line Item user fields are available for import alongside other receipt line fields.

Example Mapping

This screenshot shows a sample mapping for all of the fields which have been mentioned so far.

Usage

Like most imports, the Purchase Receipt Inventory Serial Num import can be run from Import Manager via the Run Import button.  It is also possible for users to run this import via the Receiving screen’s Serial Item Receipt Import plugin.  This allows users to receive via spreadsheets without having access to Import Manager’s setup screen.

Inventory Adjustment Import Updates For Serial Item UDFs

Import Manager already provided the ability to import inventory adjustments.  SalesPad version 5.4.1 added the ability to import Inventory Serial Num user defined fields as part of this import.  The main use case is to set UDFs for a serial number that is being adjusted into the system.  It is possible to set serial UDFs for a serial number which is being adjusted out as well, but this is less common because the UDF values would not be easily visible after posting the adjustment.

Setup

In Import Manager, create a new import definition and select InventoryAdjustment as the table.  Then create field mappings to map SalesPad fields to columns in the spreadsheet.

Required Fields

When adjusting in a serial number with user fields, the following fields are required:

• Item_Number

• Quantity

• UOfM

• Site

• Serial_Lot

• SerialUserFields.Item_Number

• SerialUserFields.Serial_Num

Both Item Number fields should map to the same column in the spreadsheet.  Likewise, Serial Lot and Serial Num should both map to the same column.

Example Mapping

This screenshot shows a sample mapping for all of the required fields.

Usage

Users can only run the Inventory Adjustment import in Import Manager via the Run Import button.  After running the import, the adjustment will be visible in SalesPad and GP.  However, the UDF values will not be visible in SalesPad right away.  Once the Inventory Adjustment document is posted, the items will enter the system, and then the serial UDFs can be viewed on the Equipment Maintenance screen which is accessible by selecting a serial number in the Equipment Lookup.  If a user needs to view the serial UDFs before posting, they can be found in the spxInventorySerialNum table in the database.

Inventory Serial Num Mass Update Import

Import Manager also has the ability to mass update Inventory Serial Num user defined fields for serial numbers which are already in inventory.  Standard serial fields such as Location and Bin are not editable via this import.

Setup

In Import Manager, create a new import definition and select InventorySerialNum as the table.  Then create field mappings to map SalesPad fields to columns in the spreadsheet.

Required Fields

InvSerial.Item_Number and InvSerial.Serial_Num are the only required fields.

Example Mapping

This screenshot shows a sample mapping for the required fields and a few UDFs.

Usage

Users can only run the Inventory Serial Num import in Import Manager via the Run Import button.  Once imported, the updated serial UDF values can be viewed by going to the Equipment Search screen, selecting a serial number, and going to its User Fields tab.

Inventory Lot Num Mass Update Import

Import Manager has the ability to update lot attributes and user defined fields for lot items which are already in inventory.

Setup

In Import Manager, create a new import definition and select InventoryLotNum as the table.  Then create field mappings to map SalesPad fields to columns in the spreadsheet.

Required Fields

• InvLot.Item_Number

• InvLot.Location

• InvLot.Lot_Num

• InvLot.Qty_Type

• InvLot.Date_Received

• InvLot.Date_Seq_Number

• InvLot.Bin (only required for multibin companies)

Lots have many primary keys to support the possibility of a lot number being received or adjusted in multiple times.  Mis-matching any of these values will result in an error that  “The provided primary key data did not match any lot numbers in inventory”.  Users can view all of these fields on the Lot Number Search screen, with the Warehouse column corresponding to the Location field and the Seq column corresponding to the Date Seq Number field.

SalesPad only officially supports the On Hand quantity type, but the following quantity types can also be imported:

Qty Type to use in spreadsheets	Integer in the database
On Hand	1
Returned	2
In Use	3
In Service	4
Damaged	5

Example Mapping

This screenshot shows a sample mapping for the required fields and a few other fields.

Usage

Users can only run the Inventory Lot Num import in Import Manager via the Run Import button.  Once imported, lot dates and user fields can be viewed by going to the Lot Number Search screen and looking at the grid, or selecting a lot to open its card.  Lot attributes can be viewed by going to Lot Number Entry, clicking the Lot Attributes dropdown, and selecting Copy To.