Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines

5 min read

Having recently solved a similar challenge for amortization journal entries, Larry, the Senior Developer at Asoville, figured that it should be possible to also change the memos on Revenue Recognition (Rev. Rec.) journal entry lines from the default “Rev Rec Source” and “Rev Rec Destination”? Perhaps, you already guessed the answer. Let’s learn from Larry!


TL;DR

Short answer: It is possible via scripting to replace the default rev. rec. journal entry line memos with something more useful, e.g. information from the source transaction. While the job can get done using only N/search, we sprinkle some SuiteQL into the solution as it produces more compact and arguably easier to understand code. This article explains the pattern and provides relevant code snippets.

Rev. Rec. JE before and after
The memo of rev. rec. JE lines can be enriched with information from the source transactions

Problem

With NetSuite’s Advanced Revenue Management (ARM) feature [I]NetSuite (February 22, 2016). Advanced Revenue Management. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/49167. [Accessed on: October 28, 2021] “you can defer revenue for recognition across future periods according to the rules you configure”. System-generated Revenue Recognition (Rev. Rec.) journal entries are used to recognize deferred revenue.

One major annoyance of rev. rec. JEs is that the line memos do not reflect the memos from the source transaction but simply state “Rev Rec Source” or “Rev Rec Destination”. This makes analysis cumbersome as the user has to drill down into the somewhat complicated data model to get to the source transaction (e.g. sales order, invoice, 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 rev. rec. source transactions. As such, we need to take matters into our own hands.

Solution

Before presenting the script, it is useful to understand the underlying data model. Be aware that the following is simply a view of the data model based on my current understanding. There might be more links than are captured.

Perhaps, the most important takeaway is that the model is pretty complex with several records and hops between the source transaction and the rev. rec. JE. Please refer to Answer ID 49167 for more details.

NetSuite Advanced Revenue Management Data Model
ARM Data Model

NetSuite’s N/query 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] addresses the multi-hop/join challenge. However, the underlying SuiteAnalytics data model is not quite as mature as the tested and tried N/search data model meaning that there is sometimes missing information in N/query. In this particular case, although there is a 1:1 relationship between a revenue element and the corresponding line on the source transaction, the desired {source} and {sourceid} fields on the revenue element record are neither exposed via N/search or N/query at the time of writing. Thus, we cannot write a single query or saved search to get the desired outcome. Instead, we’re forced to break the work up into parts as explained next.

Note that while fields {source} and {sourceid} are accessible via record.load(), that is simply not a viable path as rev. rec. JEs can contain up to 1000 lines which means that we could run out of governance units if we tried this approach. Of course, asynchronous processing would be an option but why go through the hassle when there’s an easier way?

N/query + N/search to the Rescue!

Below, I provide code snippets of the key pieces required to get the job done. The interested reader is invited to put in the little extra effort to stitch these snippets together, with the hope that such an endeavor will deepen understanding. In any case, a decent degree of proficiency with SuiteScript and SQL is necessary to produce the desired.

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.

Step 1: Retrieve the Revenue Plans from the Rev. Rec. JEs

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 * @NModuleScope SameAccount
 */

define(['N/record', 'N/query', 'N/search'],
    function (record, query, search) {
        function afterSubmit(context) {
            // We need to run this in afterSubmit to make sure all fields and links are available; sometimes, beforeSubmit turns out to be too early, especially upon context.type === 'create'.

            // Note: Remember to validate context and avoid approved entries (unless account is configured to allow making non-GL impacting changes to closed periods)
            let rec = context.newRecord;
            rec = record.load({ type: rec.type, id: rec.id });

            var memo, revenuePlan;
            var revenuePlansByLine = {};
            var revenuePlans = [];

            for (var i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
                memo = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i });

                revenuePlan = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'sourcerevenueplan', line: i });
                if (revenuePlan) {
                    if (memo === 'Rev Rec Source' || memo === 'Rev Rec Destination') {
                        revenuePlansByLine[i] = revenuePlan;
    
                        if (revenuePlans.indexOf(revenuePlan) < 0) {
                            revenuePlans.push(revenuePlan);
                        }
                    }
                }
            }

            if (revenuePlans.length > 0) {
                updateRevenueLineMemos(revenuePlans, revenuePlansByLine, rec);
            }
        }

        return {
            afterSubmit: afterSubmit
        };
    });

