N/query module – Script Sample

/**
 * @NApiVersion 2.x
 * @NScriptType Suitelet
 * @NModuleScope SameAccount
 */
/*******************************************************************************
 * 
 * Author : Jobin and jismi created on:02/8/2019 SV
 * 
 ******************************************************************************/
 define(["N/query","N/render","N/runtime"], function(query,render,runtime) {




  function onRequest(context)
  {

    try{


//Create Query
var myCustomerQuery = query.create({
    type: query.Type.CUSTOMER
});

//Join Salesrep to the Query : Auto Join
var mySalesRepJoin = myCustomerQuery.autoJoin({fieldId: 'salesrep'});

//Join department to the Query : Auto Join
var myDeptJoin = mySalesRepJoin.autoJoin({fieldId:'department'});


//Join the transactions with entity to the customer Query : Join From
var soJoin = myCustomerQuery.joinFrom({fieldId: 'entity',source:'transaction'});

//Join the employee in the field createdby within the customer record  to customer Query
var myEmployeeJoin = soJoin.joinTo({fieldId: 'createdby', target: 'employee'});



//Add columns

myCustomerQuery.columns=[
                          myCustomerQuery.createColumn({fieldId: 'entityid'}),//entity id from the customer query
                          myDeptJoin.createColumn({fieldId: 'name'}),//Name from the department
                          mySalesRepJoin.createColumn({fieldId: 'entityid'}),//entity id from the salesrep join
                          soJoin.createColumn({fieldId: 'trandate'}),//trandate from the transaction join
                          soJoin.createColumn({fieldId: 'id'}),//id from the transaction join
                          myEmployeeJoin.createColumn({fieldId: 'entityid'}),//entity id form the employee who created the customer
                          myEmployeeJoin.createColumn({fieldId: 'id'})//id form the employee who created the customer
                      ];


//Apply sort
myCustomerQuery.sort = [
    myCustomerQuery.createSort({
        column: myCustomerQuery.columns[0], //
        nullsLast: true
    }),
    myCustomerQuery.createSort({
        column: myCustomerQuery.columns[5],
          nullsLast: true
    })
];



var myPagedResults = myCustomerQuery.runPaged({
    pageSize: 1000
})

var results=[];
myPagedResults.iterator().each(function(resultPage) {
  results =  results.concat(resultPage.value.data.results)
    return false;
})


var x = runtime.getCurrentScript().getRemainingUsage();


log.debug("usage",x);



var renderer = render.create()
var s ="<tr><td>Customer Id</td><td>Department</td><td>salesrep</td><td>Trandate</td><td>Tran Id</td><td>Created by</td><td>CreaterId</td></tr>";

renderer.templateContent="<html><head></head><body><table border='1'>"+s+"<#list results.results as line><tr><#list line.values as singleLine><td>${singleLine}</td></#list></tr></#list></table></body></html>";


//var results = myCustomerQuery.run().results

  renderer.addCustomDataSource({
                  format: render.DataSource.OBJECT,
                  alias: "results",
                  data: {results:results}
                  })


    context.response.write(renderer.renderAsString());
     //context.response.write(JSON.stringify(results));

  }
  catch(err)
  {
    context.response.write(JSON.stringify(err));
  }

  }

 



        return{
         onRequest:onRequest
        }


});
HELP REFERENCE : section_1510275060.html
Leave a comment Cancel reply
