Batch-Updating Items
A batch item update allows you to make changes to existing items, including edits to recurring storage rates, UOM, dimensions, billing, track-by's, and more. This article reviews how to perform a batch item update using the new and improved experience from the Manage Items page.
To update items, you must have the following permission assigned to your user role: Customer > Customer Items > Import Item Definitions.
Qualifiers are not supported in batch item updates. If you need to make changes to an SKU's qualifier, please manually do so under Items > Manage Items.
Guidelines
First, navigate to Items > Manage Items in 3PL Warehouse Manager, then select a customer from the drop-down menu.
There are three options within the Manage menu that are used for batch item updates: Export All, Export Selected, and Update Items. Each of these is further detailed below.
- Export All: Allows you to export all items for a customer, regardless of the grid selection
- Export Selected: Allows you to export only items selected from the grid
- Update Items: Allows you to import item updates, regardless of the grid selection
Once you're familiar with each of these options, use the following steps to update your items.
Step 1: Export items
The first step in performing a batch item update is exporting a master file of your configured items so that you can make changes using this file.
- Decide whether you need to update all items.
- If you need to update all items, select Manage > Export All.
- If you do not need to update all items, select the relevant items from the grid (using < Ctrl > or < Shift > to multi-select), then select Manage > Export Selected.
- Select whether you wish to Include inactive items, then click Export to download the item information.
- Once the exported file downloads, open the file and continue to the next section below.
Using Non-English Characters
To avoid errors when re-uploading files that contain Non-English ASCII characters (i.e. ç é â ê î ô û à è ì ò ù ë ï ü ), open and save the exported file in Notepad or Notepad++.
Important! If numerical values contain leading zeros or exceed 11 characters, they will automatically convert to scientific notation format upon export. To ensure your data is imported correctly, you must re-format these converted cells to numeric strings.
The easiest way to do this using Excel is to copy your export data and open a new Excel sheet, select the entire sheet, then change the Number format to 'Text'.
Once you have formatted the text, paste your data in, then make your changes using the guidelines in the next section. Unlike the default import template process, do not remove the column headers.
Step 2: Update items
Once you export your items and format the text as needed in Excel, you can update your items. Each available column is detailed in the table below, including where the associated field is located in an item's setup via Items > Manage Items.
Please note that most of these fields are alphanumeric and do not accept special characters. Dashes are accepted and commonly used for SKU, UPC, and Description values—however, we do not support carats, commas, or pound signs.
If you need additional information on any of the mapped fields below, please refer to our help articles on Creating Items.
Because items are defined by SKU, a batch item update writes over everything except the SKU—it reads over what has already been entered into the system and only updates items that were previously in 3PL Warehouse Manager. If you add new items or delete any items in the spreadsheet, the system ignores those values.
- Update each column value for each SKU as needed. Use the table below for guidance.
Column | Column Name | Description | Item Setup Mapping Location |
A | SKU | Maps to the 'SKU' field for reference purposes—if you need to modify an SKU's name, you can do so using Column AS | Items > Description |
B | Description | Maps to the 'Item Description' field | Items > Description |
C | Description2 | Maps to the 'Secondary Item Description' field | Items > Description |
D | Min | Maps to the 'Min on Hand' field | Items > Inventory Options |
E | Max | Maps to the 'Max on Hand' field | Items > Inventory Options |
F | CycleCount | Maps to the 'Days Between Counts' field | Items > Inventory Options |
G | ReorderQty | Maps to the 'Reorder Quantity' field | Items > Inventory Options |
H | InventoryMethod | Maps to the 'Allocation Method' field | Items > Inventory Options |
I | Temperature | Maps to the 'Storage Temp' field | Items > Inventory Options |
J | Cost | Maps to the 'Product Cost' field | Items > Description |
K | UPC | Maps to the 'Primary UPC' field | Items > Description |
L | IsTrackLotNumber | Maps to the 'Lot Number Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
M | IsTrackSerialNumber | Maps to the 'Serial Number Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
N | IsTrackExpirationDate | Maps to the 'Expiration Date Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
O | LabelingUnitType | Maps to the 'Packaging Unit of Measure' field—value must be a predefined unit type | Units of Measure > Packaging Unit |
P | LabelingUnitQty | Maps to the 'Primary Units Per Package' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
Q | LabelingUnitLength | Maps to the 'Length' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
R | LabelingUnitWidth | Maps to the 'Width' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
S | LabelingUnitHeight | Maps to the 'Height' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
T | LabelingUnitWeight | Maps to the 'Weight' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
U | LabelingUnitNetWeight | Maps to the 'Net Weight' field (must have value specified in Column O) | Units of Measure > Packaging Unit |
V | InventoryUnitOfMeasureType | Maps to the 'Primary Unit of Measure' field—value must be a predefined unit type | Units of Measure > Inventory Unit of Measure |
W | SecondaryUnitOfMeasureType | Maps to the 'Secondary Unit of Measure' field—value must be a predefined unit type | Units of Measure > Inventory Unit of Measure |
X | InventoryUnitsPerSecondUnit | Maps to the 'Primary Units Per Secondary UOM' field | Units of Measure > Inventory Unit of Measure |
Y | NMFC | Maps to the 'NMFC' field—must be a predefined value configured under Items > Manage Commodities | Items > Shipping Documentation |
Z | IsTrackLotNumberRequired | Maps to the 'Lot Number Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AA | IsTrackSerialNumberRequired | Maps to the 'Serial Number Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AB | IsTrackSerialNumberUnique | Maps to the 'Serial Number Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AC | IsTrackExpirationDateRequired | Maps to the 'Expiration Date Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AD | IsPickLotNumberRequired | Maps to the 'Require Scan at Pick' field for Lot Number Tracking—value must be TRUE or FALSE | Items > Tracking Items |
AE | IsPickSerialNumberRequired | Maps to the 'Require Scan at Pick' field for Serial Number Tracking—value must be TRUE or FALSE | Items > Tracking Items |
AF | IsPickExpirationDateRequired | Maps to the 'Require Scan at Pick' field for Expiration Date Tracking—value must be TRUE or FALSE | Items > Tracking Items |
AG | IsTrackCost | Maps to the 'Cost Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AH | IsTrackCostRequired | Maps to the 'Cost Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AI | MovableUnitDescription | Maps to the description of the associated MU type in the 'Movable Unit' field—value must match a predefined MU description configured under Admin > Movable Units | Units of Measure > Movable Unit |
AJ | MovableUnitTie | Maps to the 'Tie' field | Units of Measure > Movable Unit |
AK | MovableUnitHigh | Maps to the 'High' field | Units of Measure > Movable Unit |
AL | MovableUnitQty | Maps to the 'Quantity' field | Units of Measure > Movable Unit |
AM | MovableUnitLength | Maps to the 'Length' field | Units of Measure > Movable Unit |
AN | MovableUnitWidth | Maps to the 'Width' field | Units of Measure > Movable Unit |
AO | MovableUnitHeight | Maps to the 'Height' field | Units of Measure > Movable Unit |
AP | MovableUnitWeight | Maps to the 'Weight' field | Units of Measure > Movable Unit |
AQ | Active | Specifies whether the item is currently active—value must be TRUE or FALSE | Manage Items grid |
AR | StorageUOMs | Maps to the fields in the Storage/Handling Charges setup—must use the following format, Warehouse Name:BillingUOM:#ofUnits (e.g., Central Warehouse:CuFt:1.0000) | Storage/Handling Charges |
AS | RenameSKU | Allows you to redefine the 'SKU' name | Items > Description |
AT | IsHazMat | Maps to the 'Hazardous Material' field—value must be TRUE or FALSE | Items > Hazardous Material |
AU | HazMatID | Maps to the 'Material ID' field | Items > Hazardous Material |
AV | HazMatShippingName | Maps to the 'Shipping Name' field | Items > Hazardous Material |
AW | HazMatHazardClass | Maps to the 'Class' field | Items > Hazardous Material |
AX | HazMatPackingGroup | Maps to the 'Packaging Group' field—value must be I, II, III, or left blank to indicate 'Unspecified' | Items > Hazardous Material |
AY | HazMatFlashPoint | Maps to the 'Flashpoint' field | Items > Hazardous Material |
AZ | HazMatLabelCode | Maps to the 'Label Code' field | Items > Hazardous Material |
BA | HazMatFlag | Maps to the 'Material Flag' field —value must be X, RQ, or left blank to indicate 'Unspecified' | Items > Hazardous Material |
BB | StorageUnitOfMeasure | Maps to the 'Storage UOM' field—value must be a predefined unit type and only needed for certain codes defined in Column BD | Recurring Storage |
BC | InventoryUnitsPerStorageUnit | Maps to the '# Eaches Per [UOM]' field—value must be a predefined unit type and only needed for certain codes defined in Column BD | Recurring Storage |
BD | StorageCountScriptTemplateID |
Maps to the 'Counting Mechanism' field—must use one of the following number values: 0 – Unspecified 201 – Storage UOM (must also complete column BB with storage unit of choice and BC with amount per storage to charge) 202 – Movable Units 203 – Location 204 – Rounding Storage UOM (must also complete column BB with a storage unit of choice and BC with the amount per storage to charge) 205 – MovableUnit Sq Foot 206 – Catch Weight by Lot 207 – Storage UOM by Lot (must also complete column BB with storage unit of choice and BC with amount per storage to charge) |
Recurring Storage |
BE | ItemFacilityStorageRates | Maps to the grid values in the Recurring Storage setup—must use the following format, Warehouse Name:PrimaryUOM:Rate (e.g., Central Warehouse:CuFt:15.0000) | Recurring Storage |
BF | OutboundSerialization |
Maps to the 'Outbound Mobile Serialization' field—must use one of the following number values: 0: Do not use outbound serialization 1: Ignore quantity mismatches 2: Require quantities to match |
Items > Tracking Options |
BG | Price | Maps to the 'Sell Price' field | Items > Description |
BH | Classification | Maps to the 'Classification' field—must be a predefined value configured under Items > Manage Classifications | Items > Description |
BI | CountryOfManufacture | Maps to the 'Country of Origin' field—value must be a 2-character country code that follows the ISO 3166 international standard | Items > Shipping Documentation |
BJ | HarmonizedCode | Maps to the 'Harmonized Tariff Code' field | Items > Shipping Documentation |
BK | InventoryCategory | Maps to the 'Cycle Count Type' field—value must be A, B, C, D, E, F, or left blank to indicate 'Unclassified' | Items > Inventory Options |
BL | StorageRates | Maps to the grid values in the Storage/Handling Charges setup that define custom rates* | Storage/Handling Charges |
BM | LocationZones | Maps to the 'Packaging Unit Zones' field | Items > Directed Putaway |
BN | MovableUnitLocationZones | Maps to the 'Movable Unit Zones' field | Items > Directed Putaway |
BO | OverAllocLocationZones | Maps to the 'Overallocated Zones' field | Items > Directed Putaway |
BP | PreferredLocationID | Maps to the 'PreferredLocationId' field | Items > Directed Putaway |
BQ | ForceIntoPreferredLocation | Maps to the 'Use location even if full' field—value must be TRUE or FALSE | Items > Directed Putaway |
BR | AllowMixedItemStorage | Maps to the 'Store with other items' field—value must be TRUE or FALSE | Items > Directed Putaway |
BS | AllowMixedLotStorage | Maps to the 'Store multiple lots' field—value must be TRUE or FALSE | Items > Directed Putaway |
BT | MixedExpirationDaysThreshold | Maps to the 'Mixed Expiration Days Threshold' field | Items > Directed Putaway |
BU | LabelingUnitUpc | Maps to the 'Packaging UPC' field for the packaging UoM | Units of Measure > Packaging Unit |
BV | MovableUnitUpc | Not currently supported | N/A |
BW | SecondaryUnitUpc | Maps to the 'Secondary UPC' field for the secondary UoM | Units of Measure > Inventory Units of Measure |
BX | StorageChargeMin | Maps to the 'Min Charge' field if 206 or 207 is specified for column BD | Recurring Storage |
BY | StorageChargeMinMonthsToSkip | Maps to the 'Months to Skip' field if 206 or 207 is specified for column BD | Recurring Storage |
BZ | AutoHoldExpirationDaysThreshold | Maps to the 'Hold Threshold Days' field | Items > Tracking Options |
*For Column BL, please use the following format:
Warehouse Name:BillingUOM:#ofUnits:SplitPeriodFullRate:SplitPeriodHalfRate:FreeDays:
ReceivingHandlingRate:ShippingHandlingRate|RepeatSameFormatForAdditionalWarehouses
(e.g., Central Warehouse:CuFt:5:1:1:0:0:0|3PL Warehouse:Each:10:2:2:0:2:2)
2. After you make the necessary changes to your items, click File > Save a Copy (or Save as).
3. Enter a new name for the file and select a recognizable location on your device so you can easily recognize the updated version, select the format as Text (Tab delimited), then click Save.
Step 3: Import items
Now that you've made your changed and saved your file, it's time to import.
- Return to the Manage Items page in 3PL Warehouse Manager, then select Manage > Update Items.
- Click Select Import File (txt), then double-click the file on your device.
The 'Download Current SKU List' button is an alternate way to export all items.
- All changes populate in the Expected Data Changes container. Verify that these are accurate, then click Confirm to update your items and receive a confirmation message.