Larry, the Senior Developer at Asoville, has been tasked with what seems like a straightforward question: Is there a way to change the memos on Amortization Journal Entry (AJE) lines from the default “Amortization Source” and “Amortization Destination”? Perhaps, you are interested in the answer too. Let’s learn from Larry!
TL;DR
Short answer: It is possible via scripting to replace the default amortization journal entry line memos with something more useful, e.g. information from the source transaction. This article explains the pattern and provides a deployment-ready code sample.
The solution described in this article works if you use a custom JE approval workflow. If you use the native JE approval option or no JE approvals at all, refer to the companion article here for an adapted solution.
Problem
NetSuite’s Amortization feature [I]NetSuite (March 4, 2011). Amortization Feature Overview. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/7864. [Accessed on: July 30, 2021] “enables you to record the general ledger impact of item purchases and expense charges across multiple future periods”. System-generated Amortization Journal Entries (AJEs) are used to post the amortized expenses to the general ledger.
One major annoyance of AJEs is that the line memos do not reflect the memos from the source transaction but simply state “Amortization Source” or “Amortization Destination”. This makes analysis of AJEs cumbersome as the user has to drill down to the amortization schedule to get to the source transaction (e.g. vendor bill, vendor credit, or journal entry) and then find the corresponding source line.
As far as I know, NetSuite does not offer any option to automatically grab the memo from AJE source transactions. As such, we need to take matters into our own hands.
The request from Larry’s finance team was to show the transaction number and memo from the source transaction in the corresponding lines of the AJE. Reasonable, right?
Solution
Before presenting the script, it is useful to understand the underlying data model:
A transaction line can be associated with an amortization template. Upon saving the transaction, NetSuite automatically generates an amortization schedule based on that template. Thus, an amortization schedule is associated with exactly one transaction line. The amortization schedule produces amortization journal entries to recognize expenses. A single amortization journal entry typically consolidates multiple amortization schedules. As such, each debit/credit pair of lines in the AJE may be related to a different source transaction!
As illustrated above, there are multiple hops from the AJE to the source transaction. And, as you may know, NetSuite’s search module has a limitation of a single hop/join. As such, it is not easy or efficient to write searches to access the source transaction lines from the AJE, especially given that one AJE typically references multiple schedules and source transactions. To be clear, it is possible to solve this problem using only searches. However, the code will be longer and require much more governance units.
Gladly, NetSuite now features a querying module[II]NetSuite (August 28, 2018). N/query Module. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/77359. [Accessed on: July 30, 2021] that supports multi-level joins! Although this functionality is relatively new, it is very promising and suitable for the current challenge. Refer to this article to learn more about N/query
vs. N/search
.
Using N/query to Address The Multi-Level Join Challenge
The following User Event script solves the problem. A decent degree of proficiency with SuiteScript and SQL is necessary to fully understand this code sample.
Tip: This code is written in SuiteScript 2.1. If you prefer SuiteScript 2.0 for whatever reason, you’ll need to replace the arrow functions and template strings with anonymous functions and concatenated strings, respectively.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
define(['N/record', 'N/query'],
function (record, query) {
function afterSubmit(context) {
// We need to run this in afterSubmit as it appears from tests that the schedulenum field
// was blank/uninitialized in the beforeSubmit hook when the JE is created (i.e. context.type = 'create').
// That could be an issue if JEs are set up to automatically post. As such, we use afterSubmit to avoid any problem.
if (context.type === 'delete') {
return;
}
var rec = context.newRecord;
if (rec.getValue('approvalstatus') === '2' /*= Approved*/) {
log.debug(arguments.callee.name, `Ignoring non-amortization or already approved (i.e. non-editable) JE: ${rec.getValue('tranid')} (ID: ${rec.id})`);
return;
}
// Since we're in the afterSubmit event, we need to load the record in order to persist changes.
rec = record.load({ type: rec.type, id: rec.id });
const LINE_SUBLIST = 'line';
var memo, schedule;
var schedulesByLine = {}
var schedules = [];
// Note: We resort to looping here because the schedulenum field is currently not exposed via SuiteQL
// and the exposed amortizationsched field is null on AJEs.
// Therefore, we cannot directly join all data in one query.
for (var i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
memo = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i });
schedule = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'schedulenum', line: i });
if (schedule && (memo === 'Amortization Source' || memo === 'Amortization Destination')) {
schedulesByLine[i] = schedule;
if (schedules.indexOf(schedule) < 0) {
schedules.push(schedule);
}
}
}
if (schedules.length > 0) {
log.audit(arguments.callee.name, 'Executing query to retrieve source transactions from schedules: ' + schedules);
var queryResults = query.runSuiteQL({
query:
`SELECT
sch.id,
sch.sourcetran
FROM
AmortizationSchedule AS sch
WHERE
sch.id IN (${schedules.join()})`
}).asMappedResults();
// Goal: For each source transaction, retrieve data from the line tagged with
// the specified amortization schedule (1:1 relationship guaranteed by data model).
var whereClause = '';
queryResults.forEach(result => {
if (whereClause) {
whereClause += ' OR ';
}
whereClause += '(line.transaction = ' + result.sourcetran + ' AND line.amortizationsched = ' + result.id + ')';
});
queryResults = query.runSuiteQL({
query:
`SELECT
trx.tranid,
trx.type,
line.memo,
line.amortizationsched,
line.linesequencenumber,
line.item
FROM
TransactionLine AS line
INNER JOIN Transaction AS trx
ON (trx.id = line.transaction)
WHERE ${whereClause}`
}).asMappedResults();
var lineInfoBySchedule = {};
var lineType;
queryResults.forEach(result => {
lineType = (result.type === 'Journal') ? '' : (result.item ? 'Item ' : 'Expense ');
// Adjust as needed to get the desired info.
// This implementation captures the transaction, line and memo.
lineInfoBySchedule[result.amortizationsched] =
`[${result.tranid} @${lineType}Line ${result.linesequencenumber}] ${result.memo}`;
});
// Now we have all the information, we can update the lines of the AJE
for (i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
schedule = schedulesByLine[i];
if (schedule) {
memo = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i });
memo = lineInfoBySchedule[schedule] + ' (' + memo + ')';
rec.setSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i, value: memo });
}
}
rec.save({ ignoreMandatoryFields: true });
} else {
log.debug(arguments.callee.name, 'No schedules found on JE lines');
}
}
return {
afterSubmit: afterSubmit
};
});
The script consists of four main parts:
- Identify matching lines whose memos need to be replaced.
- Retrieve the source transactions from the amortization schedules linked to those lines.
- Get the desired information from the source transactions.
- Update the AJE line memos.
Note: In the ideal world, we should be able to write a single SQL query to cover items (1) – (3) above. However, due to the following limitations at the time of writing, we have to resort to discrete steps.
Data Limitations
Although the field AmortizationSched
is exposed in the SuiteAnalytics API (which N\query
uses), the value is empty on AJEs. As per SuiteAnswers Answer ID 80572, the schedulenum
field can be used to retrieve the amortization schedule from an AJE line. Ironically, this field is not exposed via the SuiteAnalytics API. As such, we have no way to join from the AJE to the amortization schedule to the transaction lines in one query. Thus, we resort to looping to get the schedules and then writing two separate SQL queries to get all the data that we need.
As stated, SuiteAnalytics is relatively new and is based on a different data model than the one that drives saved searches. The fact that the
AmortizationSched
field on AJEs is blank seems to be a glitch that may be fixed in the future. At that point, we can revisit this solution and simplify it.
Wrap Up
NetSuite’s SuiteAnalytics module is a breath of fresh air especially for developers with SQL knowledge. It is definitely worth your while as a NetSuite developer/technical consultant to start paying attention to this module as it opens doors to solve problems that are otherwise impossible or extremely difficult to solve with traditional search-based approaches.
Tim Dietrich has developed an excellent (and free!) SuiteQL Query Tool that allows you to explore SuiteAnalytics, construct and run your queries, etc. I highly recommend Tim’s tool.
Larry used this tool to produce the queries he needed to solve our challenge and only started scripting after he was sure that he could “reach” the data that he needed.
Back at Asoville, Larry and his team are very satisfied with the results. For Larry, this first exposure to SuiteQL and a forced refresher of the SQL he learned back in college, was a pleasant experience. One more tool in his toolbox to tackle upcoming challenges and one more weapon in the ongoing, never-ending debate: “To script or not to script?”. In cases like this where there’s really no non-scripting answer, I guess the more relevant question is: “To SuiteQL or to search?”.
If you’ve found this article useful, let us know! Consider subscribing to our no-nonsense email list to get notified of NetSuite insights as soon as we publish them. You can also become a NetSuite Insights contributor and share your NetSuite gems via this distinguished platform. Keep learning. Keep sharing!
Related Posts
Further Reading
Why would this work sometimes, and throw an error sometimes? I’ve installed the same script seven times into netsuite and it worked the 7th time with no explanation as to why; I ran them all thru compareDiff and they’re completely identical…
Questioning all my life choices at this point…Debug log shows nothing when the errors are thrown.
I suspect the error you’re facing is not due to the script but something in your environment. Remember, this script was provided as a sample to explain the pattern. Each environment is different and you need to take that into account.
I am a BI Developer, so I work with ODBC driver and Netsuite2.com data schema.
I see nulls in the AJE AmortizationSched field when I select from transactionlines, just as you mentioned.
I need to connect the amortization schedules to transactionlines.
In the createdfrom field I see the source transaction id, but there might be several amortization schedules created from the source transaction, so I don’t have per line connection.
I understand that you somehow managed to get the solution. I tried to read your code, but except for the queries I didn’t understand it.
Can I achieve this by SQL queries only?
Thank you
Hi Michael,
I’m not grounded in the ODBC driver but if it uses the SuiteAnalytics API, then you’ll face the same data limitation described in this article: Without access to the amortization schedule on the AJE lines, you’d have a challenge. Perhaps you can write a script to copy the line-level amortization schedule IDs to a custom transaction line field which you can then use in your queries?
Please be sure to share your findings for the benefit of others!
Cheers
I appreciate your useful information.
Now I’ve tried to implement this solution onto my customer, however, it has not worked as of now.
I guess my configuration of “APPLY TO” of the script.
(I tried “Journal Entry”, “Amortization Schedule”, and “Amortization Template, but all didn’t work at all…)
What should I choose as “APPLY TO” of this script to let it work on Create Amortization Journal?
Thanks for sharing this. Did you find the User Event was triggered when the journal was created from the Create Amortization Journal page? Or do you need to edit and save the journal to trigger the script?
You’re welcome. The script will trigger upon JE creation provided you have the right contexts set. Cheers.
As someone who looks at the NetSuite GL for analysis, this solution will make my, and my team’s life so much easier!
Hi Sandra, glad to hear that this solution will help your team! Pretty sure you’re not alone. 😉