Step 2: Retrieve the Revenue Elements from the Revenue Plans

In this step, we use SuiteQL to get to the next link in our path: revenue elements. Note that N/search would also get the job done for this step.

By the way, when working with SuiteQL queries, I’ve found Tim Dietrich’s SuiteQL Query Tool invaluable. Be sure to check it out!

let queryResults = query.runSuiteQL({
	query: 
		`SELECT 
			element.id, 
			plan.recordnumber 
		FROM 
			RevenuePlan as plan
			INNER JOIN RevenueElement as element
				ON (plan.createdfrom = element.id)
		WHERE 
			plan.recordnumber IN ( '${revenuePlans.join("','")}' )`
		// We need to escape the strings to avoid 'Unknown identifier XXX' error
}).asMappedResults();

let revenueElements = [];
let revPlansByRevElement = {};

queryResults.forEach(result => {
	revenueElements.push(result.id);
	revPlansByRevElement[result.id] = result.recordnumber;
});

Step 3: Get the Source Transaction Lines from the Revenue Elements

This piece requires N/query because, as of October 2021, the SuiteAnalytics data model does not expose the link from revenue element to source transaction. However, N/search does via the {sourceTransaction.lineuniquekey} join. Hurray!

let revElementsByTrxLine = {}
search.create({
	type: 'revenueelement',
	filters: [
		['internalid', 'anyof', revenueElements]
	],
	columns: [
		'sourceTransaction.lineuniquekey'
	]
}).run().each(result => {
	let line = result.getValue({ join: 'sourceTransaction', name: 'lineuniquekey' });
	revElementsByTrxLine[line] = result.id
	return true;
});

Step 4: Get the Desired Information from the Source Transactions

Here we use SuiteQL as it is more compact than N/search for the job at hand and the results are easier to parse.

let lineInfoByRevPlan = {};
queryResults = query.runSuiteQL({
	query: 
		`SELECT 
			trx.tranid, 
			trx.type, 
			line.memo, 
			line.linesequencenumber, 
			line.item, 
			line.uniquekey 
		FROM 
			TransactionLine AS line 
			INNER JOIN Transaction AS trx 
				ON (trx.id = line.transaction) 
		WHERE 
			line.uniquekey in ( ${Object.keys(revElementsByTrxLine).join(',')} )`
}).asMappedResults();

To provide some context, suppose we decided to use the good old N/search. The search will look something like this. While it is still manageable, the above SuiteQL alternative is arguably more compact. You be the judge.

let lineUniqueKeys = Object.keys(revElementsByTrxLine);

let filters = [];
lineUniqueKeys.forEach(key => {
    if (filters.length > 0) {
       filters.push('OR')
    }
    filters.push(['lineuniquekey', 'equalto', key]);
});

search.create({
   type: "transaction",
   filters: [
      filters
   ],
   columns: [
      'tranid',
      'type',
      'memo',
      'linesequencenumber',
	  'item',
      'lineuniquekey'
   ]
});

Step 5: Update the JE Line Memos

The final step is trivial, now that we have all the information we need.

let lineInfoBySchedule = {};
let 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 (let i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
	let schedule = schedulesByLine[i];
	if (schedule) {
		let 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 });

Wrap Up

It was interesting to find and fit the pieces of this puzzle together. When I produced my initial solution, I incorrectly assumed that I required SuiteQL to get the job done. However, while writing this article, it dawned on me that N/search would suffice. That’s one of the beautiful things about writing; it forces you to challenge your assumptions, if you are willing, often producing deeper insights.

I find the expose to and practice with SuiteQL very valuable. Clearly, NetSuite is moving in this direction and it is welcome as it gives NetSuite professionals more power. Equally important, there are challenges that simply cannot be solved with only N/search e.g. the similar amortization JE line memo challenge that we solved earlier.

If you're a NetSuite Developer or Technical Consultant and you've not yet looked into SuiteAnalytics and the N/query module, you're missing out on a powerful tool. Click To Tweet

