This article is relevant if you would like to produce a NetSuite saved search to build financial reports.
Background
All NetSuite power users learn how to use Saved Search to answer questions and present information. Yes, the relatively new NetSuite SuiteAnalytics Workbooks is coming along — but as of this writing, the newer tool still does not match Saved Search’s simplicity and ease of use.
One category of searches can drive financial information. In NetSuite, any transaction with a Posted flag equal to True means that the transaction will participate in financial-related information. In my 2019 article, Learn How To Craft Better NetSuite Financial Saved Searches, I discuss the best practices for crafting financial-based saved searches. My 2016 article, How to Produce a NetSuite Trial Balance with Saved Searches continues to be very popular as I get frequent inquiries to install our free-of-license charge Financial Saved Search library.
A recent NetSuite implementation for a process manufacturer running a home-grown cost accounting with Sage financials needed a solution to keep their Solver-based financial data warehouse reporting running. We helped the client remodel their general ledger with practices discussed in my Best Practices for Modeling the General Ledger in NetSuite article. In the process, we created 5 different dimensions on top of the conventional GL account number. We built a set of custom cross-reference tables to help translate the new GL/Dimension coding to the old SAGE-based GL world. These cross-reference tables could then be used to help feed the Solver financial data warehouse with minimal structural changes.
During the process, we needed a simple way to identify if GL accounts were either a Balance Sheet or Income Statement Type.
No Simple NetSuite Saved Search to Discover Balance Sheet Versus Income Statement
Anyone that has tried to key on accounts that are Balance Sheet or Income Statement via NetSuite Saved Search has come across the challenge. The conventional approach is to create a Formula-based result set with CASE statements based on the Account Type value. My 2016 article, effectively gives insight into this technique.
More recently, using the new SuiteQL tools, our team could see a new field called “balancesheet” on the accountType table that is a true/false flag that actually gives us the answer. Great! But how do we take advantage of this in our Saved Searches?
Introducing a Custom Other Account Field for Major Account Type
Given the cross-reference table integration work required at the client, it became paramount to quickly identify if a NetSuite account is either Balance Sheet or Income Statement based. So we extended the Account Record to add a new field called Major Account Type (prefixed in the image as PRI BPA Entity Major Account Type). We then created a very simple script (see below) to automatically look up the Major Account Type and store it on the Account Record. Once this value is stored on the Account Record, it becomes a simple Saved Search key where you reference the Account Sublist.
NetSuite SuiteScript 2.1 to Tag Accounts with Major Financial Account Type
For the developer types in the community, here is the simple BeforeSubmit script that does the work. Note the use of SQL query. We increasingly use NetSuite SuiteQL (SQL) to drive our scripted work.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/error', 'N/record', 'N/runtime', 'N/search', 'N/query'],
(error, record, runtime, search, query) => {
//only values 1 & 2 are needed for this script (click the image)
//we had a custom sublist that served the purpose of nice display values
//but it also allowed us to extend for our other cross-reference work
let ACC_MAJ_TYPE = {
BALANCESHEET: 1,
INCOMESTATEMENT: 2,
BOTH: 3,
NEITHER: 4
};
const beforeSubmit = (scriptContext) => {
switch (scriptContext.type) {
case scriptContext.UserEventType.CREATE:
case scriptContext.UserEventType.EDIT:
var currentRecord = scriptContext.newRecord;
currentRecord = setAccMajor(scriptContext);
break;
}
return true;
}
function setAccMajor(scriptContext) {
try {
var currentRecord = scriptContext.newRecord;
var intMjrAccId = '';
//we leverage NetSuite provided account type to be a lookup
//into the accountType table to assess if it is a balance sheet
//major type
var strAccType = currentRecord.getValue('accttype');
var queryResults = query.runSuiteQL({
query: `select balancesheet
from accountType
where ID = '${strAccType}'`
}).asMappedResults();
var strBalSh = queryResults[0].balancesheet;
if (strBalSh == 'T')
intMjrAccId = ACC_MAJ_TYPE.BALANCESHEET;
else
intMjrAccId = ACC_MAJ_TYPE.INCOMESTATEMENT;
//finally populate the custom field with the Major Account Type
currentRecord.setValue('custrecord_pri_bpa_acct_major', intMjrAccId);
return currentRecord;
} catch (e) {
log.error('PRI_BPA_Ent_UE_Account.setAccMajor', e);
}
}
return {beforeSubmit}
});
Extend NetSuite’s Database to Drive Better Reporting
Until the alternative tools to NetSuite Saved Search are easier or better to use, we want to get the most out of the tools we love. Sometimes a simple extra field goes a long way to make a difference in our NetSuite use. For our clients, we simply give them these power tools without license-charge as a way to add value to our recognized NetSuite leadership. If you would like to be notified of new articles as I post them, feel free to subscribe. If you have a NetSuite financial-based saved search challenge, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
