Query.createColumn(options)

Note:

The content in this help topic pertains to SuiteScript 2.0.

Method Description

Creates a query result column based on the query.Query object.

The query.Column object is the equivalent of the search.Column object in the N/search Module. The query.Column object describes the field types (columns) that are displayed from the query results.

To create columns:

Use Query.createColumn(options) to create columns on the initial query definition created with query.create(options). Use this method in one of two ways:
- Pass in an argument for the parameter options.fieldId.
- Pass in an argument for the parameter options.formula. If you use this option, you can also use the optional parameter options.type.
If needed, use Component.createColumn(options) to create conditions on the join relationships created with Query.autoJoin(options) and Component.autoJoin(options).
Assign all created columns as array values to Query.columns. For an example, see Syntax.

When you create a column, you can specify a field context. The field context determines how field values are displayed in the column. For example, you can specify that a column should display raw data (such as internal IDs), consolidated or converted amounts (such as currency totals), or user-friendly values (such as names). You can specify a field context in two ways:

Use a context from the query.FieldContext enum directly as the value of the options.context parameter. For example:

                        myTransactionLine.createColumn({ fieldId: 'netamount', context: query.FieldContext.CURRENCY_CONSOLIDATED
});

This example is the simplest way to specify a field context that does not accept additional parameters. Because the options.context parameter is an Object, this example is equivalent to the following:

                        myTransactionLine.createColumn({ fieldId: 'netamount', context: { name: query.FieldContext.CURRENCY_CONSOLIDATED }
});

Use a context from the query.FieldContext enum as the value of the options.context.name parameter, and specify additional parameters using the options.context.params parameter. For example:
```
                        myTransactionLine.createColumn({ fieldId: 'netamount', context: { name: query.FieldContext.CONVERTED, params: { currencyId: 4, date: new Date('2019/01/01') } }
}); 

                      
```
In this example, the created column displays the value of the netamount currency field using the exchange rate that was in effect on January 1, 2019 for the currency with an ID of 4.

In this release, only the query.FieldContext.CONVERTED context uses additional parameters. The supported parameters are currencyId and date. For the date parameter, you can pass a JavaScript Date object or query.RelativeDate object. If you pass a query.RelativeDate object using a value from the query.RelativeDateRange enum, use the start property or end property to specify the exact date of the exchange rate. For example, to use the exchange rate that was in effect at the beginning of the last fiscal quarter:

                    myTransactionLine.createColumn({ fieldId: 'netamount', context: { name: query.FieldContext.CONVERTED, params: { currencyId: 4, date: query.RelativeDateRange.LAST_FISCAL_QUARTER.start } }
});

If you use only the query.RelativeDate object from the query.RelativeDateRange enum and do not specify either the start or end properties, the end date of the relative date range is used. This behavior means that the following two date properties are equivalent:

date: query.RelativeDateRange.LAST_FISCAL_QUARTER
date: query.RelativeDateRange.LAST_FISCAL_QUARTER.end

Note:

This method is a shortcut for the chained Query.root and Component.createColumn(options): Query.root.createColumn(options). The Query.root property references the root component, which is a query.Component object.

Returns

query.Column

Supported Script Types

Client and server scripts

For more information, see SuiteScript 2.x Script Types.

Governance

None

Module

N/query Module

Parent Object

query.Query

Sibling Object Members

Query Object Members

Since

2018.1

Parameters

Note:

The options parameter is a JavaScript object.

Parameter	Type	Required / Optional	Description
`options.fieldId`	string	required if `options.formula` is not used	The field ID of the query result column. This value sets the Column.fieldId property. Obtain this value from the Records Catalog. The Records Catalog lists every record type and field that is available using SuiteAnalytics Workbook and the N/query module. For more information, see Records Catalog Overview.
`options.formula`	string	required if `options.fieldId` is not used	The formula used to create the query result column. This value sets the Column.formula property. For more information about formulas, see Formulas in Search and SQL Expressions.
`options.type`	string	required if `options.formula` is used	If you use the `options.formula` parameter, use this parameter to explicitly define the formula’s return type. This value sets the Column.type property. Use the appropriate query.ReturnType enum value to pass in your argument. This enum holds all the supported values for this parameter.
`options.aggregate`	string	optional	Use this parameter to run an aggregate function on your query result column. An aggregate function performs a calculation on the column values and returns a single value. This value sets the Column.aggregate property. Use the appropriate query.Aggregate enum value to pass in your argument. This enum holds all the supported values for this parameter.
`options.alias`	string	optional	The alias for the column. An alias is an alternate name for a column, and the alias is used in mapped results. This value sets the Column.alias property. You must specify an alias in certain situations if you want to use ResultSet.asMappedResults() or Result.asMap(). For more information, see Column.alias.
`options.groupBy`	boolean	optional	Indicates whether the query results are grouped by this query result column. This value sets the Column.groupBy property. If you do not pass in an argument, the default value is set to `false`.
`options.label`	string	optional	The label for the column. A label is important if the query object is used as the data source for printing (for example, in the TemplateRenderer.addQuery(options) method in the N/render module).
`options.context`	Object	optional	The field context for values in the query result column. This value sets the Column.context property. If you do not pass in an argument, the default value is set to `query.FieldContext.RAW`.
`options.context.name`	string	required if `options.context` is used	The name of the field context. Use the appropriate query.FieldContext enum value to pass in your argument. This enum holds all the supported values for this parameter.
`options.context.params`	Object	required if `options.context.name` has a value of `query.FieldContext.CONVERTED`	The additional parameters to use with the specified field context. In this release, only the `query.FieldContext.CONVERTED` context uses additional parameters. The supported parameters are `currencyId` and `date`.
`options.context.params.currencyId`	number	required if `options.context.name` has a value of `query.FieldContext.CONVERTED`	The ID of the currency to convert to.
`options.context.params.date`	query.RelativeDate \| JavaScript Date	required if `options.context.name` has a value of `query.FieldContext.CONVERTED`	The date to use for the exchange rate between the base currency and the currency to convert to. For example, if you want to use the exchange rate that was in effect on March 3, 2019, specify a query.RelativeDate object or JavaScript `Date` object that represents this date.

