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.

Indexes and Their Uses

The LINEITEM.DAT file has 33 keys, as follows:

  1. By ItemID
  2. By Description
  3. By ItemRecordNumber
  4. By ItemIsInactive and then by ItemID
  5. By ItemIsInactive and then by Description
  6. BySKU
  7. ByItemsMaster
  8. ByItemsMasterByItemID
  9. ByItemsMasterByItemDescription
  10. ByItemsMasterByItemsSecondaryAttribute
  11. ByItemType
  12. ByItemMastersID
  13. ByItemIDByItemsMasterByInactiveByTypeByRecordNumber
  14. ByGUID
  15. ByMultiPackByPurchGUID
  16. ByMultiPackBySalesGUID
  17. ByQuantityDiscountGUID
  18. ByItemIDByRecordNumberByInactiveByTypeBySalesThisYearByDescription
  19. ByItemIDByRecordNumberByInactiveByTypeBySalesNextYearByDescription
  20. ByPrimaryVendByPartNum
  21. By category
  22. By inactive and then by company
  23. By location
  24. By inactive and then location
  25. By StockUM
  26. By inactive and then StockUM
  27. By description
  28. By inactive and then description
  29. By CustomField1
  30. By CustomField2
  31. By CustomField3
  32. By CustomField4
  33. 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.
Activity items refer to these fields in the Sage 50 application as billing rate levels. All other item types refer to these fields as sales price levels.

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.

What do you want to do next?