Formulas in the N/query Module

When you create a query using the N/query module, you can specify columns and conditions for the query. Columns describe the field types (or columns) that are displayed from the query results, and conditions narrow the query results based on certain criteria. You create a column using Query.createColumn(options), and you create a condition using Query.createCondition(options). Both of these methods let you create a column or condition in two ways:

Use the fieldId parameter to explicitly specify the field on which to create the column or condition.
Use the formula parameter to specify a formula to create the column or condition.

You can use formulas to perform a calculation to determine the column or condition value based on the values of other fields in the record. For example, consider a situation in which you are working with Customer records that include custom fields. These custom fields contain the amount of stock for various items (50 units of item A, 24 units of item B, and so on). In your query results, you want to include a column that calculates and displays the total amount of stock for all items for a Customer. If the Customer records include three custom stock fields, you can create the result column as follows:

          query.createColumn({
    formula: '{item_A_stock} + {item_B_stock} + {item_C_stock}',
    type: query.ReturnType.INTEGER
});

When you use a formula to create a column or condition, you must also use the type parameter to specify the return type of the formula. This parameter accepts values from the query.ReturnType enum. Defining the formula's return type might be required if the return type cannot be determined automatically based on the formula. When you set the type parameter, the return value is properly formatted based on the data type that you specify.

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

Formulas in Joined Queries

You can join your queries with other record types. Joining queries lets you obtain and display query results with field values from multiple record types. When you use a formula in a joined query, you must use fully qualified field IDs to access the fields in each joined record type. You must specify the full join trail from the base record type. The join trail differs depending on the record types and join type.

Use the ^ and < operators to access fields in joined queries. You can use these operators when working with formulas in SuiteScript or the NetSuite UI. Use the ^ operator to access fields in record types that are joined using Query.joinTo(options) or Component.joinTo(options). This type of join is also known as a polymorphic join. Use the < operator to access fields in record types that are joined using Query.joinFrom(options) or Component.joinFrom(options). This type of join is also known as an inverse join. When you use Query.autoJoin(options) or Component.autoJoin(options), you do not need to use the ^ or < operators to access fields in the joined query.

The following table lists common join operations and the corresponding join trail.

Join Type	Join Operation	Join Trail
Automatic using Query.autoJoin(options) or Component.autoJoin(options)	`// The base record type is Customer var myCustomerQuery = query.create({ type: query.Type.CUSTOMER }); // The joined record type is Employee var mySalesRepJoin = myCustomerQuery.autoJoin({ fieldId: 'salesrep' });`	Base record fields (Customer) `customer.<baseFieldName>` Example: `customer.email` Joined record fields (Employee) `customer.salesrep.<joinedFieldName>` Example: `customer.salesrep.phone`
Polymorphic using Query.joinTo(options) or Component.joinTo(options)	`// The base record type is Transaction var myTransactionQuery = query.create({ type: query.Type.TRANSACTION }); // The joined record type is Employee var myEmployeeJoin = myTransactionQuery.joinTo({ fieldId: 'createdby', target: 'employee' });`	Base record fields (Transaction) `transaction.<baseFieldName>` Example: `transaction.entity` Joined record fields (Employee) `transaction.<baseFieldName>^employee.<joinedFieldName>` Example: `transaction.createdby^employee.email`
Inverse using Query.joinFrom(options) or Component.joinFrom(options)	`// The base record type is Employee var myEmployeeQuery = query.create({ type: query.Type.EMPLOYEE }); // The joined record type is Transaction var myTransactionJoin = myEmployeeQuery.joinFrom({ fieldId: 'entity', source: 'transaction' });`	Base record fields (Employee) `employee.<baseFieldName>` Example: `employee.entityid` Joined record fields (Transaction) `employee.<baseFieldName><transaction.daysoverduesearch` Example: `employee.entity<transaction.daysoverduesearch`

Join Type

Join Operation

Join Trail

Automatic using Query.autoJoin(options) or Component.autoJoin(options)

                      // The base record type is Customer
var myCustomerQuery = query.create({
    type: query.Type.CUSTOMER
});

// The joined record type is Employee
var mySalesRepJoin = myCustomerQuery.autoJoin({
    fieldId: 'salesrep'
});

Base record fields (Customer)

customer.<baseFieldName>
Example: customer.email

Joined record fields (Employee)

customer.salesrep.<joinedFieldName>
Example: customer.salesrep.phone

Polymorphic using Query.joinTo(options) or Component.joinTo(options)

                      // The base record type is Transaction
var myTransactionQuery = query.create({
    type: query.Type.TRANSACTION
});

// The joined record type is Employee
var myEmployeeJoin = myTransactionQuery.joinTo({
    fieldId: 'createdby',
    target: 'employee'
});

Base record fields (Transaction)

transaction.<baseFieldName>
Example: transaction.entity

Joined record fields (Employee)

transaction.<baseFieldName>^employee.<joinedFieldName>
Example: transaction.createdby^employee.email

Inverse using Query.joinFrom(options) or Component.joinFrom(options)

                      // The base record type is Employee
var myEmployeeQuery = query.create({
    type: query.Type.EMPLOYEE
});

// The joined record type is Transaction
var myTransactionJoin = myEmployeeQuery.joinFrom({
    fieldId: 'entity',
    source: 'transaction'
});

Base record fields (Employee)

employee.<baseFieldName>
Example: employee.entityid

Joined record fields (Transaction)

employee.<baseFieldName><transaction.daysoverduesearch
Example: employee.entity<transaction.daysoverduesearch

Join Trail Formatting

When you use join trails to access fields in joined queries, you can add whitespace characters and parentheses to improve the readability of your formulas. For example, consider this join trail:

employee.entity<transaction.daysoverduesearch

The following join trails are equivalent to this one:

employee.entity < transaction.daysoverduesearch
employee.(entity<transaction).daysoverduesearch

General Notices