I am new to Netsuite,
I am fetching the records through the Query,but the query limit is 5000 so i am adding the pagination for query records,
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope Public
*/
var
log,
query,
serverWidget;
define(['N/log', 'N/record', 'N/query', 'N/ui/serverWidget', 'N/format', 'N/currentRecord', 'N/file', 'N/encode', 'N/url', 'N/http'], main);
function main(logModule, recordModule, queryModule, serverWidgetModule, formatModule, currentRecordModule, file, encode, url, http) {
log = logModule;
record = recordModule;
query = queryModule;
serverWidget = serverWidgetModule;
format = formatModule;
currentRecord = currentRecordModule;
function onRequest(context) {
// Create a form.
var form = serverWidget.createForm({
title: 'Custom Unrealized Exchange Rate Gains and Losses - Bill, Bill Payment & Invoice',
hideNavBar: false
});
// Add the filter fields.
var queryField = form.addField({
id: 'custpage_field_query',
type: serverWidget.FieldType.LONGTEXT,
label: 'Query'
});
var periodFromField = form.addField({
id: 'custpage_field_periodfrom',
type: serverWidget.FieldType.SELECT,
label: 'From',
source: 'accountingperiod'
});
var periodToField = form.addField({
id: 'custpage_field_periodto',
type: serverWidget.FieldType.SELECT,
label: 'To',
source: "accountingperiod"
});
var subsidiaryField = form.addField({
id: 'custpage_field_subsidiary',
type: serverWidget.FieldType.SELECT,
label: 'Subsidiary'
//source: "subsidiary"
});
var currencyField = form.addField({
id: 'custpage_field_currency',
type: serverWidget.FieldType.TEXT,
label: 'Base Currency'
});
var rangeField = form.addField({
id: 'custpage_field_range',
type: serverWidget.FieldType.SELECT,
label: 'Range'
});
var rangeHiddenField = form.addField({
id: 'custpage_field_range_text',
type: serverWidget.FieldType.LONGTEXT,
label: 'Range Text'
});
rangeField.addSelectOption({value : 0,text : ''});rangeField.addSelectOption({value : 1,text : '1500'});rangeField.addSelectOption({value : 2,text : '3000'});rangeField.addSelectOption({value : 3,text : '5000'});
// Add a submit button and export button
form.addSubmitButton({
label: 'Refresh'
});
form.addButton({
id: 'custpage_nextpage_btn',
label: 'NextPage'
});
var subsidiaryqueryData = "select s.id,s.name,s.parent,s.currency from subsidiary s";
// Run the query.
var subsidiaryqueryResults = query.runSuiteQL({
query: subsidiaryqueryData,
});
var subsidiaryrecords = subsidiaryqueryResults.asMappedResults();
//subsidiaryrecords.push({"name":"Veeva Systems Inc. (Consolidated)","id":"(1,2,5,6,8,58,59,30,31,32,33,34,35,36,37,39,42,52,9,54,55,56,19,21,12,13,15,16,10,11,66,65,68,70)", "currency": "6","parent": "6"});
//
// subsidiaryrecords.push({"name":"Veeva Systems Inc. (Consolidated)", "id":"1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,19,21,30,31,32,33,34,35,36,37,39,41,42,43,47,48,49,52,54,55,56,57,58,59,63,64,65,66,68,69,70", "currency": "6","parent": "6"});
log.debug({title:'subsidiaryData',details:subsidiaryrecords});
var fillSubsidiary=subsidiaryrecords;
if(fillSubsidiary.length != 0) {
for(var i in fillSubsidiary) {
subsidiaryField.addSelectOption({
value : fillSubsidiary[i].id,
text : fillSubsidiary[i].name
});
}
}
// Make the field required.
periodFromField.isMandatory = true;
periodToField.isMandatory = true;
subsidiaryField.isMandatory = true;
queryField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
currencyField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
rangeHiddenField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
form.clientScriptModulePath = '../SuiteScripts/excel_export_clientscript.js';
periodFromField.defaultValue = context.request.parameters.custpage_field_periodfrom;
periodToField.defaultValue = context.request.parameters.custpage_field_periodto;
subsidiaryField.defaultValue = context.request.parameters.custpage_field_subsidiary;
currencyField.defaultValue = context.request.parameters.custpage_field_currency;
queryField.defaultValue = context.request.parameters.custpage_field_query;
rangeField.defaultValue = context.request.parameters.custpage_field_range;
rangeHiddenField.defaultValue = context.request.parameters.custpage_field_range_text;
// If the form has been submitted...
if (context.request.method == 'POST') {
try {
var periodFromId = context.request.parameters.custpage_field_periodfrom;
var periodToId = context.request.parameters.custpage_field_periodto;
var subsidiaryId = context.request.parameters.custpage_field_subsidiary;
var basecurrency = context.request.parameters.custpage_field_currency;
var subsidaryName = context.request.parameters.custpage_field_query;
var rangeValue = context.request.parameters.custpage_field_range_text;
// var currentRecObj = context.currentRecord;
// var subsidiaryfieldName = currentRecObj.getText('custpage_field_subsidiary');
log.debug({title:'subsidiaryfieldNameSL',details:subsidaryName})
log.debug({title:'rangeValueText',details:rangeValue})
if (periodFromId) {
var fromaccperiodObj = record.load({
type: 'accountingperiod',
id: periodFromId
});
if (fromaccperiodObj) {
var periodStartDate = fromaccperiodObj.getValue({
fieldId: 'startdate'
});
}
var periodFromDate = format.format({
value: periodStartDate,
type: format.Type.DATE
});
var periodFromFormat = new Date(periodFromDate).toISOString().slice(0, 10);
}
if (periodToId) {
var toaccperiodObj = record.load({
type: 'accountingperiod',
id: periodToId
});
if (toaccperiodObj) {
var periodEndDate = toaccperiodObj.getValue({
fieldId: 'enddate'
});
}
var periodToDate = format.format({
value: periodEndDate,
type: format.Type.DATE
});
var periodToFormat = new Date(periodToDate).toISOString().slice(0, 10);
}
//,CASE WHEN (t.exchangerate !=cr1.Exchangerate) THEN 'YES' ELSE 'NO' END as " + UICurrencyChanges + ",t.exchangerate as TransExRate,cr1.Exchangerate as " + sourceexchangeratealias + ",
/*** Assign Column Name for Report in the variable and assign the variable to the Query. Reason - Onside the Query Space is not taken" ***/
var trannoalias = '"Source Transaction Number"';
var trantypealias = '"Source Transaction Type"';
var periodalias = '"Unrealized Gain / Loss Posting Period"';
var datealias = '"Source Transaction Date"';
var sourceexchangeratealias = '"Beginning Fx Rate"';
var endingexchangeratealias = '"End FX Rate"';
var currencyalias = '"Foreign Currency"';
var currencybalancealias = '"Foreign Currency Balance"';
var netgainalias = '"Net Gain / Loss"';
var consolidatedexchangerate='"Consolidated Exchange Rate"';
var consolidatedFxRate='"Consolidated Foreign Currency"';
var consolidatedgain='"Consolidated Gain / Loss"';
//CASE WHEN (TO_CHAR ( t.TranDate, 'DD' )) >= 1 THEN t.exchangerate ELSE cr1.Exchangerate END as " + sourceexchangeratealias + ",
var flDate = new Date(periodEndDate).toISOString().slice(0, 10);
var n = flDate.toString();
flDate = "'"+n+"'";
var queryData = "SELECT * FROM ( SELECT ROWNUM AS RN, * FROM ( select t.closedate, CASE when t.AbbrevType = 'INV' THEN 'Accounts Receivable' ELSE 'Accounts Payable' End as Account, BUILTIN.DF(t.Entity) as Name, BUILTIN.DF(tl.subsidiary) as Subsidiary, t.Tranid AS SourceTransactionNumber, t.TranDate as SourceTransactionDate, CASE when t.AbbrevType = 'BILL' THEN 'BILL' WHEN t.AbbrevType = 'INV' THEN 'INVOICE' ELSE 'BILL PAYMENT' End as SourceTransactionType, ap.periodname AS UnrealizedGainPostingPeriod, CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END as BeginningFxRate, cr2.Exchangerate as EndFXRate, BUILTIN.DF(t.currency) as ForeignCurrency, Round(ForeignTotal, 2) as ForeignCurrencyBalance, Round( ( ( cr2.Exchangerate - CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END ) * ForeignTotal ), 2 ) AS NetGainLoss, cexr.averagerate as ConsolidatedExchangeRate, Round( ( ( cr2.Exchangerate - CASE WHEN (TO_CHAR (t.TranDate, 'YYYY')) >= (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) >= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate WHEN (TO_CHAR (t.TranDate, 'YYYY')) > (TO_CHAR (ap.startdate, 'YYYY')) AND (TO_CHAR (t.TranDate, 'MM')) <= (TO_CHAR (ap.startdate, 'MM')) THEN t.exchangerate ELSE cr1.Exchangerate END ) * (ForeignTotal * cexr.averagerate) ), 2 ) AS ConsolidatedGainLoss, from Transaction t INNER JOIN TransactionLine tl ON (tl.Transaction = t.ID) INNER JOIN Subsidiary sb ON sb.id = tl.subsidiary INNER JOIN AccountingPeriod ap ON ( ( ap.id BETWEEN (320) AND (320) ) AND ap.isposting = 'T' ) INNER JOIN CurrencyRate cr1 ON ( cr1.BaseCurrency = sb.currency AND cr1.TransactionCurrency = t.Currency AND ( cr1.EffectiveDate = TO_DATE(ap.startdate, 'MM/DD/YYYY') ) ) INNER JOIN CurrencyRate cr2 ON ( cr2.BaseCurrency = sb.currency AND cr2.TransactionCurrency = t.Currency AND ( cr2.EffectiveDate = TO_DATE(ap.enddate, 'MM/DD/YYYY') ) ) LEFT JOIN CurrencyRate cr3 ON ( cr3.BaseCurrency = 1 AND cr3.TransactionCurrency = sb.currency AND ( cr3.EffectiveDate = TO_DATE(t.TranDate, 'MM/DD/YYYY') ) ) INNER JOIN consolidatedexchangerate cexr ON ( cexr.postingperiod = t.PostingPeriod AND cexr.fromsubsidiary = tl.subsidiary AND cexr.tosubsidiary = 1 ) Where ( ( (t.AbbrevType = ('INV')) OR (t.AbbrevType = ('BILL')) ) ) AND (tl.MainLine = 'T') AND (tl.subsidiary IN (1)) AND ( (t.CloseDate IS NULL) OR (t.CloseDate >= TO_DATE("+flDate+", 'YYYY-MM-DD')) )) ) WHERE ( RN BETWEEN 5001 AND 10000 )";
log.debug({
title: 'queryData',
details: queryData
});
// Run the query.
var queryResults = query.runSuiteQL({
query: queryData,
});
// Get the mapped results.
var beginTime = new Date().getTime();
var records = queryResults.asMappedResults();
var endTime = new Date().getTime();
var elapsedTime = endTime - beginTime;
// If records were returned...
if (records.length > 0) {
// Create a sublist for the results.
var resultsSublist = form.addSublist({
id: 'results_sublist',
label: 'Results (' + records.length + ' records retrieved in ' + elapsedTime + 'ms)',
type: serverWidget.SublistType.LIST
});
// Get the column names.
var columnNames = Object.keys(records[0]);
// Loop over the column names...
for (i = 0; i < columnNames.length; i++) {
// Add the column to the sublist as a field.
resultsSublist.addField({
id: 'custpage_results_sublist_col_' + i,
type: serverWidget.FieldType.TEXT,
label: columnNames[i]
});
}
// Add the records to the sublist...
for (r = 0; r < records.length; r++) {
// Get the record.
var recordvalue = records[r];
// Loop over the columns...
for (c = 0; c < columnNames.length; c++) {
// Get the column name.
var column = columnNames[c];
// Get the column value.
var value = recordvalue[column];
if (value != null) {
value = value.toString();
}
// Add the column value.
resultsSublist.setSublistValue({
id: 'custpage_results_sublist_col_' + c,
line: r,
value: value
});
}
}
} else {
// Create a sublist for the results.
var noresultsSublist = form.addSublist({
id: 'noresults_sublist',
label: 'No data available.',
type: serverWidget.SublistType.LIST
});
}
} catch (e) {}
}
if (records != '') {
log.debug({title:'subsidiaryIdexp',details:subsidaryName})
var subsidiaryIdData = "'"+subsidiaryId+"'";
var subsidiaryNameData = "'"+subsidaryName+"'";
form.clientScriptModulePath = '../SuiteScripts/excel_export_clientscript.js';
var exportBtn = form.addButton({
id: 'custpage_export_btn',
label: 'Export',
functionName: "excelDownload(" + periodFromId + "," + periodToId + "," + subsidiaryIdData + ","+basecurrency+","+subsidiaryNameData+");"
})
}
// Display the form.
context.response.writePage(form);
}
return {
onRequest: onRequest
};
}
The above code work perfectly, but the question is is it possible to add dynamic pagination in suitelet.
is there any sample for pagination?
I am trying to add nextpage button and when click on that button the next page should popup but it failed and the page is refreshing entirely.
Thanks in advance