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.

A basic SQL statement is SELECT * FROM ItemView

The Class column is coded: the value 1 indicates that the item is a part, while the value 2 indicates 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 displayed 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:

SELECT CompliesState = CASE

 WHEN [Complies]<>0 THEN 'Pass'

 WHEN [Complies]=0 THEN 'Fail'

 ELSE 'Unknown'

 END

FROM ItemView

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

Release state IsCanceled Revision PendingRevision
Pending false null not null
Released false not null null
Released (with Pending) false not null not null
Canceled true not null null
Canceled (with Pending) true not null not null

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 redefined or eliminated in a future PDXpert release. These columns are not documented, and you should ignore them in your SQL queries. Do not create any custom attributes that use the HK prefix. You may want to create a user-defined view that excludes the housekeeping columns.

The column ordering 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.

Column name Data type Attribute name (note 1)
ItemId GUID (item primary key) unique record identifier; not visible within PDXpert client
Class integer 1 (part) or 2 (document) item Class
Owner Organizations Name item Owner
OwnerShort Organizations Display name item Owner
IsHomeItem (note 2) Boolean calculated; not visible within PDXpert client
Type Document Types or Part Types Name item Type
TypeShort Document Types or Part Types Abbreviation item Type
Number string item Number
RevID GUID (iteration primary key) unique released (or canceled) record identifier;
not visible within PDXpert client
IsCanceled Boolean released iteration has been canceled
not visible within PDXpert client
Revision string released (or canceled) iteration's Revision
RevisionNote string released (or canceled) iteration's Revision description
PendingRevID GUID (revision primary key) unique pending record identifier;
not visible within PDXpert client
PendingRevision string pending iteration's Revision value
PendingLifecycle Item Lifecycle Phases Name pending iteration's Lifecycle phase
PendingRelativeMaturity Item Lifecycle Phases Relative maturity integer pending iteration's Lifecycle phase maturity value
PendingRevisionNote string pending iteration's Revision description
ReleasedOn date released iteration Released date
EffectiveOn date released iteration Effective date (document) or Effectivity (assembly)
Description string item Document title or Part name
Lifecycle Item Lifecycle Phases Name released iteration's Lifecycle phase
RelativeMaturity Item Lifecycle Phases Relative maturity integer released iteration's Lifecycle phase maturity value
Version string released iteration Version
Trustee Persons Name item Trustee
DefaultUoM Units of Measure Name part Default unit of measure
MakeBuy Make/Buy Categories Name part Make/buy
GlobalNumber string part Global number (GTIN, UPC, etc.)
Location string part Location
Handling Handling/Storage Categories Name part Handling/storage advisory
Recovery Recovery Methods Name part End of life recovery method
CostPerUnit real number part Unit cost per default unit value
CostCurrencyShort Currencies Code part Unit cost per default unit: Currency
CostDate date part Unit cost per default unit: As on
PackageQty integer part Standard packaging quantity value
PackageUOM Unit of Measures Name part Standard packaging quantity units
Certify Boolean part Part requires: Certification
Serialize Boolean part Part requires: Serialization
Complies Boolean part Meets regulatory requirements
Notes string item Notes
ProductFamily string semicolon-delimited list of item Product families
Mass double part Part mass (weight) value
MassUOM Mass / weight Name part Part mass (weight) unit of measure
custom (note 3) string custom attribute text or numeric value
custom_Member (note 3) collection member Name custom attribute value's units, or other collection member

Notes:

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

1173

Learn More
Help Guide Contents [PDF]