Michoel Chaikin NetSuite developer with 7 years of experience implementing and customizing NetSuite as well as full-stack development, systems integration, data management and report development. Author of popular "NetSuite Field Explorer" Chrome extension. Valued contributor to NetSuite forums and open-source projects.

Learn How to Generate Meaningful Amortization JE Memo Lines When Using Native or No JE Approval

4 min read

Amortization JE memo lines before and after

In an earlier NetSuite Insights’ article on this topic, the author outlines a way to produce more meaningful amortization journal entry memo lines via scripting. However, the solution offered there only works in accounts where a custom journal entry approval workflow is in place. This article presents an adaptation that works for accounts configured to use the native JE approval option or no JE approvals at all.


TL;DR

NetSuite offers three options when it comes to Journal Entry (JE) approval – see the public documentation on JE approvals if you do not have access to SuiteAnswers. [I]NetSuite (March 4, 2011). Journal Entry Approval Overview. Available at https://netsuite.custhelp.com/app/answers/detail/a_id/7737. [Accessed on: April 20, 2022]

  • Option 1: No JE approval i.e. JEs are automatically posted upon creation. This can be configured by unchecking the Require Approvals on Journal Entries under Setup >> Accounting >> Accounting Preferences >> General. Note: This option will not be visible if the “Journal Entries” is checked under the “Approval Routing” subtab.
NetSuite preference that controls whether or not JE approvals are required
  • Option 2: Native JE approval, which can be turned on by checking the box mentioned above in Option 1. This will expose an “Approved” checkbox on JEs.
approval checkbox when using NetSuite's native JE approval
  • Option 3: SuiteFlow-based JE approval workflow; refer to SuiteAnswers ID 51389 for more information.
NetSuite setting for enabling custom JE approval workflow

If your account is configured for option 1 or 2 above, you will need a workaround like the ones described in this article to be able to generate meaningful amortization JE memo lines.

This article is relevant if you use options 1 or 2 above. Refer to the original article if you use option 3.


Problem

