I've had frequent questions from people trying to build their own views for accessing MAS500 Item information so I thought I'd put it all down in a public place so it can be referred to easily and perhaps assist others that I don't normally work with.

Working with Units of Measure in MAS500 can be a challenge as finding what you see on the screen, in the tables behind the scene is not always cut and dried. Granted, Sage has added numerous Explorer views that make reporting much easier, these views often come with many other tables included and thus lots of extra baggage. So in this discussion, we'll talk about linking to get UOM ID's the quick and low bandwidth way.



In the screen above I've highlighted the section of the Maintain Items screen that shows the Unit of Measure. The section in Red is stored in the timItem table of the MAS500 database. The section in Blue is stored in thetimItemUnitofMeas table.

What you will find in these two tables is not going to be the Unit of Measure text (UOMID) like you see it on the screen. Instead you will find in these tables a column with some type of UOMKey reference. To get the UOMID that you see on the screen, you will need to also work with the table tciUnitMeasure.

The table timItem contains the following UOM fields (easy to find in the schema browser by finding the timItem table and then choosing the Keys link. Any keys that link to tciUnitMeasure.UnitMeasKey are units of measure):

PriceUnitMeasKey - Pricing Unit of Measure
PurchUnitMeasKey - Purchase Unit of Measure
SalesUnitMeasKey - Sales Unit of Measure
StockUnitMeasKey - Stock Unit of Measure

Notice how you have one "key" column for each of the ID fields show in the redbox above.

To get the UOMID for any of the timItem fields, you will need to create a query that joins into the tciUnitMeasure table. For example, if we want to display the ItemID, and the Stocking Unit of Measure ID for an item, we might create a query that looks like this:

SELECT item.CompanyID, item.ItemID , UOM.UnitMeasID AS 'StockUOMID'
FROM dbo.timItem Item
INNER JOIN dbo.tciUnitMeasure uom
ON Item.StockUnitMeasKey = uom.UnitMeasKey
In this case we are joining into tciUnitMeasure on the Item's StockUnitMeasKey and the Unit of Measure table's UnitMeasKey. We can use an INNER JOIN here because MAS500 requires all items to have a Stock UOM entered. If we are going to look at the Purchasing or Sales UOM, we may want to use an OUTER JOIN as not all Item Types require a Purchasing or Sales UOM to be entered. Thus if you wanted to create a report or Explorer view that shows all Items, and if the have it, a Price UOM you might have a query that looks like this:
SELECT item.CompanyID
, item.ItemID
, itemTypes.LocalText AS 'ItemType'
, descr.ShortDesc AS 'Description'
, UOM.UnitMeasID AS 'PriceUOMID'
FROM dbo.timItem Item
INNER JOIN dbo.timItemDescription Descr
ON Item.itemkey = Descr.ItemKey
LEFT JOIN dbo.vListValidationString itemTypes
ON Item.ItemType = itemTypes.DBValue
AND itemTypes.TableName = 'timitem'
AND itemTypes.ColumnName = 'ItemType'
LEFT JOIN dbo.tciUnitMeasure uom
ON Item.PriceUnitMeasKey = uom.UnitMeasKey,
When fetching data that refers to rows in the lower section of the Maintain Items screen (shown in blue) you will need to join in an addtional table. The table timItemUnitOfMeas contains one row for each row shown on the grid. This table contains a column called TargetUnitMeasKey which represents the TargetUOM ID shown on the screen. This table also contains the ConversionFactor, the Unit Volumn, Unit Weight, UPC code, and flags for if it can be used for Purchases or Sales.

If you wanted to list all the Units of Measure from this table for an item you would create a query that looks like this:
SELECT Item.CompanyID
, Item.itemid
, uom.UnitMeasID AS 'TargetUOM ID'
, iUOM.UnitWeight AS 'Unit Weight'
, iUOM.UPC AS 'UPC Code'
FROM dbo.timItem Item
LEFT JOIN dbo.timItemUnitOfMeas iUOM
ON Item.itemkey = iUOM.itemkey
LEFT JOIN dbo.tciUnitMeasure uom
ON iUOM.TargetUnitMeasKey = uom.UnitMeasKey
Here we are using Left Joins since many items, Misc Items for example, will not have any rows in the timItemUnitofMeas table.

Finally, because many UOM columns are stored in one row in the timItem table, it is often necessary to join into the tciUnitofMeasure table multiple times. This is accomplished through creating what are called alias's for tables in your query. In the examples above, I used the alias "item" for the timItem table and "uom" for the tciUnitMeasure table. An alias allows you to reference the same table, in different ways inside a query.

The query below is an example of what might be created if you wanted to see ALL the UOM's assigned to an item, in this case, the item ID used in the screen capture above:
SELECT item.CompanyID
, item.ItemID
, itemTypes.LocalText AS 'ItemType'
, descr.ShortDesc AS 'Description'
, UOM.UnitMeasID AS 'PriceUOMID'
, StockUOM.UnitMeasID AS 'StockingUOMID'
, PurchUOM.UnitMeasID AS 'PurchaseUOMID'
, case
WHEN targetuom.UnitMeasID IS NULL THEN 'Nothing'
ELSE TargetUOM.UnitMeasID
END AS 'TargetUOMID'
, case
WHEN targetuom.UnitMeasID IS NULL THEN ''
ELSE iUOM.UnitWeight
END AS 'Weight per'
FROM dbo.timItem Item
INNER JOIN dbo.timItemDescription Descr
ON Item.itemkey = Descr.ItemKey
LEFT JOIN dbo.vListValidationString itemTypes
ON Item.ItemType = itemTypes.DBValue
AND itemTypes.TableName = 'timitem'
AND itemTypes.ColumnName = 'ItemType'
LEFT JOIN dbo.tciUnitMeasure uom
ON Item.PriceUnitMeasKey = uom.UnitMeasKey
LEFT JOIN dbo.timItemUnitOfMeas iUOM
ON Item.itemkey = iUOM.ItemKey
LEFT JOIN dbo.tciUnitMeasure TargetUOM
ON iUOM.TargetUnitMeasKey = TargetUOM.UnitMeasKey
LEFT JOIN dbo.tciUnitMeasure StockUOM
ON Item.StockUnitMeasKey = StockUOM.UnitMeasKey
LEFT JOIN dbo.tciUnitMeasure PurchUOM
ON Item.PurchUnitMeasKey = PurchUOM.UnitMeasKey
WHERE Item.itemid LIKE '2way%'
AND Item.CompanyID = 'SOA'
Note the use of Left Joins, and all of the joins into tciUnitMeasure. In the future I will try and discuss how to work with the Conversion Factor and how you might link into the tsoSOLine and tsoShipLine to figure out QTY.

--Michael Harms