I do not believe there is an item type table.  The item type is a attribute of an item.  timItem.ItemType is the field that defines this, and allows 8 distinct numeric values enforced by a database constraint.

To see the defined item values and their associated descriptions types in Sage 500 ERP, you can use the following query (sorry, the editor removed the formatting):

SELECT
CONVERT(VARCHAR(40),LV.TableName) 'TableName'
,CONVERT(CHAR(30),LV.ColumnName) 'ColumnName'
,LS.StringNo
,S.ConstantName
,LEFT(LS.LocalText,25) 'LocalText'
,LV.DBValue
FROM
tsmListValidation LV (NOLOCK)
INNER JOIN tsmLocalString LS (NOLOCK) ON
LS.StringNo = LV.StringNo
AND LS.LanguageID = 1033
INNER JOIN tsmString S WITH (NOLOCK) ON
S.StringNo = LS.StringNo

WHERE
LV.TableName = 'timItem'
AND LV.ColumnName = 'ItemType'
ORDER BY
LV.TableName
,LV.ColumnName
,LV.DBValue

When you run the query you can see that timItem.ItemType = 5 is a finished good.

Besides this, there are some big assumptions such as timItem.ItemType values 1-4 are non inventory goods and item types 5 or greater are inventory goods.  Inventory goods have an entry in timInventory, while non-inventory goods such as "expense" or "comment only" item types do not.

I would caution people that adding an item type is a HUGE effort, especially if it is a non-inventory item type.

If this is not what you were actually looking for let me know.

I do not believe there is an item type table.  The item type is a attribute of an item.  timItem.ItemType is the field that defines this, and allows 8 distinct numeric values enforced by a database constraint.

To see the defined item values and their associated descriptions types in Sage 500 ERP, you can use the following query (sorry, the editor removed the formatting):

SELECT
CONVERT(VARCHAR(40),LV.TableName) 'TableName'
,CONVERT(CHAR(30),LV.ColumnName) 'ColumnName'
,LS.StringNo
,S.ConstantName
,LEFT(LS.LocalText,25) 'LocalText'
,LV.DBValue
FROM
tsmListValidation LV (NOLOCK)
INNER JOIN tsmLocalString LS (NOLOCK) ON
LS.StringNo = LV.StringNo
AND LS.LanguageID = 1033
INNER JOIN tsmString S WITH (NOLOCK) ON
S.StringNo = LS.StringNo

WHERE
LV.TableName = 'timItem'
AND LV.ColumnName = 'ItemType'
ORDER BY
LV.TableName
,LV.ColumnName
,LV.DBValue

When you run the query you can see that timItem.ItemType = 5 is a finished good.

Besides this, there are some big assumptions such as timItem.ItemType values 1-4 are non inventory goods and item types 5 or greater are inventory goods.  Inventory goods have an entry in timInventory, while non-inventory goods such as "expense" or "comment only" item types do not.

I would caution people that adding an item type is a HUGE effort, especially if it is a non-inventory item type.

If this is not what you were actually looking for let me know.

Ramon -

Joe pulled together a great blog about how to add this to the Schema information found in Sage.  Would be a great add to a product release.  Step by step instructions of how to add it to 500 are in the blog.

https://www.rklesolutions.com/blog/sage-500-erp-bi-view-data-column-field-level-information

He added this so that it could be launched right from the menu (screen shot #1).  Which then allows you to filter by TableName and ColumnName.  Or as seen in the second screen shot below you can see it as a preview in the Tables Explorer view.

Screen Shot #1:

Screen Shot #2:

Russ Griffith

RKL eSolutions, LLC