N/query column definition

Question

I'm trying to get transaction data using the N/query module:

require([ 'N/query'],
function(query) {
        var trxQuery = query.create({ type: query.Type.TRANSACTION });

        trxQuery.columns = [
            trxQuery.createColumn({ fieldId: 'externalid' }),
            trxQuery.createColumn({ fieldId: 'account' }),
            trxQuery.createColumn({ fieldId: 'fxamount' })
       ];

      var results = trxQuery.run();
})

The help says next thing about setting of fieldId in creating the query column:

Obtain this value (fieldId) from the Records Browser.

1. Go to the appropriate record type.
2. Scroll until you see the Search Columns table.
3. Locate the appropriate value in the Internal ID column.

However, I get the following error: {"type":"error.SuiteScriptError","name":"SSS_SEARCH_ERROR_OCCURRED","message":"Search error occurred: Field 'account' for record 'transaction' was not found." ...

The same thing happens for fxamount field.

How can I define query column in order to get data on Account and Amount (Foreign Currency) for transactions by using N/query module?

Answer 1

The N/query module allows you to use the same logic as the Analytics Workbooks feature in the UI. For some reason, these workbooks do not always have the same field names that are in the Record Browser. For my two cents, the N/search module is still the way to go. N/query appears to be much slower than N/search.

In the Workbook UI, you can click the info icon next to each field name and see the field ID needed for the query module.

In your example, fxamount for a search is probably foreigntotal in a query.

Also, there doesn't appear to be an account field at the top level transaction or transaction line level. At the Transaction Accounting Line level, there IS an account field.

I'm not sure if its the account field that you're looking for, but this code seems to work.

var trxQuery = query.create({
  type: query.Type.TRANSACTION
});

trxQuery.columns = [
  trxQuery.createColumn({ fieldId: 'id' }),
  trxQuery.createColumn({ fieldId: 'transactionlines.accountingimpact.account' }),
  trxQuery.createColumn({ fieldId: 'foreigntotal' })
];

var results = trxQuery.run();