Inventory Item Fields
The following field information is intended to assist those who design reports for Sage 50 using Crystal Reports.
Filename: LINEITEM.DAT
LINEITEM.DAT is the inventory items file. It describes each inventory item your company stocks. Most of the information below is entered or displayed in Sage 50's Maintain Inventory Items window.
- To add Sage 50 inventory item fields to your Crystal reports, select LINEITEM.DAT.
Indexes and Their Uses
The LINEITEM.DAT file has 33 keys, as follows:
- By ItemID
- By Description
- By ItemRecordNumber
- By ItemIsInactive and then by ItemID
- By ItemIsInactive and then by Description
- BySKU
- ByItemsMaster
- ByItemsMasterByItemID
- ByItemsMasterByItemDescription
- ByItemsMasterByItemsSecondaryAttribute
- ByItemType
- ByItemMastersID
- ByItemIDByItemsMasterByInactiveByTypeByRecordNumber
- ByGUID
- ByMultiPackByPurchGUID
- ByMultiPackBySalesGUID
- ByQuantityDiscountGUID
- ByItemIDByRecordNumberByInactiveByTypeBySalesThisYearByDescription
- ByItemIDByRecordNumberByInactiveByTypeBySalesNextYearByDescription
- ByPrimaryVendByPartNum
- By category
- By inactive and then by company
- By location
- By inactive and then location
- By StockUM
- By inactive and then StockUM
- By description
- By inactive and then description
- By CustomField1
- By CustomField2
- By CustomField3
- By CustomField4
- By CustomField5
Fields
The following table shows the fields available in the LINEITEM.DAT file and includes the type and size in number of bytes of each field.
|
Field Name |
Type | Size | Comment |
|---|---|---|---|
|
ItemID |
ZString |
20 |
Item ID entered in the Maintain Inventory Items window. This record ID is unique, but it can change (if Sage 50's Change ID feature is used). |
|
GUID |
UBinary |
GUID (Globally Unique Identifier) used for linking the LineItem table. |
|
|
ItemDescription |
ZString |
30 |
The (short) description of the item entered in the Maintain Inventory Items window. This short description (located just under the Item ID field) is commonly used for internal purposes; it appears throughout the Sage 50 application. |
|
ItemRecordNumber |
Integer |
This (internal) item field is used for linking purposes to other Sage 50 data tables. For more information, see Indexes and Their Uses above. This field is unique in the table and never changes. |
|
|
ItemIsInactive |
Logical |
1 |
The Inactive check box in the Maintain Inventory Items window. Valid choices include: 1 = if the item record is inactive. 0 = if the item record is active. |
|
UPC_SKU |
ZString |
20 |
UPC/SKU code for the master stock item. |
|
AnotherUnused |
Integer |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
MasterItemID |
ZString |
20 |
Item ID of the master stock item. |
|
ItemClass |
Integer |
The Item Class field entered on the General tab of the Maintain Inventory Items window. Valid values include: 0 = Non-Stock Item 1 = Stock Item 2 = Description Only 3 = Assembly (Assemblies behave exactly like stock items most of the time. For this reason, assemblies are stored as stock items. When you need to know if an item is an assembly, check the number of components. If > 0, then stock item is really an assembly item.) 4 = Service. 5 = Labor Item. 6 = Activity Item (Sage 50 Premium Accounting and higher) 7 = Charge Item (Sage 50 Premium Accounting and higher) 8 = Master Stock Item (Sage 50 Quantum Accounting) 9 = Substock Item (Sage 50 Quantum Accounting) 10 = Serialized Stock Item (Sage 50 Premium Accounting and higher) 11 = Serialized Assembly Item (Sage 50 Premium Accounting and higher) |
|
|
SSSecondAttribID |
ZString |
4 |
ID of the substock item's secondary attribute. |
|
UseMultiPacks |
Logical |
1 |
The item uses unit of measure information. (Sage 50 Quantum Accounting) |
|
PurchasingUMGUID |
UBinary |
GUID (Globally Unique Identifier) used for linking to the Purchasing UM table. (Sage 50 Quantum Accounting) |
|
|
SalesUMGUID |
Ubinary |
GUID (Globally Unique Identifier) used for linking to the Sales UM table. (Sage 50 Quantum Accounting) |
|
|
QtyDiscountGUID |
Integer |
4 |
GUID (Globally Unique Identifier) used for linking the QtyDisc table. (Sage 50 Quantum Accounting) |
|
SalesDescription |
ZString |
160 |
The item description used for sales transactions. This is entered on the General tab of the Maintain Inventory Items window. |
|
PurchaseDescription |
ZString |
160 |
The item description used for purchase transactions. This is entered on the General tab of the Maintain Inventory Items window. |
|
Category |
ZString |
8 |
Item type entered on the General tab of the Maintain Inventory Items window. |
|
StockingUM |
ZString |
20 |
Stocking unit of measure entered on the General tab of the Maintain Inventory Items window. |
|
SalesAcctRecordNumber |
ZString |
15 |
The G/L sales (income) account used when this item is sold. This field can link to the CHART.DAT file. |
|
TaxFlag |
Integer |
Item Tax Type number selected on the General tab of the Maintain Inventory Items window. Item tax types identifies whether the item is taxable or exempt and are set up in the Inventory Defaults window. Valid values are 1-25. |
|
|
SalesAmt1 [1-42] |
Decimal |
12 |
Sales recorded for this inventory item in the specified period. |
|
SalesQty1 [1-42] |
Decimal |
12 |
Quantity sold of this stock item in the specified period. Note: Quantity amounts are only recorded in Sage 50 for stock items (and assembly items). For all other types of inventory items (non-stock, service, labor, and so on), this field will display 0.00 as the quantity amount. |
|
InvAcctRecordNumber |
Integer |
4 |
The G/L inventory account used when this stock item is purchased or sold. This field can link to the CHART.DAT file. Note: If this item is a non-stock labor, or service item, this field will display default Salary/Wages G/L account rather than an inventory account. Description
only, activity, and charge items do not use this field. |
|
COGSAcctRecordNumber |
Integer |
The G/L cost of sales account used when this item is sold. This field can link to the CHART.DAT file. Note: Description
only, activity, and charge items do not use this field. |
|
|
VendorRecordNumber |
Integer |
This preferred vendor ID entered on the General tab of the Maintain Inventory items window. This field can link to VendorRecordNumber in VENDOR.DAT. |
|
|
PartNumber |
ZString |
20 |
The vendor part number. |
|
Location |
ZString |
10 |
Item location entered on the General tab of the Maintain Inventory Items window. |
|
ReorderPoint |
Float |
Minimum stock level quantity entered on the General tab of the Maintain Inventory Items window for this stock or assembly item. Note: This field is not available for other types of inventory items (non-stock, service, labor, and so on). |
|
|
OrderQty |
Float |
Reorder quantity amount entered on the General tab of the Maintain Inventory Items window for this stock or assembly item. Note: This field is not available for other types of inventory items (non-stock, service, labor, and so on). |
|
|
Note |
ZString |
250 |
Internal note entered for this item. Notes are entered by clicking the Note toolbar button in the Maintain Inventory Items window. |
|
CustomField1 |
ZString |
40 |
Data entered in first inventory item custom field. Notes: Custom field data is entered on the Custom Fields tab of the Maintain Inventory Items window. Labels for inventory item custom fields are set up on the Custom Fields tab of the Inventory Items Defaults window. To get the custom field names, you must use a custom formula. Tell me more about this. |
|
CustomField2 |
ZString |
40 |
Data entered in second inventory item custom field. |
|
CustomField3 |
ZString |
40 |
Data entered in third inventory item custom field. |
|
CustomField4 |
ZString |
40 |
Data entered in fourth inventory item custom field. |
|
CustomField5 |
ZString |
40 |
Data entered in fifth inventory item custom field. |
|
CostMethod |
Integer |
Identifies the costing method for the selected stock, serialized, or assembly item. Valid values include: 0 = Average Cost 1 = FIFO 2 = LIFO 3 = Serialized Note: This field is not relevant for other types of inventory items (non-stock, service, labor, and so on). |
|
|
ItemDontUse61 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel1 |
Integer |
2 |
|
|
Sales1Price1CalcBase |
UBinary |
For sales prices that are calculated (instead of being absolute values), the CalcBase field indicates the base value used for the calculation. 0 = Price Level 1 is the base value 1 = The Current Price is the base value 2 = The Last Cost is the base value 3 = No Base Value. The price is an absolute value, not based on a calculation. Price levels are entered on the
General tab of the Maintain Inventory
Items window. Which price is used is determined by the default unit price
level selected in the customer record. The minimum number of price levels is five (Sage 50 Pro Accounting). The maximum number of price levels is 10 (Sage 50 Premium Accounting and higher). |
|
|
SalesPrice1OpCode |
UBinary |
If a price calculation is applied, this indicates what operation to perform on the base value: 0 = Increase by percentage 1 = Increase by fixed amount 2 = Decrease by percentage 3 = Decrease by fixed amount |
|
|
SalesPrice1Rnd |
UBinary |
If a price calculation is applied, this indicates the type of rounding to be done on the calculated price: 0 = No Rounding 1 = Round to indicated cent 2 = Round to nearest dollar |
|
|
SalesPrice1AdjValue |
Float |
This is the adjustment value is used to adjust the base value indicated by SalesPrice1CalcBase. This field’s value is not used if SalesPrice1CalcBase contains a value of 3 (indicating that no base value used). |
|
|
SalesPrice1RndNo |
Float |
This indicates the cent that the price will be rounded to if the SalesPrice1Rnd field has a value of 1 (Round to indicated cent). |
|
|
PriceLevel1Amount |
Float |
Item price for price level 1 (calculated or absolute). |
|
|
ItemDontUse71 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse62 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel2 |
Integer |
2 |
|
|
SalesPrice2CalcBase |
UBinary |
||
|
SalesPrice2OpCode |
UBinary |
||
|
SalesPrice2Rnd |
UBinary |
||
|
SalesPrice2AdjValue |
Float |
||
|
SalesPrice2RndNo |
Float |
||
|
PriceLevel2Amount |
Float |
Item price for price level 2. |
|
|
ItemDontUse72 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse63 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel3 |
Integer |
2 |
|
|
SalesPrice3CalcBase |
UBinary |
||
|
SalesPrice3OpCode |
UBinary |
||
|
SalesPrice3Rnd |
UBinary |
||
|
SalesPrice3AdjValue |
Float |
||
|
SalesPrice3RndNo |
Float |
||
|
PriceLevel3Amount |
Float |
Item price for price level 3. |
|
|
ItemDontUse73 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse64 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel4 |
Integer |
2 |
|
|
SalesPrice4CalcBase |
UBinary |
||
|
SalesPrice4OpCode |
UBinary |
||
|
SalesPrice4Rnd |
UBinary |
||
|
SalesPrice4AdjValue |
Float |
||
|
SalesPrice4RndNo |
Float |
||
|
PriceLevel4Amount |
Float |
Item price for price level 4. |
|
|
ItemDontUse74 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse65 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel5 |
Integer |
2 |
|
|
SalesPrice5CalcBase |
UBinary |
||
|
SalesPrice5OpCode |
UBinary |
||
|
SalesPrice5Rnd |
UBinary |
||
|
SalesPrice5AdjValue |
Float |
||
|
SalesPrice5RndNo |
Float |
||
|
PriceLevel5Amount |
Float |
Item price for price level 5. |
|
|
ItemDontUse75 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse66 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel6 |
Integer |
2 |
|
|
SalesPrice6CalcBase |
UBinary |
||
|
SalesPrice6OpCode |
UBinary |
||
|
SalesPrice6Rnd |
UBinary |
||
|
SalesPrice6AdjValue |
Float |
||
|
SalesPrice6RndNo |
Float |
||
|
PriceLevel6Amount |
Float |
Item price for price level 6. |
|
|
ItemDontUse76 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse67 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel7 |
Integer |
2 |
|
|
SalesPrice7CalcBase |
UBinary |
||
|
SalesPrice7OpCode |
UBinary |
||
|
SalesPrice7Rnd |
UBinary |
||
|
SalesPrice7AdjValue |
Float |
||
|
SalesPrice7RndNo |
Float |
||
|
PriceLevel7Amount |
Float |
Item price for price level 7. |
|
|
ItemDontUse77 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse68 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel8 |
Integer |
||
|
SalesPrice8CalcBase |
UBinary |
||
|
SalesPrice8OpCode |
UBinary |
||
|
SalesPrice8Rnd |
UBinary |
||
|
SalesPrice8AdjValue |
Float |
||
|
SalesPrice8RndNo |
Float |
||
|
PriceLevel8Amount |
Float |
Item price for price level 8. |
|
|
ItemDontUse78 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse69 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel9 |
Integer |
||
|
SalesPrice9CalcBase |
UBinary |
||
|
SalesPrice9OpCode |
UBinary |
||
|
SalesPrice9Rnd |
UBinary |
||
|
SalesPrice9AdjValue |
Float |
||
|
SalesPrice9RndNo |
Float |
||
|
PriceLevel9Amount |
Float |
Item price for price level 9. |
|
|
ItemDontUse79 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemDontUse610 |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
nPriceLevel10 |
Integer |
||
|
SalesPrice10CalcBase |
UBinary |
||
|
SalesPrice10OpCode |
UBinary |
||
|
SalesPrice10Rnd |
UBinary |
||
|
SalesPrice10AdjValue |
Float |
||
|
SalesPrice10RndNo |
Float |
||
|
PriceLevel10Amount |
Float |
Item price for price level 10. |
|
|
ItemDontUse710 |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
||
|
HasCommission |
Integer |
1 |
1 (True) = The Subject to Commission option is selected for this item on the General tab of the Maintain Inventory Items window. |
|
PrintComponents |
Integer |
1 |
The Print Components on Invoice option on the Bill of Materials tab of the Maintain Inventory Items window. 1 (True) = The whole component list of the selected assembly item prints on the invoice. 0 (False) = Only the assembly item description prints on the invoice. Note: This field is relevant only for assembly items. |
|
LaborCost |
Float |
Identifies the last unit cost for the item that appears on the General tab of the Maintain Inventory Items window. For stock, serialized, or assembly items, you can enter a default cost that is used for cost of sales UNTIL the first purchase is recorded for this item. After that point in time, this field is no longer used for stock or assembly items. However, the Maintain Inventory Items window displays the “last” purchase cost for stock, serialized, and assembly items, which you cannot change. For non-stock, service, and labor items, this field represents the actual cost of sale cost amount. Note: Description
only, activity, and charge items do not use this field. |
|
|
Weight |
Float |
The weight of the item in a consistent unit of measure (for example, grams, ounces, pounds). |
|
|
EmpRecordNumber |
Ubinary |
The ID of the employee designated as buyer for the item. |
|
|
PrimaryAttribName |
ZString |
20 |
Name of the primary attribute for the master stock item. |
|
PrimaryAttribID1...20 |
ZString |
4 |
IDs for the first 20 primary attributes of the master stock item. |
|
PrimaryAttribDesc1...20 |
ZString |
20 |
Descriptions for the first 20 primary attributes of the master stock item. |
|
SecondaryAttribName |
ZString |
20 |
Name of the secondary attribute for the master stock item. |
|
SecondaryAttribID1...20 |
ZString |
4 |
IDs for the first 20 secondary attributes of the master stock item. |
|
SecondaryAttribDesc1...20 |
ZString |
20 |
Descriptions for the first 20 secondary attributes of the master stock item. |
|
SSPrimaryAttribID |
ZString |
20 |
ID of the substock item's primary attribute. |
|
SSPrimaryAttribDesc |
ZString |
20 |
Description of the substock item's primary attribute. |
|
SSSecondAttribDesc |
ZString |
20 |
Description of the substock item's secondary attribute. |
|
ItemUnused7 |
Binary |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
ItemUnused8 |
Binary |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
|
PurchasingUPC_SCC |
ZString |
20 |
|
|
PurchasingWeight |
Float |
The weight of the multi-pack as entered in the Item Multi-Packs window. (Sage 50 Quantum Accounting) |
|
|
DefaultForPurchasing |
Integer |
1 |
The unit of measure ID is the default ID for purchasing. |
|
SalesUPC_SCC |
ZString |
20 |
The UPC/SCC code using the selling unit of measure. (Sage 50 Quantum Accounting) |
|
SalesWeight |
Float |
Item weight using sales unit of measure. |
|
|
DefaultForSales |
Integer |
1 |
Unit of measure ID will be the default ID for sales. (Sage 50 Quantum Accounting) |
|
WarrantySpan |
UBinary |
This is the length of the warranty expressed in either days, months, or years. If the value is zero, no warranty is offered for this item. |
|
|
WarrantyPeriod |
UBinary |
This field tells whether the warranty is expressed in days, months, or years. 0 =Warranty Days 1 = Warranty Months 2 = Warranty Years |
|
|
LastUpdateCounter |
Integer |
This increases incrementally when the inventory item record is changed. |
|
|
LastSavedAt |
TimeStamp |
This time stamp indicates the last time the item was saved. |