ItemView & ItemMasterView reference

The ItemView contains all part and document records with standard item attributes. The ItemMasterView contains all columns of the ItemView plus all custom attributes. These views are available in both the viewer and the released schemas.

A basic SQL statement is SELECT * FROM ItemView

The Class column is coded: the value 1 specifies that the item is a part, while the value 2 shows that it's a document. For example:

SELECT ClassName = CASE Class WHEN 1 THEN 'Part' WHEN 2 THEN 'Document' ELSE 'Unknown' END FROM ItemView

All dates are shown using SQL Server's default format. If you need a different format, such as ISO 8601, then a conversion is required. For example:

SELECT convert(varchar(23),CostDate,126) AS IsoCostDate FROM ItemView

Booleans ("checkbox values") are zero or non-zero integers. A value =0 is false while value <>0 is true. Null values are possible. For example:

You can use the IsCanceled, Revision and PendingRevision values in the viewer schema to obtain insight into the item's current release state.

Both of these views are also contained in the local views database. Client-side ODBC applications can select from these views and from SQLite-compatible Views collection members based on these views.

Column definitions §

There may exist in the view one or more "housekeeping" columns that have been reserved for PDXpert's private use. These columns' names begin with the prefix HK (for instance, HK1). Housekeeping columns may be changed or eliminated in a future PDXpert release. These columns are not documented, and you should ignore them in your SQL queries. Do not add any custom attributes that use the HK prefix. You may want to make a user-specified view that excludes the housekeeping columns.

The column sequence may not be exactly as shown in this table, and may change in a future PDXpert release. Use the column name, not ordinal position, in your SQL queries.

Notes:

1. A True value represents a home part or document; a False value is a partner item. This value will never be NULL.
2. If the item has neither a released nor canceled iteration, then the iteration-related value (e.g., RevID) is null.
3. Document rows (that is, where Class is 2) are NULL for part attributes.
4. These columns are included in the ItemMasterView, and are not in the ItemView. Custom attribute ("CA") names are shown in value/unit columns; the name is used in the column header. All identically-named CAs are shown in the same column, regardless of item type. Where a custom attribute name conflicts with a system reserved attribute name (such as Number or Type) then your custom attribute name is shown with a leading underscore (_Number or _Type). See the Custom attributes help topic for naming guidelines. If a custom attribute name is longer than 120 characters, it is truncated. Custom attribute values are NULL if the item hasn't been assigned the custom attribute.

1173