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:

SELECT

 CASE

  WHEN [Complies]<>0 THEN 'Pass'

  WHEN [Complies]=0 THEN 'Fail'

  ELSE 'Unknown'

  END AS CompliesState

FROM viewer.ItemView

You can use the IsCanceled, Revision and PendingRevision values in the viewer schema 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 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.

Column name Note Data type Attribute name
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 1 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 2 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 2 string released (or canceled) iteration's Revision
RevisionNote 2 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 2 Item Lifecycle Phases Name released iteration's Lifecycle phase
RelativeMaturity 2 Item Lifecycle Phases Relative maturity integer released iteration's Lifecycle phase maturity value
Version 2 string released iteration Version
Trustee   Persons Name item Trustee
DefaultUoM 3 Units of Measure Name part Default unit of measure
MakeBuy 3 Make/Buy Categories Name part Make/buy
GlobalNumber 3 string part Global number (GTIN, UPC, etc.)
Location 3 string part Location
Handling 3 Handling/Storage Categories Name part Handling/storage advisory
Recovery 3 Recovery Methods Name part End of life recovery method
CostPerUnit 3 real number part Unit cost per default unit value
CostCurrencyShort 3 Currencies Code part Unit cost per default unit: Currency
CostDate 3 date part Unit cost per default unit: As on
PackageQty 3 integer part Standard packaging quantity value
PackageUOM 3 Unit of Measures Name part Standard packaging quantity units
Certify 3 Boolean part Part requires: Certification
Serialize 3 Boolean part Part requires: Serialization
Complies 3 Boolean part Meets regulatory requirements
Notes   string item Notes
ProductFamily   string semicolon-delimited list of item Product families
Mass 3 double part Part mass (weight) value
MassUOM 3 Mass / weight Name part Part mass (weight) unit of measure
custom 4 string custom attribute text or numeric value
custom_Member 4 collection member Name custom attribute value's UOM or currency units, or other collection member

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

Help Guide Contents [as PDF]