Gain visibility of hard-to-reach Fixed Asset data where it’s needed most, using a custom SuiteQL reporting tool
Fixed Asset Management troubleshooting
Many NetSuite users experience frustration attempting to reconcile NetSuite’s Fixed Asset Management tool (“FAM”) with the General Ledger. Due to its record architecture, FAM is very difficult to troubleshoot when discrepancies arise. This article will show how to solve a particularly vexing problem, but it is part of a bigger story. After numerous experiences helping clients address challenges with FAM reporting, I realized that a breakthrough is needed to really get at the root of the problem. So I got to work, cataloguing every scenario I could think of that causes FAM to fail. I then created Saved Searches and “SuiteQL queries” (more about queries later), and a written report guide that will become a “FAM Troubleshooting Bundle” to provide relief to all the tired accountants out there. The problem described below and its solution will be part of that bundle. Stay tuned for more details! (Update: Bundle has been released, 12/22/2022)
Most common FAM complaint
Absent a formal poll, it seems that the most frequent pain point that our clients encounter with FAM is the lack of visibility of Assets on Depreciation Journal Entries. Auditors and business users want to look at a Journal Entry for Depreciation and get the detail of which assets were depreciated. Surprisingly, there is no easy way in NetSuite to get this information. In order to get to the data, one would need to traverse multiple custom records and search for values buried in text strings. Until recently, there was no way to do this using any of the standard reporting tools provided by NetSuite. This has now changed, thanks to NetSuite exposing it’s tables to traditional SQL querying and thanks to a new Prolecto created tool. By taking advantage of these resources, I have been able to create a custom sublist of all assets and individual depreciation amounts on a Depreciation Journal Entry, as will be explained in this article. First, some background:
FAM Record Structure
FAM has a different record structure than the standard subledgers such as A/R and A/P. I’ve written articles about FAM’s general records and mechanics before on this blog (see my articles “Fixed Asset Management” and “Accounting Periods and FAM Reports“), so I won’t take up too much space here on the general topic. For purposes of this article, all we need to understand are 2 record types:
- Depreciation History Records (“DHR”): Custom records that are linked to each asset, showing (among other data) monthly depreciation amounts. Each asset has many DHRs attached to it. There is one DHR per Period per Asset representing the monthly depreciation, and holding data about the Subsidiary, Class, and Department for the Asset.
- BG Summary Records: These are custom records that are automatically generated once the DHRs exist. They are summary records that hold the aggregate amount of all DHRs for every combination of Period, Subsidiary, and GL account (as well as Department and Class, if Assets are classified as such) for Deprecation. BG is short for “BackGround”, since these records sit in the background and are not easily visible to the FAM user.
Each time new DHRs are generated (for example, when a new asset is added), existing BG Summary record amounts are replaced with new ones to reflect the updated aggregate amounts of monthly depreciation. When a user triggers the Depreciation process in FAM, the BG Summary records are assessed by the system, and Journal Entries are created based on the amounts in the BG Summary records. There can be many BG Summary records to one Journal Entry. The BG Summary records are then tagged with a link to the Journal Entry.
Here is a diagram of the relationship between these records:
Key Weakness of the Record Structure
The key weakness of the above structure is the way that the DHRs are linked to the BG Summary records. Or perhaps more accurately, the way that they are NOT linked to the BG Summary records. There is no inherent field on the DHRs holding a link between the two records. Rather, NetSuite uses a record numbering convention to preserve the story of how the BG Summary records were created. The record ID of BG Summary records will be the same as the record ID given automatically to the DHRs that are created for scheduled depreciation. Now we can understand the difficulty in getting a list of assets from the Journals created – we need to first get the BG Summary records from the JE, then find all Depreciation History records having a common record ID, then find the underlying assets of the DHRs.
Prolecto SuiteQL Rendering Game Changer
NetSuite recently opened up the ability to use SQL language to query the database, and create complex table joins. My colleague Boban at Prolecto has created a revolutionary new reporting tool called “Prolecto Query Renderer” which is absolutely magical in it’s ability to create, manipulate, and display reports based on SQL queries. It can be be mastered by anyone who knows SQL language, even with no other programming background (like me). As with all of Prolecto’s bundles, the tool is free to our clients. Using this tool, I was able to craft a report that shows the asset breakdown and individual depreciation amounts that were summarized on the Journal Entries.
For more information about the Prolecto Query Renderer, and a related tool called “NetSuite SQL Query Tool” which is part of the Prolecto Utilities Bundle, see the below articles:
Render NetSuite SQL Queries Like Saved Searches
Depreciation Journal Asset Sublist
Aside from the PRI Query Renderer‘s ability to present complex query in a report format, it also has the ability to present query results against a record as a sublist or HTML field. Not only do we have a report that shows the asset breakdown of Journal Entries, we can also display the individual breakdown against each Journal Entry! Please see the below screenshots for a sample of how this appears.
High Level Discrepancy Review
Using the Prolecto Query Renderer tool, we also can produce a high-level summary report that shows the total amounts of Depreciation Journal Entries created using FAM and compares it to the total of the BG Summary Records (the “bridge” record linking Journals to Depreciation History Records) as well as the total of the Depreciation History Records. This is a long-awaited solution for anyone who has attempted to reconcile what the Asset General Ledger accounts show and what FAM reports show. See below for a sample. Note that the report includes links to the Journal Entry transactions and a drill down to detailed report.
Conclusion
I hope that this article provided inspiration and conveyed a sense of what is possible with our custom toolkit. It was certainly gratifying to be able to solve a problem that previously seemed intractable. If you would like to learn more about FAM troubleshooting or about the Prolecto Query Renderer, we would love to hear from you.
Postscript: FAM Reconciler Bundle Released!
Please be sure to read the subsequent article announcing a complete FAM reconciliation toolkit, which includes the tools described in the above article:
Conversations with Meir Bulman 
One thought on “Finally! Trace Fixed Asset Depreciation Journal Entries Back to Underlying Assets”