The NetSuite Accountant

Optimize a GL transaction saved search with the CASE WHEN formula

Paul Giese Jul 8, 2021 2:26:11 PM
Paul Giese

A general ledger (GL) export is a common request for budgeting purposes and for the financial statement auditors. In this blog post, we will discuss how to build this with a NetSuite saved search, utilizing the CASE WHEN formula to include the header-level and line-level name information in a single column.

First, navigate to Reports -> New Search and select the ‘Transaction’ type. From here, include the below criteria to get all the posting transactions for May 2021. Remember, you want to use the Period field instead of a Date range to ensure that the transaction saved search will tie to the period's financial statements.

GL Posting saved search criteriaSimple right? Let us look at the search results.

GL Posting saved search initial resultsWait? Why isn’t the Name and Vendor Name populating on my vendor bill and journal entry lines? The issue is because how NetSuite handles the Name field on the line level vs. the header level fields for different transaction types. Thankfully, we can optimize the saved search results using the CASE WHEN formula.

Let us navigate back to the Saved Search results subtab and add a custom formula to solve this issue. Remove the Name and Vendor Name columns. Replace this with the ‘Formula (Text)’ field. In the ‘Formula’ column, add this code:

CASE WHEN {mainname} is NULL THEN {name} ELSE {mainname} END

This formula is saying, if the 'mainname' field is blank, replace it with the 'name' field. Before rerunning the search, replace the generic column name with a custom label. Here is what the final results subtab should look like:

GL Posting saved search result columnsAnd here are the updated saved search results:

GL Posting saved search updated resultsAwesome! When we rerun the saved search, the Name field is populated on both header and line-level fields. This tweak results in optimized export for reporting purposes.

If you need assistance with your NetSuite account, contact intheBlk consulting for help!

Topics: Financial Reporting, NetSuite, Reporting