We've enhanced this functionality! To learn more, see Batch-Updating Items. In the coming weeks, the classic Batch Item Update page will be removed from 3PL Warehouse Manager.
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 process extracts data from the items that have been entered into the system, allows you to make changes to the items through a spreadsheet, and then places the file back into the system to apply the modified settings.
This article reviews how to perform a batch item update in detail.
To update items, you must have the following permission assigned to your user role: Customer Module > Customer Items > Import Item Definitions.
Qualifiers are not supported in batch item updates. If you need to make changes to a SKU's qualifier, please manually do so under Items > Manage Items.
Skip Ahead: Use the following links to skip to the relevant guidelines below.
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.
Please note that larger exports can sometimes cause timeouts. If a timeout occurs while exporting, we recommend doing minor edits manually. If you need to make larger edits, please contact 3PL Central Support for assistance.
- Navigate to Items > Batch Item Update, then select the relevant customer and warehouse from the respective drop-down menus.
- Click Export Master. Once the exported file downloads, open the file and continue to the next section for more details.
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. Unlike the default import template process, do not remove the column headers. Continue to the next section for additional details.
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 the majority 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 mentioned below, please refer to our help articles on Creating Items.
Because items are defined by the 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.
1. Change the values for each column that needs updating for each SKU. 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 a SKU's name, you can do so using Column AP | 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 '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 UOM' field—value must be a predefined unit type | Units of Measure > Packaging Unit |
P | LabelingUnitQty | Maps to the 'Primary Units Per Packaging Unit' field | Units of Measure > Packaging Unit |
Q | LabelingUnitLength | Maps to the 'Length' field | Units of Measure > Packaging Unit |
R | LabelingUnitWidth | Maps to the 'Width' field | Units of Measure > Packaging Unit |
S | LabelingUnitHeight | Maps to the 'Height' field | Units of Measure > Packaging Unit |
T | LabelingUnitWeight | Maps to the 'Weight' field | Units of Measure > Packaging Unit |
U | LabelingUnitNetWeight | Maps to the 'Net Weight' field | Units of Measure > Packaging Unit |
V | InventoryUnitOfMeasureType | Maps to the 'Primary UOM' field—value must be a predefined unit type | Units of Measure > Inventory Unit of Measure |
W | SecondaryUnitOfMeasureType | Maps to the 'Secondary UOM' field—value must be a predefined unit type | Units of Measure > Inventory Unit of Measure |
X | InventoryUnitsPerSecondUnit | Maps to the 'Primary quantity' field—if secondary UOM is not enabled, value must be 1 | 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 | IsTrackCost | Maps to the 'Cost Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AE | IsTrackCostRequired | Maps to the 'Cost Tracking' field—value must be TRUE or FALSE | Items > Tracking Options |
AF | 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 |
AG | MovableUnitTie | Maps to the 'Tie' field | Units of Measure > Movable Unit |
AH | MovableUnitHigh | Maps to the 'High' field | Units of Measure > Movable Unit |
AI | MovableUnitQty | Maps to the 'Quantity' field | Units of Measure > Movable Unit |
AJ | MovableUnitLength | Maps to the 'Length' field | Units of Measure > Movable Unit |
AK | MovableUnitWidth | Maps to the 'Width' field | Units of Measure > Movable Unit |
AL | MovableUnitHeight | Maps to the 'Height' field | Units of Measure > Movable Unit |
AM | MovableUnitWeight | Maps to the 'Weight' field | Units of Measure > Movable Unit |
AN | Active | Specifies whether the item is currently active—value must be TRUE or FALSE | Manage Items grid |
AO | 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 |
AP | RenameSKU | Allows you to redefine the 'SKU' field | Items > Description |
AQ | IsHazMat | Maps to the 'Hazardous Material' field—value must be TRUE or FALSE | Items > Hazardous Material |
AR | HazMatID | Maps to the 'Material ID' field | Items > Hazardous Material |
AS | HazMatShippingName | Maps to the 'Shipping Name' field | Items > Hazardous Material |
AT | HazMatHazardClass | Maps to the 'Class' field | Items > Hazardous Material |
AU | HazMatPackingGroup | Maps to the 'Packaging Group' field—value must be I, II, III, or left blank to indicate 'Unspecified' | Items > Hazardous Material |
AV | HazMatFlashPoint | Maps to the 'Flashpoint' field | Items > Hazardous Material |
AW | HazMatLabelCode | Maps to the 'Label Code' field | Items > Hazardous Material |
AX | HazMatFlag | Maps to the 'Material Flag' field —value must be X, RQ, or left blank to indicate 'Unspecified' | Items > Hazardous Material |
AY | StorageUnitOfMeasure | Maps to the 'Storage UOM' field—value must be a predefined unit type and only needed for certain codes defined in Column BA | Recurring Storage |
AZ | InventoryUnitsPerStorageUnit | Maps to the '# Eaches Per [UOM]' field—value must be a predefined unit type and only needed for certain codes defined in Column BA | Recurring Storage |
BA |
StorageCountScriptTemplateID | Maps to the 'Counting Mechanism' field—must use one of the following number values: 0 – Unspecified 201 – Storage UOM (must also complete column AY with storage unit of choice and AZ with amount per storage to charge) 202 – Movable Units 203 – Location 204 – Rounding Storage UOM (must also complete column AY with storage unit of choice and AZ with amount per storage to charge) 205 – MovableUnit Sq Foot 206 – Catch Weight by Lot 207 – Storage UOM by Lot (must also complete column AY with storage unit of choice and AZ with amount per storage to charge) |
Recurring Storage |
BB |
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 |
BC |
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 |
BD |
Price |
Maps to the 'Sell Price' field |
Items > Description |
BE |
Classification |
Maps to the 'Classification' field—must be a predefined value configured under Items > Manage Classifications |
Items > Description |
BF |
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 |
BG |
HarmonizedCode |
Maps to the 'Harmonized Tariff Code' field |
Items > Shipping Documentation |
BH |
InventoryCategory |
Maps to the 'Cycle Count Type' field—value must be A, B, C, or left blank to indicate 'Unclassified' |
Items > Inventory Options |
BI |
StorageRates | Maps to the grid values in the Storage/Handling Charges setup that define custom rates* | Storage/Handling Charges |
* For Column BI, 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 changes and saved your file, it's time to import.
- Return to the Batch Item Update page in 3PL Warehouse Manager.
- Click Browse, doube-click the updated file, then click Import Items.
- A preview of the imported data appears in the bottom text boxes. Once you've verified the data for accuracy, click Update Items to finalize the import.
The items are now updated in 3PL Warehouse Manager and available for viewing or additional editing under Items > Manage Items.
Comments
0 comments
Please sign in to leave a comment.