https://netsuiteprofessionals.com logo
Join Slack
Powered by
I am trying to create a journal from the search re...
# suitescript
v
I am trying to create a journal from the search results of a map reduce script The get input stage is returning a list of transactions and their line ids; document numbers; document types, account code and amount. All of this is working fine I want to create a journal from this to recode every result line into a specific code (a fixed asset code) Where I am getting stuck is the journal creation part: 
function createJournalEntry(account, debit, credit, memo) {
try {
// Create a journal entry
var journal = record.create({
type: record.Type.JOURNAL_ENTRY,
isDynamic: true
});
// Set the subsidiary on the journal entry
journal.setValue({
fieldId: 'subsidiary',
value: 12 // Assuming subsidiary ID
});
// Add a line to the journal entry
journal.selectNewLine({
sublistId: 'line'
});
// Set the account, debit, credit, and memo for the line
journal.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: account
});
journal.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'debit',
value: debit
});
journal.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'credit',
value: credit
});
journal.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'memo',
value: memo
});
// Commit the line
journal.commitLine();
// Save the journal entry
var journalId = journal.save();
log.debug('Journal entry created with ID:', journalId);
} catch (error) {
log.error('Error creating journal entry:', error);
}
}
What I can't work out is: how do I add an additional line, after all of the other lines from the search results, that would be the line to recode to the fixed asset account? The value will change depending on the results returned from the get input stage it should be the sum of all debits minus credits I can't work out how to use the 'final value' of the Jnltotal variable to set the journal balance line. This variable is calculated by iterating through all of the results in the reduce function. 
// Initialize variables to store the maximum line ID and corresponding amount. These variables are outside of any function
var maxLineId = 0;
var maxAmount = 0;
var balanceLine=0
var jnlTotal=0
function reduce(context) {
log.debug('context.values-reduce',context)
context.values.forEach(function(value) {
// Parse the value JSON string
var parsedValue = JSON.parse(value);
var lineId = parsedValue.lineid;
log.debug("lineid-reduce", lineId);
// Check if the current line ID is greater than the maximum line ID
if (lineId > maxLineId) {
// Update the maximum line ID and corresponding amount
maxLineId = lineId;
maxAmount = parsedValue.amount;
}
// Calculate balanceLine after iterating through all values
balanceLine = parseInt(maxLineId) + 1;
log.debug('balanceLine', balanceLine)
var amount = parseFloat(parsedValue.amount);
log.debug("amount",amount)
jnlTotal+=amount
log.debug('jnlTotal'+'lineid',"journaltotal:"+jnlTotal+''+"lineId "+lineId)
});
Hope this question makes sense.
I have found a way to get the total amount for the journal now the issue I have is the 'journal is not balanced' error when I hard code the values for the total debits, it works fine however, there is something wrong in the journal creation lines setting the debit and credit values // 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 }); /* 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) if (parseFloat(debit) > 0) { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "debit", value: parseFloat(debit), }); log.debug("debit-journal",debit) } else { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "credit", value: parseFloat(credit), }); log.debug("credit-journal",credit) } journalEntry.commitLine({ sublistId: "line", }); } 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", }); } else { journalEntry.setCurrentSublistValue({ sublistId: "line", fieldId: "credit", value: balanceAmount, }); log.debug("balance-credit",balanceAmount) } journalEntry.commitLine({ sublistId: "line", }); } var id = journalEntry.save(); log.debug("id", id); } catch (error) { log.error("Error creating journal entry", error); } } catch (error) { log.error("Error in map", error); } }
Revised version of the script: If I hard code the debit value of the journal, it works correctly The debug logs are returning values for all of the lines The totaldebits debug log returns 156,000 and the totalcredits debug log also returns 156000 The journal should be balanced I cannot for the life of me work out where this is going wrong and why I am getting a "journal entries must balance" if I am using variables (though it works correctly if I hard code the journal debit value) 
/**
* @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+"_"+type
position++
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=0
var 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)
}
}
} catch (error) {
log.error("Error creating journal entry", error);
}
try {
var id = journalEntry.save();
log.debug("id", id);
} catch (error) {
log.error("Error saving 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());
}
return {
getInputData: getInputData,
map: map,
reduce: reduce,
summarize: summarize,
};
});
c
You need to log out the values (and their types) that you are trying to set on the journal lines to make sure they are what you expect. Reduce your logic so it only sets one credit and one debit at first, if that balances, then allow it to set all the debits/credits. I bet you're passing in a value that is not correct for debit/credit and maybe that's because the code is so messy, you can't easily trace what's happening. I would put a focus on cleaner code that's easier to maintain and it will make finding these sorts of issues much easier. Feel free to point to exactly where you think the issue is and I can take a deeper look. It's hard without being able to test the actual data being passed from your input function though.
c
@Vernita To save our scrolling muscles, please use Slack's snippets feature to share code and similar multi-line text messages.
👍 1
v
Thanks @Clay Roper I don't see an option for snippets in the web browser version. Do I need the desktop version of Slack for this?
Thanks @Craig. I will try to change the search results so it only returns one line and then try to set the value I have only started to learn how to write Suitescript so appreciate the feedback
c
@Vernita This screenshot is from the web browser version (Chrome / Windows fwiw). Click the + icon at the left of the icon row below the text input, then select "Create a text snippet". Also note the keyboard shortcut (will differ on mac)
👍 1
v
Thanks @Clay Roper. I will use that from now on
b
your code is too long and annoying for a close look
but my guess is that
Copy code
parseFloat('156,000')
is causing issues for you
the value of that statement is not 156000
lesser issues that may affect you later on are that you declare 
totalDebits
totalCredits
outside the scope of your map entrypoint, which means that their value actually depends on previous iterations and resets every time the script yields
8 Views
Open in Slack
PreviousNext
Powered by