This article is relevant if you are seeking to write a saved search on balance sheet amounts and you want to get the total balance on the account for different date periods.
Background
NetSuite does not maintain a balance sheet summary value in a table that you can query directly in Saved Search. Instead, you use a NetSuite Saved Search to summarize Transaction data to roll up and produce the balance. Because balance sheet accounts are effectively a roll forward cummulative balance, when working with these type of accounts, all the transactions from inception must be included in your summary. Often times, you want a saved search that will give you the current balance and the balance at a different date, such as one month ago. This can be tricky to produce.
Use Advanced Formulas in Saved Search
The goal of this exercise is to get the current bank balance and the balance one month ago. See the image for the resulting outcome.
Whenever working with NetSuite summarized saved searches, I recommend you first work in detail mode. Make sure the records that you are selecting meet the criteria. Thereafter, apply your aggregate formulas. In our example, we want to target a Bitcoin bank account and see the changes in the number of Bitcoins held on the general ledger. This concept works for any balance sheet account. We must create a Saved Search of type Transaction to get started.
We first set our criteria to include just the account we are interested in. This operation is straightforward. Notice that I did not set the date criteria though.
The real work is in a custom result formula that tests to see if the date of the transaction is within the last month. Here, we use NetSuite’s ‘sysdate’ which is simply an Oracle database variable that returns the value of the current date. In our case, we want to know what the date was one month ago. We use the ‘add_months()’ function call with a -1 value to go back one month from now. We then perform math by subtracting the transaction date, {trandate}, from add_months(sysdate, -1). If the result is greater than zero, then we know we need to include it in the balance sheet rollup for last month’s balance. However, if the result is less than zero, then the transaction happened sometime within the last month and should not be included in last month’s balance.
Once you have your key formulas in place, you can then write a case statement to basically determine if you include or exclude the transaction amount during the summary operation. By returning a zero when the amount should not be included, it has no effect on the summary.
Leverage NetSuite’s Saved Search Capacities
Remember that NetSuite’s underlying database is Oracle and Saved Search is effectively producing a database query. Start without defining summary functions to make sure you have detail information that meets your criteria and your search produces the results as you expect. Then add summary functions to the columns you want to summarize.
If you need help enhancing your NetSuite system or would like to get better informatation through Saved Search and Reports, contact us.
Marty, can you get Balance Sheet for multi currency and multibook instance ?
Hello Dmitry, you should be able to. The currency elements must be calculated back to the local (subsidiary) currency via the quantity and exchange rate. I believe the book references would need to target a single book — but I need to try it to confirm. Are you having challenges?
Marty