Get Table Fields

The get_table_fields tool lists the columns of one table and the data type of each column. Use the column names in the Query Analytics Data and Query Business Data tools. The get_table_fields tool does not read any rows. To find a table and its identifiers, use the List Tables tool.

Tool Name – get_table_fields

Title – Get Table Fields

Type – Private

Access Profile – Operator

Permissions

The tool is available to the Operator access profile when tool access control is on. The get_table_fields tool uses your Pricefx permissions and lists only the columns that you have permission to query. When you do not have access to a table, the tool returns the same error as for a table that does not exist.

Parameters

Parameter

Required

Description

kind

Yes

The kind of table, for example datamart or products. The available kinds are the same as for the list_tables tool.

table_id

Depends on kind

The first identifier of the table, as the list_tables tool returns it in tableId. Required for the kinds in the Table Identifiers table on this page. Empty for every other kind.

table_id_2

Depends on kind

The second identifier of the table, as the list_tables tool returns it in tableId2. Required only for modelTable. Optional for companyParameterRows. Empty for every other kind.

filter

No

Text to narrow the list. The tool returns the columns whose name or label contains this text. The comparison ignores case.

Default: Empty, which lists every column.

Table Identifiers

Kind

table_id

table_id_2

datamart

Unique name of the Datamart

–

dataSource

Unique name of the Data Source

–

rollup

Label of the Rollup

–

modelTable

Unique name of the Model Object

Name of the table

productExtensionRows

Name of the Product Extension

–

customerExtensionRows

Name of the Customer Extension

–

sellerExtensionRows

Name of the Seller Extension

–

priceListLineItems

ID of the Price List

–

priceGridLineItems

ID of the Live Price Grid

–

calculationGridLineItems

ID of the Calculation Grid

–

companyParameterRows

Unique name of the Company Parameter table

Target date (optional)

conditionRecords

Unique name of the Condition Record Set

–

The pricing kinds in this table (priceListLineItems, priceGridLineItems, calculationGridLineItems, companyParameterRows, and conditionRecords) are available only when Pricefx enables pricing tables for your partition.

Output

The get_table_fields tool returns an object with the following fields.

Field

Description

kind

The kind of the table.

tableId

The first identifier of the table. Empty when the kind needs none.

tableId2

The second identifier of the table. Empty when the kind needs none.

total

The number of columns returned. When filter is set, total counts only the matching columns.

fields

The returned columns. Each column has the fields in the Column Fields table on this page.

Column Fields

Field

Description

name

The column name to use in the query tools.

label

The display label of the column. The column name when there is no label.

type

The data type of the column, for example STRING, REAL, INTEGER, DATE_ONLY, DATE_TIME, or BOOLEAN.

description

The business description of the column. Present only when the partition has one.

paType

The field type in Pricefx Analytics, for example MONEY, NUMBER, or QUANTITY. Datamarts and Data Sources only.

dimension

true when the column is a dimension. Datamarts and Data Sources only.

measure

true when the column is a numeric column that is not a dimension. Datamarts and Data Sources only.

expression

The formula of a calculated column. Datamarts and Data Sources only.

Behavior and Limitations

  • The get_table_fields tool describes one table per call and reads no rows.

  • Column names differ between tables. For example, the product column is sku in Live Price Grid line items and Product Id in Products. Use each name in the query tools exactly as the get_table_fields tool returns it, including spaces.

  • A number stored in a STRING column sorts as text and cannot be summed.

  • The paType, dimension, measure, and expression fields are present only for Datamarts and Data Sources.

Errors

Situation

Error Message

kind is not an available table kind

Unsupported table kind 'X'. Please use one of: Y.

An identifier is given for a kind that does not take it

Table kind 'X' takes no identifier. or Table kind 'X' takes only N identifier(s): Y.

A required identifier is empty

Table kind 'X' needs Y.

An identifier has the wrong format, for example a Price List ID that is not a number

Invalid identifier for table kind 'X' (Y): Z.

The table does not exist, or you do not have access to it

No X table with these identifiers is available to you.