Financial Reporting, NetSuite, Reporting

Optimize a GL transaction saved search with the CASE WHEN formula

A general ledger (GL) export is a common request for budgeting purposes and the financial statement auditors. In this blog post, we will discuss building 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 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 how NetSuite handles the Name field on the line level vs. the header level fields for different transaction types. We can optimize the saved search results using the CASE WHEN formula.

Navigate to the 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 OptimalData Consulting for help!

Subscribe for updates!