Back at Asoville, Larry is once again satisfied, having solved the Amortization JE and Rev. Rec. JE line memo challenges. Perhaps, there are other similar problems waiting to be discovered and tackled. In the meantime, “To SuiteQL or not to SuiteQL?” is really no longer a question for him. The more (good) tools you have in your toolbox, the better equipped you are to solve problems. SuiteQL is one of such tools.


NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, check out how you can become an author/collaborator here.

Also, consider subscribing to our no-nonsense email list to get these insights delivered to your inbox as soon as they’re published. Sometimes, ignorance is a choice. Choose wisely!

Related Posts

Further Reading[+]

Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

6 Replies to “Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines”

  1. Hi. I found this article because I needed to do exactly what you do here. populating memo and other fields on the rev rec journal from the source invoice/sales order. I used your snippets in my code, but I have found out that there is actually much easier way to get what you need. Without going into too much details, What I do is:
    1) get the plans from the JE line (same as you really)
    2) run a revenue element search, where I can join the plan as a filter, but also populate details from an invoice to get the memo, etc.
    3) save the values into an object, where the revenue plan is the key and my custom values are the values
    4) loop through the lines and based on the revenue plan assign the field values based on my custom object values.
    I’m more than happy to share some snippets of code with you.
    I just wanted to share a different approach where you can get to the result with just one simple search and avoid multiple queries:)

    1. Hi Michaela,

      Thanks for dropping by and for sharing your simplification! Like many things, it often takes another pair of eyes to see the “obvious”. Thanks for that!

      By all means, please share a snippet of your updated revenue element search for the benefit of other readers.

      Thanks

      1. Hi.

        no problem. it’s a fairly siple solution. The same applies also to the amortization journals, which I’ve seen a similar article on here, that describes the process through multiple complex queries and searches, where one search is needed again.

        Below is a snippet of my code, where I run the search and set the memo fields on the rev rec journals from the source transaction. Works beautifly for any other source fields line and header alike.

        var formula = “formulatext: case when {revenueplan.recordnumber} in (‘” + revenuePlans.join(“‘,'”) + “‘) then ‘yes’ else ‘no’ end”;

        var revenueelementSearchObj = search.create({
        type: “revenueelement”,
        filters: [
        [“revenueplan.revenueplantype”, “anyof”, “ACTUAL”],
        “AND”,
        [formula,”is”,”yes”]
        ],
        columns: [
        search.createColumn({
        name: “recordnumber”,
        join: “revenuePlan”,
        label: “Number”
        }),
        search.createColumn({
        name: “memo”,
        join: “sourceTransaction”,
        label: “Memo”
        }),

        ]
        });

        var fieldsObject = {};

        revenueelementSearchObj.run().each(function(result) {
        var revenueplan = result.getValue({
        name: “recordnumber”,
        join: “revenuePlan”
        });

        var memoNew = result.getValue({
        name: “memo”,
        join: “sourceTransaction”
        });

        fieldsObject[revenueplan] = {
        ‘memo’: memoNew,
        };

        return true;
        });

        var numLines = newTransactionRecord.getLineCount({
        sublistId: ‘line’
        });

        for (var i = 0; i < numLines; i++) {
        let revenuePlan = newTransactionRecord.getSublistValue({
        sublistId: 'line',
        fieldId: 'sourcerevenueplan',
        line: i
        });
        if (fieldsObject.hasOwnProperty(revenuePlan)) {
        newTransactionRecord.setSublistValue({
        sublistId: 'line',
        fieldId: 'memo',
        value: fieldsObject[revenuePlan].memo,
        line: i
        });

        }
        }

        1. Thank you so much for sharing this optimization! When I have some time, I plan to revise the article to incorporate it.

          I’m not sure you’re quite right about the amortization scenario. Someone else has pointed out an improvement that can combine two of the queries into one. But my understanding is still that we can’t get the desired output with just N/search. Did you actually try your simplified approach? I’m curious to learn more. Thanks

          1. Excellent! Could you please share you solution for the benefit of others? You can post here or email to stories[at]netsuite-insights.com

            Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You deserve to know as soon as we share a new NetSuite Insight.
We won't disturb you with anything else or share/sell your data.