Parameter

Type

Required / Optional

Description

options.fieldId

string

required if options.formula is not used

The field ID of the query result column. This value sets the Column.fieldId property.

Obtain this value from the Records Catalog. The Records Catalog lists every record type and field that is available using SuiteAnalytics Workbook and the N/query module. For more information, see Records Catalog Overview.

options.formula

string

required if options.fieldId is not used

The formula used to create the query result column. This value sets the Column.formula property.

For more information about formulas, see Formulas in Search and SQL Expressions.

options.type

string

required if options.formula is used

If you use the options.formula parameter, use this parameter to explicitly define the formula’s return type. This value sets the Column.type property.

Use the appropriate query.ReturnType enum value to pass in your argument. This enum holds all the supported values for this parameter.

options.aggregate

string

optional

Use this parameter to run an aggregate function on your query result column. An aggregate function performs a calculation on the column values and returns a single value. This value sets the Column.aggregate property.

Use the appropriate query.Aggregate enum value to pass in your argument. This enum holds all the supported values for this parameter.

options.alias

string

optional

The alias for the column. An alias is an alternate name for a column, and the alias is used in mapped results. This value sets the Column.alias property.

You must specify an alias in certain situations if you want to use ResultSet.asMappedResults() or Result.asMap(). For more information, see Column.alias.

options.groupBy

boolean

optional

Indicates whether the query results are grouped by this query result column. This value sets the Column.groupBy property.

If you do not pass in an argument, the default value is set to false.

options.label

string

optional

The label for the column.

A label is important if the query object is used as the data source for printing (for example, in the TemplateRenderer.addQuery(options) method in the N/render module).

options.context

Object

optional

The field context for values in the query result column. This value sets the Column.context property.

If you do not pass in an argument, the default value is set to query.FieldContext.RAW.

options.context.name

string

required if options.context is used

The name of the field context.

Use the appropriate query.FieldContext enum value to pass in your argument. This enum holds all the supported values for this parameter.

options.context.params

Object

required if options.context.name has a value of query.FieldContext.CONVERTED

The additional parameters to use with the specified field context.

In this release, only the query.FieldContext.CONVERTED context uses additional parameters. The supported parameters are currencyId and date.

options.context.params.currencyId

number

required if options.context.name has a value of query.FieldContext.CONVERTED

The ID of the currency to convert to.

options.context.params.date

query.RelativeDate | JavaScript Date

required if options.context.name has a value of query.FieldContext.CONVERTED

The date to use for the exchange rate between the base currency and the currency to convert to.

For example, if you want to use the exchange rate that was in effect on March 3, 2019, specify a query.RelativeDate object or JavaScript Date object that represents this date.

Syntax

Important:

The following code sample shows the syntax for this member. It is not a functional example. For a complete script example, see N/query Module Script Samples.

            // Add additional code
...
var myCustomerQuery = query.create({ type: query.Type.CUSTOMER
}); var mySalesRepJoin = myCustomerQuery.autoJoin({ fieldId: 'salesrep'
}); myCustomerQuery.columns = [ myCustomerQuery.createColumn({ fieldId: 'entityid' }), myCustomerQuery.createColumn({ fieldId: 'id' }), mySalesRepJoin.createColumn({ fieldId: 'entityid' }), mySalesRepJoin.createColumn({ fieldId: 'email' }), mySalesRepJoin.createColumn({ fieldId: 'hiredate' })
]; myCustomerQuery.sort = [ myCustomerQuery.createSort({ column: myCustomerQuery.columns[1] }), mySalesRepJoin.createSort({ column: mySalesRepJoin.columns[0], ascending: false })
]; var resultSet = myCustomerQuery.run();
...
// Add additional code

Parameters

Syntax

Related Topics