Item Master Import Guide - Revision history

Freddy at 15:23, 27 November 2023

2023-11-27T15:23:14Z

Tcampbell at 16:31, 22 December 2022

2022-12-22T16:31:32Z

Jared at 17:57, 8 January 2021

2021-01-08T17:57:10Z

Freddy at 19:33, 10 January 2019

2019-01-10T19:33:48Z

Freddy at 15:45, 13 December 2018

2018-12-13T15:45:03Z

Freddy at 20:22, 9 November 2018

2018-11-09T20:22:44Z

Freddy: /* Item Master Import File Definitions */

2018-11-09T20:22:22Z

‎Item Master Import File Definitions

Freddy at 20:15, 9 November 2018

2018-11-09T20:15:54Z

Show changes

Freddy at 19:02, 9 November 2018

2018-11-09T19:02:24Z

Freddy at 18:39, 9 November 2018

2018-11-09T18:39:08Z

Show changes

@@ Line 44: / Line 44: @@
 * '''Unit Weights''' - Unit weight can be critically important throughout multiple Adjutant systems and processes such as production and shipping. It is best practices to make sure that all sold items have a unit weight that corresponds to the stock unit imported.
+* '''Price/Cost/Weight Rounding''' - Review all values in the spreadsheet for desired rounding and final values. Excel may show different data in each column compared to the actual cell data. If any values need to be imported as values rounded to a specific number of decimals, or as whole numbers, make sure the spreadsheet cell source data is updated and saved with the correct values before importing.
@@ Line 100: / Line 100: @@
 '''*f7 - AvgCost''' -  Item’s current average cost per stocking unit
-'''*f8 - Price''' -  Item’s price per pricing unit
+'''*f8 - Price''' -  Item’s price per pricing unit. Review the spreadsheet data carefully and update any pricing information in the spreadsheet to reflect the desired rounding/# of decimals before importing.
 '''**f9 - PriceUnit''' -  Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)

← Older revision		Revision as of 16:31, 22 December 2022
Line 1:		Line 1:
−		+	__NOTOC__
	==General Data Import Notes==		==General Data Import Notes==

@@ Line 104: / Line 104: @@
 '''**f9 - PriceUnit''' -  Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
-'''**f10 - CostUnit''' -  Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+'''**f10 - CostUnit''' -  Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units).  Referred to as the Pur Cost Unit on the Item Master screen.
 '''*f11 - StkFlag''' -  Y if the item is stocked in the warehouse, N if it is not (made to order items are not stocked)

← Older revision		Revision as of 19:33, 10 January 2019
Line 1:		Line 1:

−	==General Import ~~Template File~~ Notes==	+	==General Data Import Notes==

−	*Template files are typically generated from their respective Import ~~screen using an 'Export Template' button.~~	+	Refer to the [[Data Import General Information]] page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.
−	*Using the 'Export Template' button from an updated system will help ensure ~~that~~ you are using the most ~~up-to-date import file, and will help avoid errors.~~
−	*Templates generally (but not in all cases) contain 3 rows of header information.
−	**The first row is generally labelled with f1 through f## and is used to give a consistent column id to each data column. Do not remove this row unless instructed.
−	**The second row is labelled with the column data/field name. The values in this row are used for the file mapping screen. Editing these values is generally not needed and may make the field mapping process difficult.
−	**The third row (if present) is labelled either with the same values from row 2, or with more detailed field descriptions. The third row is available to create a customer-friendly name for ~~the associated~~ data ~~column.~~
−	***The third row should be removed from the source file prior to importing. It will cause errors since it will be treated as the first import record on the file.
−	Import files MUST always be saved as 'Microsoft Excel 5.0/95 Workbook (.xls)' format until further notice. The import screens will generate errors if the file type is not correct.

−	*It is a good idea to perform a Mapping Table Import using a current source list before beginning on any imports. Without a current mapping table, the quality of imports can be compromised. The Mapping Table Import only needs to be run once on a newly set up Adjutant system. It does not need to be run before each import.
−	**Export a current list from the Mapping Table Import screen from an updated system (or contact your Implementation Consultant for an updated file).
−	**Perform a Mapping Table Import on the target system with the XLS file from the step above.

