Hi There
I have a map reduce script which is continuously returning a 'journal not balanced' error. can you see where the error is in my code? The debug logs, in particular the 'totaldebits' and 'totalcredits' are returning the correct values
/** * @NApiVersion 2.x * @NScriptType MapReduceScript */
define(["N/search", "N/record", "N/runtime","N/format"], function ( search, record, runtime,format) { function getInputData() { var transactionSearchObj = search.create({ type: "transaction", settings: [{ name: "consolidationtype", value: "ACCTTYPE" }], filters: [ ["voided", "is", "F"], "AND", ["mainline", "is", "F"], "AND", [ ["projecttask", "noneof", "@NONE@"], "OR", ["custcol_project_task_je", "noneof", "@NONE@"], ], "AND", ["type", "anyof", "VendBill", "VendCred", "ExpRept"], "AND", ["custcol_already_capitalised", "is", "F"], "AND", ["job.entityid", "is", "84 Elm Street"], ], columns: [ search.createColumn({ name: "tranid", summary: "GROUP", label: "Document Number", }), search.createColumn({ name: "amount", summary: "SUM", label: "Amount", }), search.createColumn({ name: "account", summary: "GROUP", label: "Account", }), search.createColumn({ name: "type", summary: "GROUP", label: "Type", }), search.createColumn({ name: "line", summary: "GROUP", label: "Line ID", }), ], });
var totalAmount = 0; var totalLines=0 var totalDebit=0 var totalCredit=0
var searchResult = transactionSearchObj.run().getRange({ start: 0, end: 1000 });
searchResult.forEach(function (searchResult) { var amount = searchResult.getValue(searchResult.columns[1]);
totalAmount += parseFloat(amount); log.debug("totalAmount-Get Input loop", totalAmount); var count=1 totalLines+=count log.debug("totalLines-Get Input loop", totalLines);
if(parseFloat(amount)>0){ var parsedAmount=parseFloat(amount) totalDebit +=parsedAmount log.debug("totalDebit-getInput loop",totalDebit) }else{ var parsedAmount=parseFloat(amount)*-1 totalCredit+=parsedAmount} log.debug("totalCredit-getInput loop",totalCredit) }); log.debug("totalAmount-Get Input Outside of Loop", totalAmount); var totalLines=totalLines+1 log.debug("totalLines-Get Input Outside of Loop", totalLines); log.debug("totalDebit-Get Input Outside of Loop", totalDebit); log.debug("TotalCrebit-Get Input Outside of Loop", totalCredit);
/* searchResult.forEach(function (searchResult) { var transId= searchResult.getValue(searchResult.columns[0]) var lineId=searchResult.getValue( searchResult.columns[4]) var transRef=transId+"_"+lineId log.debug('transRef-getInput',transRef) })
*/
var noofLines = transactionSearchObj.runPaged().count; var inputData = []; var position=0
searchResult.forEach(function(result) {var transId=result.getValue({name:'tranid', summary:"GROUP"})var lineId=result.getValue({name:'line', summary:"GROUP"})var type=result.getValue({name:"type",summary:"GROUP"})var transRef=transId+"_"+lineId+"_"+typeposition++var amount=result.getValue({name:"amount", summary:"SUM"})
if (amount>0) { var debit=amount var credit=0 } else { var debit=0 var credit=amount*-1}
inputData.push({ key: transRef, value: { amount: result.getValue({ name: "amount", summary: "SUM" }), account: result.getValue({ name: "account", summary: "GROUP" }), type: result.getValue({ name: "type", summary: "GROUP" }), lineId: result.getValue({ name: "line", summary: "GROUP" }), transactionId:transId, debit:debit, credit:credit, position:position } }); })
inputData.push({ key:'totalAmount', value:{ amount:totalAmount, account:379, position:totalLines, type:"balancing", totalCredit:totalCredit, totalDebit:totalDebit } }) return inputData }var totalDebits=0var totalCredits=0 function map(context) { try { // Processing each search result in the map function var searchResult = JSON.parse(context.value); log.debug("searchResult-map", searchResult); // Accessing the necessary data from the search result /* var transactionId = searchResult.id; */ //can be used for line id? var transactionId = searchResult.value["transactionId"] log.debug('transactionId-map', transactionId) var lineId = searchResult.value["lineId"] log.debug('lineId-map', lineId) var type = searchResult.value["type"] log.debug('type-map', type) var account = searchResult.value["account"] log.debug("account=map",account)
var lineRef = searchResult.value["type"] log.debug("lineRef-map", lineRef);
var linePosition=searchResult.value["position"] log.debug("line position in Map",linePosition)
var debit=parseFloat(searchResult.value["debit"]) var credit=parseFloat(searchResult.value["credit"])
log.debug("debit-map",debit) log.debug("credit-map",credit)
if (searchResult.key=="totalAmount") { var totalAmount=searchResult.value["amount"] var linePositionBal=searchResult.value["position"]var totalDebit=searchResult.value["totalDebit"]var totalCredit=searchResult.value["totalCredit"]var accountBalance=searchResult.value["account"]
log.debug("totalAmount-Map",totalAmount) log.debug("linePositionBal",linePositionBal) log.debug("totalDebit-Map",totalDebit) log.debug("totalCredit-Map",totalCredit)
if (totalDebit>totalCredit) { var balanceAmount=totalDebit-totalCredit var balanceSide="credit" } else { var balanceAmount=totalCredit-totalDebit var balanceSide="debit" } }
log.debug("balanceAmount "+balanceSide,balanceAmount)
try { var currentDate=new Date() log.debug("currentDate",currentDate) var currentDateTimezone= format.format({ value: currentDate, type: format.Type.DATETIME, timezone: format.Timezone.AUSTRALIA_SYDNEY }); log.debug("currentDateTimeZone",currentDateTimezone+" "+typeof(currentDateTimezone)) var parsedDate=format.parse({ value:currentDateTimezone, type: format.Type.DATE }) log.debug("parsedDate",parsedDate)} catch (error) { error.message } // Create a journal entry and recode lines to the WIP account
try { var journalEntry = record.create({ type: record.Type.JOURNAL_ENTRY, isDynamic: true, });
// Set subsidiary and transaction date journalEntry.setValue({ fieldId: "subsidiary", value: "12", // Replace '12' with the correct subsidiary ID }); journalEntry.setValue({ fieldId: "trandate", // value: new Date(2024, 5, 12), // Set the transaction date to today value:parsedDate });
//TODO: Check the total amount variable. do all the debits add up to this?/* journalEntry.selectNewLine({ sublistId: "line", }); journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "account", value: 424, }); journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "debit", value: totalAmount, }); journalEntry.commitLine({ sublistId: "line", }); */ log.debug("linePosition1",linePosition) if (linePosition !=linePositionBal) { journalEntry.selectNewLine({ sublistId: "line", }); journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "account", value: account, }); log.debug("linePosition2",linePosition) log.debug("account-journal",account)
if (parseFloat(debit) > 0) { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "debit", value: parseFloat(debit), });
journalEntry.commitLine({ sublistId: "line", }); log.debug("debit-journal",debit) totalDebits+=parseFloat(debit) log.debug("total Debits",totalDebits)
} else { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "credit", value: parseFloat(credit), }); journalEntry.commitLine({ sublistId: "line", });
log.debug("credit-journal",credit) totalCredits+=parseFloat(credit) log.debug("total credits",totalCredits) }
} if(linePosition==linePositionBal) { journalEntry.selectNewLine({ sublistId: "line", }); journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "account", value: accountBalance, });
if (balanceSide == "debit") { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "debit", value: balanceAmount, }); log.debug("balance-Debit",balanceAmount) journalEntry.commitLine({ sublistId: "line", }); totalDebits+=balanceAmount log.debug("total Debits with balance",totalDebits) } else { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "credit", value: balanceAmount, }); journalEntry.commitLine({ sublistId: "line", });
log.debug("balance-credit",balanceAmount) totalCredits+=balanceAmount log.debug("total credits with balance",totalCredits) try { var id = journalEntry.save(); log.debug("id", id); } catch (error) { log.error("Error saving journal entry", error); }
} }
} catch (error) { log.error("Error creating journal entry", error); }
} catch (error) { log.error("Error in map", error); } }
function reduce(context) { }
function summarize(summary) { // This function is called after all map/reduce tasks are completed log.debug("Script completed", summary.toString()); }
// Exposing functions return { getInputData: getInputData, map: map, reduce: reduce, summarize: summarize, };});