Does anyone know where the Item Type table is in Sage 500?
Thanks,
Tina McLauchlan, Business Analyst
Industrial Fabricators, Inc.
2408 Forbes Road
Gastonia, NC 28052
(704) 864-3032 Ex 211
“Carpe Diem”
Does anyone know where the Item Type table is in Sage 500?
Thanks,
Tina McLauchlan, Business Analyst
Industrial Fabricators, Inc.
2408 Forbes Road
Gastonia, NC 28052
(704) 864-3032 Ex 211
“Carpe Diem”
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT [timItem].[CompanyID], [timItem].[ItemID], [timItemDescription].[ShortDesc] AS [Short Description], [timItem].[ItemType], [ItemTypeDesc].[LocalText] AS [Item Type Description] FROM [dbo].[timItem] 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' WHERE [timItem].[CompanyID] = 'SOA';
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT [timItem].[CompanyID], [timItem].[ItemID], [timItemDescription].[ShortDesc] AS [Short Description], [timItem].[ItemType], [ItemTypeDesc].[LocalText] AS [Item Type Description] FROM [dbo].[timItem] 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' WHERE [timItem].[CompanyID] = 'SOA';
*Community Hub is the new name for Sage City