Financial Reporting, NetSuite, Reporting
August 25, 2022
2 min Read
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.
Simple right? Let us look at the search results.
Wait? 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:
And here are the updated saved search results:
Awesome! 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!