← Older revision		Revision as of 15:45, 13 December 2018
Line 64:		Line 64:


−	'''File Name~~(Header)~~/Browse:''' Use the Browse button to locate and select the completed Item Master import template file (in XLS format).	+	'''Item File Name/Browse:''' Use the Browse button to locate and select the completed Item Master import template file (in XLS format).

	'''Export Template:''' Generates a blank Item Master import template file		'''Export Template:''' Generates a blank Item Master import template file

← Older revision		Revision as of 20:22, 9 November 2018
Line 198:		Line 198:

	'''f51 - UPCCode''' - Item's UPC code (alphanumeric, up to 12 characters)		'''f51 - UPCCode''' - Item's UPC code (alphanumeric, up to 12 characters)
		+
		+

	==Item Master Import Reconciliation==		==Item Master Import Reconciliation==

@@ Line 94: / Line 94: @@
 ==Item Master Import File Definitions==
-Required fields are indicated with an * ; REquired fields that can be auto-filled by the Import screen are indicated with **
+Required fields are indicated with an * ; Required fields that can be auto-filled by the Import screen are indicated with **
 '''*f1 - Item''' - Item number/code (20 character max, alphanumeric, no spaces allowed). The following special characters are allowed (. - + / _)
@@ Line 112: / Line 112: @@
 '''*f8 - Price''' -  Item’s price per pricing unit
-'''*f9 - PriceUnit''' -  Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+'''**f9 - PriceUnit''' -  Item’s pricing unit (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
-'''*f10 - CostUnit''' -  Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
+'''**f10 - CostUnit''' -  Item’s costing unit associated with the Standard Cost (STDCOST) value (Unit values should match UNITS rule exactly, including capitalization, generally use ALL CAPS for units)
 '''*f11 - StkFlag''' -  Y if the item is stocked in the warehouse, N if it is not (made to order items are not stocked)
@@ Line 140: / Line 140: @@
 '''f22 - MiscItem''' -  Y if the item is miscellaneous, N if it is not
-'''f23 - Resell''' -  Y if you plan on selling this item, N if you do not
+'''f23 - Resell''' -  Y if you plan on selling this item, N if you do not. If this field is left blank, the item cannot be added to a sales order.
 '''f24 - PackCode''' -  Item Packing Code (accepts single character letters from A - T). Can be used to group items that should be packed together by this code. (rare)
@@ Line 197: / Line 197: @@
 '''f50 - EDICode''' - Item's EDI code (alphanumeric, up to 10 digits, rare)
 '''f51 - UPCCode''' -  Item's UPC code (alphanumeric, up to 12 characters)
 ==Item Master Import Reconciliation==

@@ Line 38: / Line 38: @@
 '''OHF Records'''  If you are importing items for a single warehouse/single owner location, the Item Master import can build the default OHF records automatically.  If you are importing for a multi-warehouse and/or multi-owner locations, and ALL imported items on the file are valid for ALL OHF combinations, the Item Master import can also automatically build the needed OHF records for all combinations. If some items are exclusive to certain OHF records, it may be easiest to handle this with separate Item Master import files. Or, depending on the situation, you may be able to use the Item Import (DET) file, also known as the Min/Max file to create the needed OHF combinations.
-'''Item Attributes'''  The Item Master import can automatically create the Catch Weight attribute if the CW columns is 'Y' and the related CW columns are filled in. It will also create the Coil Lot (Coil Receipt Defaults) attribute if the Lot column is 'Y'.
+'''Item Attributes'''  The Item Master import can automatically create the Catch Weight attribute if the CW columns is 'Y' and the related CW columns are filled in. It will also create the Coil Lot (Coil Receipt Defaults) attribute if the Coil column is 'Y'.
 Another reason to consider separating the import files is automatically apply other attributes to certain item types during import. For example, the Buyout, Purchase in QuickBuy, Warning Note, and other special attributes can be auto-assigned to every item on the import if the attribute is flagged during the import. To set an attribute as available in the Item Master import, set the 'Show in Import' (Logic2) option in the Item Attributes (ITEMATTRIB) rule.
@@ Line 120: / Line 120: @@
 '''f12 - SerialFlag''' -  Y if the item is serialized, N if it is not (serialized items have a serial number for every quantity of 1)
-'''f13 - LotFlag''' -  Y if the item is lot controlled, N if it is not (all coils are lot controlled). Lot-controlled items will automatically be assigned the Coil Receipts Default attribute.
+'''f13 - LotFlag''' -  Y if the item is lot controlled, N if it is not (all coils are lot controlled).
 '''f14 - Vendno''' -  The vendor code of  the item’s default supplier
@@ Line 165: / Line 165: @@
 '''f34 - SearchCode''' -  Custom text that can be used to generate a search screen for items with the same/similar search code value. For example, all items with the search code of 'Trim' will display in a search code display (by double-clicking on the item Search Code field), regardless of their item number or description. (maximum 35 characters, alphanumeric)
-'''f35 - ProdGrp''' -  Item’s product group. Enter one of the following in the column; 'M'aterial,'L'abor,'E'quipment, E'X'pense, 'S'ubcontract. All items must be assigned to one of the 5 system-defined categories that drive revenue and cost accounting.
+'''f35 - ProdGrp''' -  Item’s product group. Enter one of the following in the column - '''M''' (Material), '''L''' (Labor), '''E''' (Equipment), '''X''' (Expense), or '''S''' (Subcontract). All items must be assigned to one of the 5 system-defined categories that drive revenue and cost accounting.
-'''f36 - OwnerID''' -  Item's inventory owner. This is generally only used in locations where inventory can be owned by other entities and the accounting needs to be kept separate. Generally, all items should be imported with the default OwnerID value, unless given specific instructions to create multiple Owner records, and multiple OHF combinations.
+'''f39 - Coil''' - Y if the item is a coil. Coil items will automatically be assigned the Coil Receipts Default attribute. All coil items should also have the LotFlag column set to Y.
-'''f36 - SearchCode''' -  Leave blank
+'''f43 - CWSPriceConv''' -  Catch-Weight conversion factor between stock unit and pricing unit. (numeric, up to 5 digits)
-'''f37 - StdCost''' -  Item’s standard cost per pricing unit
+'''f44 - CWSTKConv''' -  Catch-Weight conversion factor between catch unit and stock unit. (numeric, up to 5 digits)
-'''f38 - Coil''' - Y if the item is a coil.
+'''f45 - CWSellConv''' -  Catch-Weight conversion factor between stock unit and selling unit. (numeric, up to 5 digits)
-'''f39 - CW''' -  Y if the item is a catch-weight item.
+'''f46 - CWPurCon''' - Catch-Weight conversion factor between stock unit and purchase unit. (numeric, up to 5 digits)
-'''f40 - CWPurCon''' - Catch-Weight conversion factor between stock unit and purchase unit
+'''f47 - CWUnit''' -  Catch-Weight Unit
-'''f41 - CWSellConv''' -  Catch-Weight conversion factor between stock unit and selling unit.
+'''f48 - PackQty''' -  Quantity to pack. (numeric, up to 5 digits)
-'''f42 - CWSPriceConv''' -  Catch-Weight conversion factor between stock unit and pricing unit.
+'''f49 - AllOrder''' - Enter Y to set the Allocate at Order flag in Item Master
-'''f43 - CWSTKConv''' -  Catch-Weight conversion factor between catch unit and stock unit.
+'''f50 - EDICode''' - Item's EDI code (alphanumeric, up to 10 digits, rare)
-'''f44 - CWUnit''' -  Catch-Weight Unit.
+'''f51 - UPCCode''' -  Item's UPC code (alphanumeric, up to 12 characters)