Does anyone know where the Item Type table is in Sage 500?


Tina McLauchlan, Business Analyst

Industrial Fabricators, Inc.

2408 Forbes Road

Gastonia, NC 28052

(704) 864-3032 Ex 211

“Carpe Diem”

  • 0

    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):

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

    LV.TableName = 'timItem'
    AND LV.ColumnName = 'ItemType'

    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.

  • 0 in reply to Ramon M.

    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.

    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

  • 0

    I'm going to throw my response in as well. A direct answer is that ItemType is part of the metadata collection, so if you review the data in timItem it is generally translated in the UI as:

    1 Misc Item
    2 Service
    3 Expense
    4 Comment Only
    5 Finished Good
    6 Raw Material
    7 BTO Kit
    8 Assembled Kit

    Presumably, you have access to run queries against the data, so a somewhat simpler format to determine the values for metadata columns is typically found by employing a view that Sage created for the list validation lookup in a query that looks something like the code here.

            [timItemDescription].[ShortDesc] AS [Short Description],
            [ItemTypeDesc].[LocalText] AS [Item Type Description]
            INNER JOIN [dbo].[timItemDescription]
                ON [timItem].[ItemKey] = [timItemDescription].[ItemKey]
                AND [timItemDescription].[LanguageID] = 1033
            LEFT OUTER JOIN [dbo].[vListValidationString] AS [ItemTypeDesc]
                ON [timItem].[ItemType] = [ItemTypeDesc].[DBValue]
                AND [ItemTypeDesc].[TableName] = 'timItem'
                AND [ItemTypeDesc].[ColumnName] = 'ItemType'
            [timItem].[CompanyID] = 'SOA';