The original problem of why NetSuite AJE lines are not very meaningful is well-articulated in the reference article and I will not repeat it here. However, when trying to implement that solution, it simply did not work for me. After some research, I realized that due to a limitation in NetSuite documented in SuiteAnswers Answer Id 85137 [II]NetSuite (May 23, 3019). User Event triggers doesn’t fire on Amortization Journal Entry creation. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/85137. [Accessed on: April … Continue reading, the beforeSubmit and afterSubmit events are not triggered upon creating amortization journal entries when using NetSuite’s native approval or no JE approval. While there is an enhancement request #235749 for this, it is unclear if or when the issue will be resolved by NetSuite. Thus, some innovation was required to work around the situation.


Solution

In seeking to solve this problem, I considered three different approaches which I briefly outline below before delving further into the solution that I settled for.

Approaches Considered

  1. Enable SuiteFlow-based JE approvals and create a workflow to default all journals to “Approved”. This would allow us to leverage the original solution; in fact, it is the suggested approach in SuiteAnswers. However, after discussions with other team members, we did not want to add this additional complexity to our NetSuite environment.
  2. Create a scheduled script to update the JEs after the fact. Similar to the first option, we did not want to add additional background scripts. Furthermore, this approach would mean that there would be a delay between amortization JE creation and when the memo lines will be updated.
  3. Run the logic to update the amortization JEs in the beforeLoad event in view mode. I like this approach because it has very minimal performance implications because the deployment is limited to the view action. Moreover, it does not need to perform any I/O unless the JE needs to be updated. Thus, we settled for this approach. However, depending on your use case, one of the other options might better meet your needs.

Limitations of the beforeLoad Approach

  1. The record has to be viewed before the fields are updated. For example, if reports are run on the JE before they are ever viewed, they would not have the fields updated.
  2. The first user viewing the record needs to have permission to edit JEs. As a workaround, the script deployment could be set to run under using a role that has this permission.
  3. During testing, we found that editing the record in beforeLoad can cause spurious “Record has been changed” errors. To get around this, we load and save the record in the script, and then redirect back to it.

Sample Implementation of the beforeLoad Approach

Here’s what my final implementation looked like:

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 * @NModuleScope SameAccount
 *
 * Update Amortization Journal Entries with a more meaningful memo field, and source transaction tagged in custom fields.
 *
 * Code was adapted from a sample shared at:
 * https://netsuite.smash-ict.com/learn-how-to-generate-meaningful-amortization-journal-entry-memo-lines/
 *
 * Author: Michoel Chaikin <
        
            mi*************@ca******.au
            
                
                
                
            
            
                
                
                
            
        
>
 */

define(["N/record", "N/query", "N/redirect"], /**
 * @param { import("N/record") } record
 * @param { import("N/query")} query
 * @param { import("N/redirect")} redirect
 * @returns { any } script definition
 */ (record, query, redirect) => {
  /**
   * @param { import("N/types").EntryPoints.UserEvent.beforeLoadContext } context
   */
  function beforeLoad(context) {
    // Journals created through the "Create Amortization Journal Entry" page do not trigger user event scripts unless
    // workflow based Approval Routing is enabled for Journals (see SuiteAnswer # 85137)
    // To ensure the script is executed, it is run on beforeLoad the first time the record is viewed

    const isFromAmortization = context.newRecord.getValue("isfromamortization");

    const firstLineAmortizationSource = context.newRecord.getSublistValue({
      sublistId: "line",
      line: 0,
      fieldId: "custcol_cs_amortization_source",
    });

    if (context.type !== context.UserEventType.VIEW ||
      !context.newRecord.id ||
      !isFromAmortization ||
      firstLineAmortizationSource
    ) {
      return;
    }

    const schedules = getSchedules(context.newRecord);

    if (schedules.length <= 0) {
      log.debug({ title: "No schedules found on JE lines" });
      return;
    }

    const sourceTransactionDetails = getSourceTransactionDetails(schedules);

    try {
        updateJournal(context, sourceTransactionDetails);
        // Redirect back to the record so our updates are reflected
        redirect.toRecord({ type: context.newRecord.type, id: context.newRecord.id });
    } catch(error) {
        // Updating could fail if the user doesn't have edit permission or the journal is in a closed period
        log.error({ title: "Error updating journal", details: JSON.stringify(error) });
    }
  }

  /**
   * Retrieve the schedule numbers from the JE lines.
   * We need to retrieve the schedules from the record because the fields are not accessible via SuiteQL
   *
   * @param { import("N/record").Record } rec
   * @returns {string[]} Array of the schedule Internal Numbers
   */
  function getSchedules(rec) {
    const schedules = [];

    const sublistId = "line";

    for (let line = 0; line < rec.getLineCount({ sublistId }); line++) {
      const schedule = rec.getSublistValue({ sublistId, line, fieldId: "schedulenum" });

      if (schedule) {
        schedules.push(schedule);
      }
    }

    return [...new Set(schedules)]; // Schedule is repeated for source and destination lines. Remove the duplicates
  }

  /**
   * @typedef {{scheduleId: string, id: string, tranId: string, type: string, memo: string, lineSequenceNumber: string, lineId: string, item: string} } SourceTransactionDetails
   */

  /**
   * @param {string[]} schedules List of Internal IDs of the Amortization Schedules
   * @returns { SourceTransactionDetails[] } Details from the source transactions
   */
  function getSourceTransactionDetails(schedules) {
    const sql = `
      SELECT
        amortizationSchedule.id AS scheduleId,
        transaction.id,
        transaction.tranId,
        transaction.type,
        transactionLine.id AS lineid,
        transactionLine.memo,
        transactionLine.lineSequenceNumber,
        transactionline.item
      FROM
        amortizationSchedule
        JOIN transaction ON (transaction.id = amortizationSchedule.sourceTran)
        JOIN transactionLine ON (
            transactionLine.transaction = transaction.id
            AND transactionLine.amortizationSched = amortizationSchedule.id)
      WHERE
        AmortizationSchedule.id IN (${schedules.join()})
    `;

    return query
      .runSuiteQL({ query: sql })
      .asMappedResults()
      .map(result => ({
        scheduleId: String(result.scheduleid),
        id: String(result.id),
        tranId: String(result.tranid),
        type: String(result.type),
        lineId: String(result.lineid),
        memo: String(result.memo),
        lineSequenceNumber: String(result.linesequencenumber),
        item: String(result.item)
      }));
  }

  /**
   * @param { import("N/types").EntryPoints.UserEvent.beforeLoadContext } context
   * @param { SourceTransactionDetails[] } sourceTransactionDetails
   */
  function updateJournal(context, sourceTransactionDetails) {
    const rec = record.load({ type: context.newRecord.type, id: context.newRecord.id });

    const sublistId = "line";

    for (let line = 0; line < rec.getLineCount({ sublistId }); line++) {
      const scheduleId = rec.getSublistValue({ sublistId, line, fieldId: "schedulenum" });
      const memo = rec.getSublistValue({ sublistId, line, fieldId: "memo" });

      const details = sourceTransactionDetails.find((x) => x.scheduleId === scheduleId);

      if (!details) {
        continue;
      }

      if (details) {
        // Requirement from our accounting team to link back to the source transaction in reports. 
        // Thus, we use custom columns field to capture the amortization source info.
        rec.setSublistValue({ sublistId, line, fieldId: "custcol_cs_amortization_source", value: details.id });
        rec.setSublistValue({ sublistId, line, fieldId: "custcol_cs_amortization_source_line", value: details.lineId });
        rec.setSublistValue({ sublistId, line, fieldId: "memo", value: formatMemo(memo, details) });
      }
    }

    rec.save({ ignoreMandatoryFields: true });
  }

  /**
   * @param { string } existingMemo
   * @param { SourceTransactionDetails } details
   * @returns { string } formatted memo
   */
  function formatMemo(existingMemo, details) {
    if (existingMemo !== "Amortization Destination" && existingMemo !== "Amortization Source") {
      return existingMemo;
    }

    let lineType;
    if (details.type === "Journal") {
      lineType = "";
    } else {
      lineType = details.item ? "Item " : "Expense ";
    }

    return `${existingMemo}: [${details.tranId} ${lineType}Line # ${details.lineSequenceNumber}] ${details.memo}`;
  }

  return {
    beforeLoad
  };
});

Conclusion

I hope that you have found this article helpful. If you come across other ways to tackle this problem, be sure to share them! You might also find my NetSuite Field Explorer Chrome extension useful.


Related Posts


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

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!

Further Reading[+]

Michoel Chaikin NetSuite developer with 7 years of experience implementing and customizing NetSuite as well as full-stack development, systems integration, data management and report development. Author of popular "NetSuite Field Explorer" Chrome extension. Valued contributor to NetSuite forums and open-source projects.

6 Replies to “Learn How to Generate Meaningful Amortization JE Memo Lines When Using Native or No JE Approval”

  1. Hi there, I’m getting an error with this script… steps I’m taking are:
    Before deploying the script in test environment, the journal I’ve got loaded comes up with Amortization Destination & Source.
    After deploying the script to the journal as testing, try to reload the script and getting an unexpected error, then undeploying the script and refreshing the journal, this time it does come up with the changes made to the memo… Any idea why this would happen?

  2. Hi,

    Can you please share some insight into the two fields {custcol_cs_amortization_source} & {custcol_cs_amortization_source_line} as I’m getting an unexpected error.

  3. Hi Michoel,
    Regarding the custom columns in your script, can you share more info regarding this?
    I’m getting an unexpected error.

    1. Hi J G,

      Sure! Here is how there are set up in my account.

      Both are Transaction Line Fields

      Label: Amortization Source Transaction
      ID: custcol_cs_amortization_source
      Type: List/Record
      List/Record: Transaction
      Store Value: T
      Applies To: Journal

      Label: Amortization Source Transaction Line
      ID: custcol_cs_amortization_source_line
      Type: Integer Number
      Store Value: T
      Applies To: Journal

      Regards,

      Michoel

  4. I believe there is a bit of an issue here with redirecting to the record part:

    updateJournal(context, sourceTransactionDetails);
    // Redirect back to the record so our updates are reflected
    redirect.toRecord({ type: context.newRecord.type, id: context.newRecord.id });

    This is a before load script, so when the redirect happens the first time, it will trigger this exact same script again and the redirects continue in an endless loop and browser can’t display the page because there are too may redirects happening.

    The second issue is a minor one, still an issue nonetheless if someone would just copy-paste this code:

    return {
    beforeLoad
    };
    – NS script validator throws an error because the entry point functionw as not correctly defined – it should have been beforeLoad: beforeLoad

    Anyway, interesting concepts. I followed this pattern for a customer request, however, I’ve used a different approach, with search.lookups to get the data that I need, rather than SuiteQL – I’m going to have a look on this approach too, seems interesting.

    1. Hi Adrian,

      Thanks for dropping by and sharing your observations.

      In response:

      1. The author’s code will not produce an endless loop because of line 41. However, since that is a parameter specific to his use case, you’d need a comparable check to prevent endlessly looping.
      2. This is SuiteScript 2.1 code and the compact syntax is completely valid.

      It’s never a great idea to copy-paste code. These samples are for instruction and illustration. Readers are expected to understand the concepts and adapt to their use cases.

      Cheers

Leave a Reply

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

×