Hey All, Has anyone written a transaction search w...
# suitescript
r
Hey All, Has anyone written a transaction search with the new Query module. At this point I am just trying to duplicate the Default join behavior for a transaction workbook: Transaction > TransactionLine >Item. here is my code:
Copy code
javascript
/**
 *@NApiVersion 2.x
 *@NScriptType ScheduledScript
 *@NModuleScope SameAccount
 */

define(['N/query', 'N/log', 'N/record'],
  function(query, log, record) {

  function execute(){
    log.debug({title: "Running Search", details: ""})
    var transactionQuery = query.create({
      type: query.Type.TRANSACTION
    });

    var lineItems = transactionQuery.autoJoin({fieldId: 'transactionlines'});
    var item = lineItems.autoJoin({fieldId: 'item'});
   /* var itemLocation = item.autoJoin({fieldId: 'locations'}); */
    
    transactionQuery.columns = [
     transactionQuery.createColumn({fieldId: 'tranid'}),
     transactionQuery.createColumn({fieldId: 'trandate'}),
     item.createColumn({fieldId: 'itemid'})
      /*
     transactionQuery.createColumn({fieldId: 'transactionlines.location'}),
     item.createColumn({fieldId: 'locations.location'})
     */
    ];
    
     var cond1 = transactionQuery.createCondition({
      fieldId: 'tranid',
      operator: query.Operator.IS,
      values: 'TO832'
    })
    
    var cond2 = transactionQuery.createCondition({
      fieldId: 'transactionlines.item.locations.location',
      operator: query.Operator.EQUAL,
      values: 2
    });
    
    var cond3 = transactionQuery.createCondition({
      fieldId: 'transactionlines.location',
      operator: query.Operator.EQUAL,
      values: 1
    });
    
    transactionQuery.condition = transactionQuery.and(cond1,cond3/*,cond2*/)
    
    var resultSet = transactionQuery.run();
    
    log.debug({title: 'results: ', details: resultSet});
  }
  return {execute: execute};
  });