Table of Contents

<partsFromQuery> Element

This element specifies parts for a component type from a query.

Parent Elements

componentType, rejectParts

Attributes

Name Type Description Required
query string The query that will be executed to retrieve the parts list. Yes
componentAlternateColumn string The name of the query result column that contains componentAlternate part values. See the remarks for details. No (defaults to componentAlternate)
componentModelNumColumn string The name of the query result column that contains componentModelNum part values. See the remarks for details. No (defaults to componentModelNum)
componentSerialNumColumn string The name of the query result column that contains componentSerialNum part values. See the remarks for details. No (defaults to componentSerialNum)
connectionString string The connection string used to establish the connection before performing the query. If this is not specified, a connection to the Acuit Pinpoint database for the line will be used. No
descriptionColumn string The name of the query result column that contains description part values. See the remarks for details. No
ignoreBlankComponentRows boolean Whether blank component rows should be ignored. Component rows are considered blank if both the component model number and component serial number are blank. No (defaults to false)
providerName string The name of the data provider to use for the query. If this is not specified, the default Microsoft SQL Server provider will be used. If connectionString is not specified, then this must not be specified. See the remarks for the list of supported providers. No
refreshMinutes integer Specifies how often the query will be performed to refresh the parts list. If neither refreshMinutes nor refreshWatchFile are specified, then Acuit Pinpoint Server will query every fifteen minutes. No
refreshWatchFile string Specifies the full path to a file that Acuit Pinpoint Server will watch for changes; whenever the file modification time changes, the query will be performed to refresh the parts list. If neither refreshMinutes nor refreshWatchFile are specified, then Acuit Pinpoint Server will query every fifteen minutes. No
unitModelNumColumn string The name of the query result column that contains unitModelNum part values. See the remarks for details. No (defaults to unitModelNum)
unitSerialNumColumn string The name of the query result column that contains unitSerialNum part values. See the remarks for details. No (defaults to unitSerialNum)

Child Elements

Name Description Required
otherComponents Other components against which part entries must match.
This feature is available in Acuit Pinpoint Workstation version 7.13 or later.		 No

Remarks

This defines a SQL query that will be used to build a parts list from the query result. Each row from the query result will produce a parts list entry, effectively creating a part as defined by a <part> element (see that element for more details about part entry values).

For each of the column attributes, if the column name is not specified or is blank, or if a column with the specified name does not exist in the query result, or if the column's field value in a query result row is null or blank or only white space, then the corresponding part value will not be set (i.e., it will be as if the corresponding part attribute were not specified).

Values from the query result that do exist are trimmed of leading and trailing white space.

The following data providers are supported:

Provider Name Description
Microsoft.Data.SqlClient The data provider for Microsoft SQL Server. This is the default provider that is used when providerName is not specified.
System.Data.Odbc The data provider for ODBC.
System.Data.OleDb The data provider for OLE DB.
System.Data.OracleClient The data provider for Oracle.
Note

The System.Data.SqlClient data provider is no longer supported in version 8. Microsoft.Data.SqlClient should be used instead.

query can specify any query supported by the selected data provider, as long as it results in a table with column names that match those specified by unitModelNumColumn, unitSerialNumColumn, componentModelNumColumn, componentSerialNumColumn, and componentAlternateColumn (case-insensitive). Refer to <part> Element for descriptions of these attributes. Any of these column names not included as columns in the query will act as if the corresponding part attributes were not specified.

The componentAlternateColumn data value is parsed as follows:

  1. If componentAlternateColumn was not specified, false is used.
  2. Otherwise, if the column is nullable and the field is NULL, false is used.
  3. Otherwise, if the column data type is a boolean type (e.g., a bit Microsoft SQL Server data type), its value is used directly.
  4. Otherwise:
    1. The value is converted to a string, and all leading and trailing whitespace is removed.
    2. If the resulting string value is "True", "Yes", "Alternate", "Alt", or "A" (case-insensitive), then true is used.
    3. Otherwise, if the value is "False" or "No" (case-insensitive), then false is used.
    4. Otherwise, if the value can be parsed as an integer, then true is used if the integer value is non-zero; false is used if the integer value is zero.
    5. Otherwise, true is used if the trimmed string value is not empty; false is used if it is empty.

The order of the returned set determines the order in which the parts are added to the parts list.

By default, Acuit Pinpoint Server executes the query every fifteen minutes, as needed, so changes made to the components parts list data while Acuit Pinpoint Server is running will take effect in fifteen minutes or less. refreshMinutes or refreshWatchFile can be used to change this default behavior. Note that both can be specified at the same time. To force Acuit Pinpoint Server to query the components parts list data immediately, do one of the following:

  • Restart the Acuit Pinpoint Server service, or
  • Edit the line configuration and save the changes.

Examples

Parts List in SQL Server Database Table

A custom table can be added to the line database to contain the parts list. For example, the following SQL command could be used to create a custom table to hold the compressor models parts list:

CREATE TABLE [CompressorModels] (
    [UnitModelNum] [varchar] (100) NOT NULL,
    [UnitSerialNum] [varchar] (100) NOT NULL,
    [ComponentModelNum] [varchar] (100) NOT NULL,
    [ComponentSerialNum] [varchar] (100) NOT NULL,
    [ComponentAlternate] [bit] NOT NULL
)

The parts list query for this table could be specified like this:

<partsList query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentSerialNum, ComponentAlternate FROM CompressorModels" />

Since providerName and connectionString were not specified, the query acts against the line database. A different Microsoft SQL Server database can be specified by providing a value for connectionString.

Parts List in Excel Spreadsheet

Note

A better method of retrieving parts data from an Excel spreadsheet is to use a <table> Element configured with the Excel spreadsheet as the source file, and then retrieve the parts via <partsFromTable> Element.

The parts list can be retrieved from external files using the Microsoft Jet OLE DB data provider. To read the parts list from an Excel file, the parts list query could look like this:

<partsList
  providerName="System.Data.OleDb"
  connectionString='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PartsLists\Compressors.xls;Extended Properties="Excel 12.0 Xml;HDR=Yes"'
  query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentAlternate FROM [Sheet1$]" />

The Excel spreadsheet is assumed to have a sheet named "Sheet1" which contains a table of values, with the first row containing headers above the columns named UnitModelNum, UnitSerialNum, ComponentModelNum, and ComponentAlternate.

This requires the 64-bit version of Microsoft Excel 2007 or later (or the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable from http://www.microsoft.com/download/en/details.aspx?id=13255) to be installed on the computer hosting Acuit Pinpoint Server.

Parts List in Comma-separated Values File

Note

A better method of retrieving parts data from an Excel spreadsheet is to use a <table> Element configured with the comma-separated values file as the source file, and then retrieve the parts via <partsFromTable> Element.

The parts list can be retrieved from external files using the Microsoft Jet OLE DB data provider. To read the parts list from a comma-separated values file, the parts list query could look like this:

<partsList
  providerName="System.Data.OleDb"
  connectionString='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PartsLists;Extended Properties="text;HDR=Yes;FMT=Delimited"'
  query="SELECT UnitModelNum, UnitSerialNum, ComponentModelNum, ComponentAlternate FROM [Compressors.csv]" />

The comma-separated values file is assumed to contain a table of values, with the first row containing headers above the columns named UnitModelNum, UnitSerialNum, ComponentModelNum, and ComponentAlternate.

This requires the 64-bit version of Microsoft Excel 2007 or later (or the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable from http://www.microsoft.com/download/en/details.aspx?id=13255) to be installed on the computer hosting Acuit Pinpoint Server.