Exporting journal entry item line details as CSV files
UserEvent Script
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/*******************************************************************************
* Fennetti
*
******************************************************************************
* Date: 7 February 2022
* Author: Jobin & Jismi IT Services LLP
* Date created : 7 February 2022
* v1.0 Sreevidya : Created
******************************************************************************/
define([],
function () {
function beforeLoad(context) {
try {
log.debug('context.type',context.type)
if (context.type == 'view'){
var objRec = context.newRecord;
//add button on Invoice record in view mode
context.form.addButton({
id: "custpage_csv_button",
label: "Download CSV",
functionName: "csvJournal"
});
//trigger client script on button click
context.form.clientScriptFileId = 9125;
}
}
catch (e) {
log.debug('catchError', e)
}
}
return {
beforeLoad: beforeLoad
};
});Client Script
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* Fenetti
*
******************************************************************************
* Author: Jobin & Jismi IT Services LLP
* Version History
* v1.0 Sreevidya : Created
* Date created : 7 February 2022
******************************************************************************/
define(['N/url','N/currentRecord'],
function(url,currentRecord) {
/**
* Function to be executed after page is initialized.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
*
* @since 2015.2
*/
function pageInit(scriptContext) {
console.log("scriptContext",scriptContext.mode)
}
function csvJournal(){
try{
var internalIds = currentRecord.get().id;
console.log("internalIds ",internalIds);
//Invoke suitelet page up on button click
var currenturl = url.resolveScript({
scriptId: "customscript_jj_sl_csvjournal",
deploymentId: "customdeploy_jj_sl_csvjournal",
returnExternalUrl: false
})
//open suitelet page in new window
window.open(currenturl + "&internalIds=" + internalIds)
}catch (e) {
console.log("error@csv Journal",e)
}
}
return {
pageInit: pageInit,
csvJournal: csvJournal
};
});Suitelet
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/*******************************************************************************
* Fennetti
*
******************************************************************************
* Date: 7 February 2022
* Author: Jobin & Jismi IT Services LLP
* Date created : 7 February 2022
* v1.0 Sreevidya : Created
******************************************************************************/
var CONTEXT;
define(['N/search', 'N/render', 'N/log', 'N/file'],
(search, render, log, file) => {
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
const onRequest = (scriptContext) => {
try {
var recIds = scriptContext.request.parameters.internalIds
// log.debug('record id', recIds);
var results = searchDetailsPrint(recIds);
// log.debug('results', results);
var columns = results.columns;
// Creating arrays that will populate results
var content = new Array();
var cells = new Array();
var headers = new Array();
var temp = new Array();
var x = 0;
headers[0] = "Amount in EUR";
headers[1] = "Debit/Credit";
headers[2] = "Account No.";
headers[3] = "Contra Account No.";
headers[4] = "VAT-Key";
headers[5] = "Date";
headers[6] = "Invoice No.";
headers[7] = "Description";
headers[8] = "EU-Country";
headers[9] = "EU-VAT tax rate";
content[x] = headers;
x = 1;
results.run().each(function (result) {
content[x] = "";
//looping through each columns
for (var y = 0; y < columns.length; y++) {
var searchResult = result.getValue(
result.columns[y]
);
temp[y] = searchResult.replace(',', '');
log.debug("searchResult",searchResult);
}
// var filtered = temp.filter(function (el) {
// return el != '';
// });
content[x] += temp;
log.debug("temp", temp);
log.debug("content[x]", content[x]);
x++;
return true;
});
//Creating a string variable that will be used as CSV Content
var contents = '';
for (var z = 0; z < content.length; z++) {
contents += content[z].toString() + '\n';
}
log.debug("contents",contents);
//Creating a CSv file
var fileObj = file.create({
name: 'searchresults.CSV',
fileType: file.Type.CSV,
contents: contents
});
scriptContext.response.writeFile(fileObj, true);
}
catch (e) {
log.debug("error@onRequest", e)
}
}
function searchDetailsPrint(recIds) {
try{
log.debug('inside search function', true);
log.debug('record id', recIds);
var journalentrySearchObj = search.create({
type: "journalentry",
filters:
[
["type","anyof","Journal"],
"AND",
["multisubsidiary","is","F"],
"AND",
["advintercompany","is","F"],
"AND",
["internalid","anyof",recIds],
"AND",
["taxline","is","F"]
],
columns:
[
search.createColumn({name: "grossamount", label: "Amount (Gross)"}),
search.createColumn({
name: "formulatext",
formula: "CASE WHEN {creditamount} IS NOT NULL THEN 'H' ELSE 'S' END",
label: "Debit/Credit"
}),
// search.createColumn({
// name: "number",
// join: "account",
// label: "Number"
// }),
search.createColumn({
name: "formulatext",
formula: "CASE WHEN {creditamount} IS NOT NULL THEN {account.number} ELSE NULL END",
label: "Account No"
}),
search.createColumn({
name: "formulatext",
formula: "CASE WHEN {debitamount} IS NOT NULL THEN {account.number} ELSE NULL END",
label: "Contra"
}),
search.createColumn({name: "taxcode", label: "Tax Item"}),
search.createColumn({name: "trandate", label: "Date"}),
search.createColumn({name: "invoicenum", label: "Invoice Number"}),
search.createColumn({name: "memo", label: "Memo"}),
search.createColumn({
name: "country",
join: "subsidiary",
label: "Country"
}),
search.createColumn({
name: "rate",
join: "taxItem",
label: "Rate"
})
]
});
var searchResultCount = journalentrySearchObj.runPaged().count;
log.debug("journalentrySearchObj result count",searchResultCount);
journalentrySearchObj.run();
// .each(function(result){
// // .run().each has a limit of 4,000 results
// return true;
// });
return journalentrySearchObj;
}
catch (e) {
log.debug("error@search function", e);
}
}
return {onRequest}
});