Ever get confused about which fields do what when creating A/R – oriented SuiteQL reports? Here’s a table that help organize the fields and their meanings:
Transaction Types
Table
Field
Meaning
All Types
transactionLine
foreignamount
Transaction amount in transaction currency
All Types
transactionAccountingLine
amount
Transaction amount in GL base currency
Invoice
transaction
foreignAmountPaid
Total paid amount, in the transaction currency
Invoice
transaction
foreignAmountUnpaid
Total unpaid (open) amount, in the transaction currency
Customer Deposit, Payment, Credit Memo
transaction
foreignPaymentAmountUnused
Total unapplied (open) amount in the transaction currency
Prolecto’s Content Rendering Engine (“CRE”): A versatile tool to create and distribute external-facing content such as customer forms, special reports, and Vendor Remittance statements. The content can be distributed as PDF or HTML embedded in an email, and either automatically sent or via a suitelet. This tool is extremely powerful, and we often use it as part of holistic business process improvements.
SuiteQL queries: Since early 2021 the ability to use SQL to bring together far-reaching data into one report in NetSuite has been a game changer.
The latest version of the CRE tool leverages SQL queries as the “inputs” for the content shown in a PDF or email body.
In this article I will describe the background of the components needed to produce a Customer Statement and the structure in NetSuite that allow us to bring the data together in understanding a customer’s balance.
NetSuite Accounts Receivable Patterns
Accounts that are of the Account Type “Accounts Receivable” (and “Accounts Payable”) have special treatment in NetSuite. Unlike other account types, when a transaction impacts a GL Account that is of one of these types, it can be “settled”. In other words, in addition to a simple running total of a balance based on debits and credits, specific transactions can be applied to each other. When entering a customer payment and “applying” it to unpaid invoices, NetSuite preserves a link between the transactions. At first glace this may be obvious, but there are some important ramifications that might not be intuitive:
Presentation of balance aging on A/R or A/P reports depend on settlement of invoices. Even if a customer or vendor balance is zero, there may be results showing on a standard Aging report as positives and negatives, if transactions are not “settled” against each other.
Reporting tools in NetSuite can show values based on “settlement”. Certain transaction types will have values as “open” or “unpaid” balances, and other transactions types may have “amount applied” or “amount unapplied”.
Currency revaluation works differently on “settled” transactions vs. open transactions, if the underlying transactions are in a foreign currency as compared to the Subsidiary’s base currency.
Customer Deposits
One of the important improvements that we can provide is the ability to include Customer Deposit balances (prepayments) in a customer’s open balance. Being able to present open Customer Deposit balances in a similar way as open Invoices and open Credit Memos is challenging.
Customer Deposits in NetSuite (prepayments from customers) have special treatment for A/R reporting purposes. They do not impact Accounts Receivable, rather they result in a credit to a liability account, “Customer Deposits”. Yet, they represent an important part of the Accounts Receivable collection cycle. NetSuite allows the application of Customer Deposits to invoices, via a transaction called “Deposit Application” which acts as a reclassing entry to move the credit out of Customer Deposits liability accounts into an Accounts Receivable account so that it can settle an invoice or other A/R balance.
Journal Entries
Generally, Journal Entries should be avoided when attempting to impact Accounts Receivable. If there is no “name” on the line, there will be no way to settle the A/R balance. If a name exists on the JE line that is impacting A/R, it can be settled against any other A/R transaction. Another challenge with Journal Entries impacting A/R is that not all of the fields that are available on Invoices or Credit Memos are available on Journal Entries in Saved Search or SuiteQL query reporting. The table at the end of this article will show which fields are available for Journal Entry balances.
Reporting Approaches
There are really 2 ways to produce analysis of customer balances using SuiteQL tools.
Balance Approach: It would be straightforward to produce a simple analysis of the customer balances using SQL. We could query the transaction impacts in either the base currency or the transactional currency by pulling in the below fields:
SELECT tra.amount,
trl.foreignamount
FROM Transaction Tran
INNER JOIN Transactionline TRL ON (Tran.id = TRL.transaction)
INNER JOIN Transactionaccountingline TRA ON (Tran.id = TRA.transaction AND TRL.id = TRA.transactionline)
Aging Approach: If we want to get Aging buckets, we need to know not just the “debits and credits” side of transactions, but also the amounts still open on each transaction. For example, if an invoice date was 60 days ago, and a partial payment was made 30 days ago, we do not want to see a debit in the 60 column for the full invoice amount and a credit in the 30 day column for the full payment amount. Obviously, we want only the unpaid balance of that invoice in the 60 day bucket.
Generally, companies would be more interested in the second approach. We thus need to find which fields to pull in a query to provide the “Open Balances”. The tricky part here relates to Transactions Types and the concepts of what NetSuite considers an “Open” balance. The below table shows the tables, field mapping, and meanings of each available field:
Transaction Types
Table
Field
Meaning
All Types
transactionLine
foreignamount
Transaction amount in transaction currency
All Types
transactionAccountingLine
amount
Transaction amount in GL base currency
Invoice
transaction
foreignAmountPaid
Total paid amount, in the transaction currency
Invoice
transaction
foreignAmountUnpaid
Total unpaid (open) amount, in the transaction currency
Customer Deposit, Payment, Credit Memo
transaction
foreignPaymentAmountUnused
Total unapplied (open) amount in the transaction currency
Total unapplied (open) amount in GL base currency (note: JE only if Cr to A/R)
Observations
Observe the following general guidelines in the above table:
The “Transaction” table fields related to open/unapplied amounts are not available for Journal Entries
The “transactionLine” and “TransactionAccountingLine” table fields are available for Journal entries, and the specific fields available depend on whether there is a credit or debit to A/R
“transactionAccountingLine” table fields only show the Base Currency (GL) amounts, and the other two tables’ fields only show the original transaction currency amounts
Conclusion
Once we understand the fields and tables that are available, we can use SQL arguments to pull in the necessary field values to craft the balances and aging buckets. I worked together with members of the incredibly talented team at Prolecto to get the queries wired up to the CRE tool to produce the below output:
If you would like to learn about working with us, or would like to hear how we can put our tools to work for you, please feel free to reach out!
Techniques for getting NetSuite to correctly auto-eliminate intercompany balances
Auto-Elimination: Background
NetSuite’s Automatic Intercompany Elimination feature can really ease the burden for multi-subsidiary period close. The tool finds balances to eliminate based on a transaction line field called “eliminate”, and reverses any impact of those lines into an “Elimination” subsidiary. I’ve recently written an article describing this feature and how to analyze amounts that were eliminated. Here is a list of some resources that may be helpful in understanding how the tool works:
Challenge: Clean Up Historical Intercompany Amounts
We recently were contacted by a client that was having difficulty understanding why intercompany account balances were not clearing out each period even though they were using the Auto-Elimination feature in NetSuite. After some analysis using SuiteQL queries to search for fields not available in Saved Searches, we were able to get the root of the problem and fix it, so that going forward the balances should clear out completely without manual work.
Solution (in short): reverse, re-import, revalue, eliminate
The causes of the non-eliminating amounts were:
Transactions that did not have the “Eliminate” checkbox “flagged” for elimination
Currency revaluations in the intercompany accounts, likewise not flagged for elimination
Manual Journal Entries into elimination subsidiaries that couldn’t always offset foreign subsidiaries’ intercompany amounts (due to currency translation).
In order to fix the problem, we needed to do the following:
Find the sources of Currency Revaluation transactions that were not flagged for Elimination, and reverse the impacts in both the Base Currencies and Foreign currencies
Re-import those balances, using the same exchange rates, but this time, flagged for elimination.
Find the remaining non-flagged for elimination amounts and do the same (reverse and re-import, flagged for elimination)
Run Currency Revaluation for the first available open period, verify that no Currency Revaluations remained that impacted intercompany accounts not flagged for elimination
Reverse the impact of any manually-booked elimination entries
Run Auto-elimination; verify zero balance in the consolidated financial statements for the intercompany accounts
Now let’s take a look under the hood to understand how the “eliminate flag” works, and why certain intercompany accounts won’t just automatically eliminate:
Intercompany Elimination and Account Types
One of the key considerations in running Auto-Elimination is the different treatment of Elimination based on Account Type. The Account Type selected on the GL Account record is a consequential decision to make when each account is set up, because the Account Type has a significant impact on all sorts of transactions and processing in NetSuite, and the Intercompany Elimination process is one example.
In order to flag transaction lines for Elimination, the “ELIMINATE INTERCOMPANY TRANSACTIONS” checkbox field must be checked on the account record. Yet, even when this is checked, the behavior of Auto-Elimination based on that flag will still depend on the Account Type. Here is a table of differences to Auto-Elimination based on Account Type:
Account Type → ——————————- Behavior ↓
Accounts Receivable/ Accounts Payable
Income / COGS / Expense / Equity
Other Assets / Other Liabilities (Current or Long-Term)
“Eliminate” can be selected or deselected at the Transaction Line level?
No – tagged by default, cannot be “un-tagged”
Yes
Yes
Forces a “Representing Subsidiary” entity as Customer/Vendor?
Yes
No entity required
No entity required
Links Elimination Entries back to Source Transactions?
Yes
Yes
No
Elimination JE is based on Summary Amounts / Specific Transactions?
Depends on “Journal Entry Summarization” Preference
Depends on “Journal Entry Summarization” Preference
Always Summarized
Elimination JE respects original Class, Department, Location?
Yes
Yes
No
Elimination JE Auto-Reverses in Next Period
Yes
No
Yes
Intercompany “Other Asset” or “Other Liability” accounts
Often, companies will opt not to use an Accounts Receivable or Accounts Payable type of account for Intercompany balances. Sometimes, this is to maintain natural netting of amounts when a subsidiary has both payables and receivables to/from a different subsidiary. (Side note: NetSuite’s Intercompany Netting feature accommodates this requirement and is worth considering). Using non-A/R and non-A/P accounts for Intercompany presents some challenges with period end automatic elimination. As can be seen from the chart above, non-A/R and non-A/P accounts need the “Eliminate” flag checked on Transaction Lines in order to auto-eliminate. Thus, if the practice of flagging transaction lines was not in place historically, it can be quite challenging to fix so that balances in these accounts are eliminated in consolidated financial reports, as was observed in the client situation described above.
Additional Complication – Currency Revaluation and Elimination Flag
Another interesting observation of the Elimination flag is that it exists on Currency Revaluations, although there doesn’t seem to be a way to expose it on the currency revaluation transaction forms. The determinant of whether the field will be checked on Currency Revaluations depends on the source transactions that are subject to Currency Revaluation. If the source transactions in a non-base currency are flagged for elimination, the currency revaluation will likewise be flagged for elimination. (For additional info regarding Currency Revaluation, see my previous article, “Understand NetSuite Currency Translation”).
Best Practices
When encountering accounting cleanup projects such as this one, I like to help clients think of how to best avoid errors and costly manual work. When it comes to Intercompany Elimination, if a non-A/R, non-A/P account is used, it would be good to create a simple script or workflow that forces the user to check the “eliminate” flag before saving. Normally, a saved search alert would be a good, low-tech solution, but in this case the field is not available in searches, so a little more development work would be needed to stay in control. If you would like to learn more about best practices for Intercompany Consolidation, feel free to reach out!
Announcing a Fixed Asset supplementary bundle that can diagnose and help resolve discrepancies
NetSuite Fixed Asset Common Questions
Over the past few years, I have had the privilege of helping clients understand and use NetSuite’s Fixed Asset Management tool (“FAM”). I’ve been approached with support requests over time, and begin to see some recurring patterns in the nature of these requests. Here are some of the common challenges posed by clients in trying to understand their Fixed Asset numbers:
Why do the Asset Net Book Values not tie out to the balances in the Fixed Asset ledger accounts?
How can I be sure that the system-generated Depreciation Journal Entries are correct?
Where can I find a breakdown of the Assets that were Depreciated in the system-generated Depreciation Journal Entries?
Why do the native Fixed Asset reports offered as part of the FAM module not tie to saved searches driven by Fixed Asset records?
We adjusted Depreciation Journals, yet the FAM system reports are still incorrect. Why?
FAM Records
I’ve written previously about NetSuite’s Fixed Asset Management tool (“FAM”), and the below previous articles describe some of the foundational record structure of the module.
Many of the challenges with using NetSuite’s Fixed Asset Management tool stem from the way that FAM differs from general subledgers like Accounts Receivables / Accounts Payable. The various records that the FAM module contains do not directly impact the ledger, rather they support and drive automation that in turn creates transactions. This implies a risk, since an entry can theoretically be booked to the GL that doesn’t match what shows in the FAM module.
What Can Go Wrong?
Here are some specific scenarios that cause FAM reports to fall out of sync:
Historical mid-life assets imported did not include special records for accumulated depreciation to-date
Journal Entries were manually booked to Fixed Asset accounts, without corresponding entries in the module
Asset records or Depreciation History Records were manually modified or deleted after Depreciation was posted
Initial Acquisition records are missing or do not have a date
Announcement! Fixed Asset Module Reconciler
After engaging in several repeat troubleshooting projects helping clients understand their Fixed Asset reporting in NetSuite, I realized that it would be helpful to create a supplementary system that could help clients quickly diagnose and fix problems with their Fixed Asset environment. With support from the Prolecto Team, I’ve created a new bundle that is a complete toolkit to locate any problems with Fixed Asset records. The flagship report in the bundle is a SuiteQL Query that shows the breakdown of assets depreciated on a Journal Entry for depreciation generated by the FAM module, as described in my previous article.
Example: Journals Not Linked to FAM
Here’s an example of a common problem: Journal entries booked manually for depreciation. These Journals have no linkage to FAM, thus generally they would throw off the reporting. The saved search to locate these is relatively simple if you understand the record structure in NetSuite. There are no direct links from assets to depreciation journals, but there is an indirect link through a record called “BG Summary Record”. A saved search that find Journals for Depreciation that are missing a link to a BG Summary record will reveal these entries:
Conclusion
My very first project as a NetSuite consultant was a Fixed Asset implementation project. It is very gratifying to be able to provide what I believe is a very valuable resource to the community. Like all Prolecto bundles, the FAM Reconciler is free to our clients. If you would like to hear more about getting more transparency into your Fixed Asset technology, or for all NetSuite related projects, please reach out! We would be glad to hear from you.
Get clarity on hard-to-find NetSuite elimination entry sources to ease consolidation
Consolidation and Elimination Entries
NetSuite OneWorld offers some really powerful tools to make consolidated reporting simple. A key feature offered is the “Automate Elimination Entries” tool. The concept is straightforward: Intercompany transactions that occur during the period are “flagged” for eventual elimination, and at month end, when initiated, NetSuite will find all transaction lines that have been flagged, and reverse the impact of these transaction lines to a designated “Elimination” Subsidiary by booking special Journal Entries automatically.
Trace Elimination Entries to Source Transactions
Automatically-generated Elimination entries in mostinstances get tagged on each line with a link back to the source transaction.
These links to the source transaction are in the form of URLs that when clicked, open up new transaction windows as popups. It is not currently possible to reach the “source” transactions from Elimination Journal Entries in Saved Searches or SuiteQL Queries, though the field is available via script as can be seen below (hat tip to Michoel Chaikin for his useful NetSuite Field Explorer).
NetSuite Elimination Entries missing “Source”
Although, as mentioned, the Source transactions cannot be accessed via Saved Search or Query, there is a native report that shows detail from the source transactions. It is called “Intercompany Elimination” and can be accessed via Reports > Financial. However, there are some Elimination Entries that will not get tagged with a “source” transaction and will likewise not appear on the native “Intercompany Elimination” report. This is documented in SuiteAnswers article 34241 and in SuiteAnswers article 106926. As stated in those articles, Balance Sheet accounts other than Accounts Receivable and Accounts Payable (and Equity – not mentioned in the article) will not have a link to the source transactions on the Elimination Journals. This means that for these types of accounts, it is even more difficult to reconcile transactions that should have eliminated and what actually posted automatically to the Elimination Subsidiaries.
Additionally, it is difficult to properly reconcile amounts even those in accounts that do get tagged with “Source”, since the native “Intercompany Elimination” report only goes one way – it shows the Intercompany Elimination entries that were auto-created, and traces back to the original source transactions. There doesn’t seem to be a standard way to start with entries subject to elimination and test whether they were all successfully eliminated.
“Eliminate” flag on transactions
Although I mentioned above that the “source” field cannot be accessed via SuiteQL queries, there is a way to find the “sources” of Elimination Journal Entries, and even when there is no “source” link as described above. There is a field called “eliminate” on all NS transactions, though it often is hidden on the forms. This field is a checkbox that drives the Automatic Elimination process. If we can search all transactions that have this flag checked, we can see the amounts that should have eliminated, and that can be a basis of comparison to the actual eliminated amounts.
SuiteQL Strikes Again!
Although the Eliminate flag is not available in Saved Search, it is available in SuiteQL queries. SuiteQL is a rapidly growing resource for reporting in NetSuite, and several tools have emerged to help the user create reports that have depth and breadth in the database. See my previous articles that discuss ways that SuiteQL can be leveraged. See also Marty Zigman‘s blog article about a custom “renderer” tool that greatly expands the use of this type of reporting.
Here is a sample basic Query that shows transactions that are flagged for Elimination. Carefully note the “FROM” argument and the way the Joins are built; this is the way to bring together all of the elements of a transaction. Also note that the Elimination flag is “tl.eliminate”, which means that the field lives in the transactionLine table (tl is the alias for that table):
FROM transaction t INNER JOIN transactionline tl ON t.id = tl.transaction INNER JOIN transactionaccountingline tal ON tal.transaction = t.id AND tal.transactionline = tl.id INNER JOIN subsidiary s ON s.id = tl.subsidiary
WHERE
tl.eliminate = 'T'
AND
t.trandate <= '06/30/2022'
ORDER BY
BUILTIN.DF(tl.subsidiary)
,t.id DESC
Conclusion
I have found that the above query gives the most value when viewed with and compared to other reports, such as the “Intercompany Elimination” report described above, or supplementary saved searches, such as the one suggested in SuiteAnswers article 34241.
NetSuite knowledge tends to build on itself. A whole world of reporting possibilities opened up once I learned the SQL reporting techniques in NetSuite. If you would like to hear more about expanding your analytics horizons, please reach out!
In an earlier article, I presented the various types of currency concerns and the difference between Currency Revaluation and Currency Translation in NetSuite. This article will address a complex problem (and solution!), but this is not going to make sense to users without familiarity of the basics. If you are not yet comfortable with these topics, first check out SuiteAnswers article 7733 or SuiteAnswers article 7734 – unless you have trouble falling asleep. To me, this topic is exciting; but to non-accountants, well, it may be right up there with paperclip collections.
Migrating Consolidated Financial Statements
Situation Background
An international company using NetSuite OneWorld needed to onboard a group of foreign currency subsidiaries into NetSuite from other systems. These subsidiaries had been purchased and operating under the new corporate ownership for some time, and therefore there had been several periods’ worth of consolidated financial statements already issued. We needed to bring in the beginning trial balances, but also needed to make sure that the beginning balances agreed with the published financial history at the corporate consolidated level.
NetSuite Consolidation
The financial activity (income statement) and position (balance sheet) of foreign currency subsidiaries needs to be expressed in the currency of the parent entity to produce meaningful consolidated statements. NetSuite’s consolidation engine uses 3 translation rate types to accomplish this conversion: “Current”, “Historical”, and “Average” (if this is unfamiliar, please see NetSuite Support article 7734 and my earlier article first). These “types” are defined at the account level. The specific, actual rates used for each “type” are defined in tables that are unique to each Subsidiary/Parent pair, for each accounting period. In other words, if you want to know what translation rate will be used to convert a particular GL account balance, you would need to see what “Rate type” is set on the Account record, then navigate to Lists > Accounting > Consolidated Exchange Rates and then select a Period, and a “From” and “To” Subsidiary. You will then see 3 rates, one for each type mentioned above.
The Challenge
Back to our story: We were presented with trial balances in both original base currencies and in USD amounts (the corporate parent entity currency). The Asset and Liability amounts had been converted using a uniform rate, as could be expected. Typically (most) Balance Sheet accounts are translated using a “current” or “spot” rate, applied to the balance as of the period end. However, Equity and Income Statements accounts balances expressed in the foreign base currencies were not uniformly translated to USD with one rate. Rather, each account’s balance was expressed in USD using a different exchange rate. (This was not surprising, given the philosophy behind the translation types and account types, but that’s beyond the scope of this article.) This presented a difficulty – NetSuite only allows ONE rate to be used for all Income Statements accounts, and likewise one rate for all Equity accounts, yet we essentially needed a different rate for each individual account to get both the base currency amounts and USD consolidated amounts correct. See illustrated example below for an Australian Dollar subsidiary of a USD parent entity. Note that the subsidiary’s base currency is Australian Dollars:
NetSuite’s Currency Translation Engine
Can’t you just “plug” the amounts?
Clients are often confused when I explain problems with Consolidated Currency amounts, since other Accounting platforms provide the ability to simply adjust the amounts expressed in the parent’s base currency. The reason this cannot be done in NetSuite can be illustrated by the below diagram:
Note that although we have full control over the transactions that hit the Subsidiary’s GL, they must be expressed initially as AUD (in the diagram’s example), and they then go through consolidation to hit the Parent entity’s reporting. We cannot be selective about the translation rates for individual accounts. The most we can do is set the rates for the group of accounts that share the same consolidated rate type.
The Solution: Control over Currency Translation
Two important considerations
The solution to this problem required some unconventional thinking: Instead of bringing in the AUD base currency amounts and trying to work with the rates to get the consolidated amounts (which is actually impossible mathematically, since the accounts used multiple rates), we needed to work backwards from the USD-translated amounts (since we have the least control over these) and then work out how to bring in the AUD base currency amounts to yield the USD balances yet keep those AUD balances correct. One more important discovery about NetSuite is that we can set consolidation rates to near zero, thus avoiding any translation impact (more about that below). Here are the steps to solve:
Start with the amounts needed in the parent entity’s currency (in our case, USD amounts). These amounts will be our anchor, since we have the least direct control over these amounts.
Next, calculate the overall rates for Historical and Average types. Essentially, this will be a weighted average across all accounts that use each of the two types. We need to do this so that the Trial Balance JE will balance out (beyond the scope of this article, but you’ll need to trust me for now – or try yourself!).
Calculate backwards using the derived rates to get to the base currency amounts that will yield the desired parent entity’s currency amounts. We know these are “wrong” in the base currency, but we also know that the consolidated amounts will be correct, based on 1. and 2. Bring in these amounts in the period needed.
Set the average and historical consolidation rates in a prior period to near zero (NetSuite won’t let us do zero, but it allows up to 12 decimal places).
Calculate the delta between the base currency amounts in step 3. and the actual correct base currency amounts per account. Bring in a second JE in the prior period with these delta amounts.
Viola! The parent entity balances will still be correct, since steps 4 and 5 guarantee zero translation impact, and the base currency amounts will be correct as well, due to the combination of the entries from the 2 periods. See below illustration:
Conclusion
It was extremely satisfying to be able to find a solution for a problem that I had encountered several times in varying forms. I’ve learned a lot about creative problem solving from Marty Zigman and from the talented team at Prolecto. If you are struggling with getting NetSuite to align with your business or accounting needs, please reach out!
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.
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:
Learn how to easily book adjustments and get the accounting right for online payment providers
Amazon Pay transactions
Amazon Pay is a popular payment capture service (a.k.a “payment gateway”) that collects funds from customers, deducts fees, and transfers a net balance to businesses that use its service. Booking the fees and transfers seamlessly is a common challenge for businesses using NetSuite. This article will demonstrate good accounting practices for booking the transactions, and also how a custom import tool created by Prolecto Resources can get the transactions in NetSuite automatically. Be sure to see the video posted at the end of the article to get the full sense of what is possible.
Accounting: Use a Clearing Account
Using a Clearing Account for the currency flows is a good practice for booking the transactions that come through payment gateways like Amazon Pay. This account can be looked at as a pseudo-bank account – a GL Account that is set up with an account type of “Bank” in NetSuite, but that is not a true bank account in the traditional sense. Then, create a Payment Method in NetSuite that should be associated with this account. When setting up an integration for Customer Deposits that come through the website using the Amazon Pay gateway, have the Customer Deposits use this new Payment Method, so that the customer payments at “gross” (prior to the fee adjustments) do not land directly in the true bank account GL, rather, they accumulate in the Clearing account. Use the same clearing account to book the fees and adjustments. It may be desirable to use custom transaction types for the fees, so that they are clearly distinguishable from other transactions. A Bank Transfer can be used to move the net amounts from the clearing account into the GL Account that represents the real bank account that Amazon transfers the funds into. The below screenshot illustrates the transaction flows. In this model, we use a custom transaction type.
Custom Importing Tool
The Amazon Pay gateway (similar to many other gateways) provides a CSV download of all the transactions, fees, and transfers with unique keys. Marty Zigman and the team at Prolecto have developed a “Record Import / Export Manager” tool, or RIEM for short, which allows us to create custom mapping and automate more than one transaction type all from the same file. All the user needs to do is upload a file to a designated folder in the NetSuite File Cabinet, and the tool does the rest! Several Map/Reduce scripts run in background to parse the data based on pre-defined mapping and the transactions are entered into NetSuite effortlessly. Please see the video below for an overview of how this appears from a user’s perspective:
Extend the reach of the import tool
You may notice that in this instance, we uploaded the file to the NetSuite File Cabinet. It is worth mentioning that we can also implement a system to avoid that step entirely using other means to bring the files in from various other sources. For example, we often implement the RIEM tool together with our sFTP file transfer tool, allowing an unrelated system or business (e.g. a Bank, Customer, or Vendor) to upload the file in a secured location in the cloud and allowing our sFTP folder to pick it up automatically and bring it into NetSuite. Another option is to use a built-in email plug in which allows NetSuite to consume files sent to a designated email address.
Conclusion
Getting the transaction entry right for credit card payments and processing fees is a common problem. It’s always gratifying to see how we can combine elements from our extensive library of custom solutions to produce real value and time-savings for our clients. If you would like to learn how we might be able to help you solve business problems in NetSuite using a tool like “Record Import Export Manager”, we’d be happy to hear from you!
Use an “Analytics Audit Trail” type Saved Search to find references within other Saved Searches
Introduction
Saved Searches are probably the most widely-used reporting tool in NetSuite. They are often used to stay in control of what is happening in the environment using email alerts and dashboards and the like. Sometimes, though, Saved Search criteria or results include references to specific text values that may be subject to change, posing a risk that the search can eventually become obsolete or misleading.
Use Case
For example, consider a custom field on a Purchase Order “Custom Status” with a drop down list of values. If the text in the drop-down is referenced in a search formula, and later someone changes the wording of the text, the saved search will no longer be accurate. This article will show how to prevent and detect record changes that will adversely impact existing Saved Searches.
Best Practices: Use Internal IDs
Before the solution, I would like to point out that it’s never good practice to reference specific text in a formula, if it can be avoided. It is much better to use the internal id as a reference. Here’s a handy tip: You can usually add “.id” in a formula to get the internal id of the list value or record value, even when the field/record is more than two joins away. For example, to reference a Location value of the Sales Order transaction on an Invoice search, you can use “{createdfrom.location.id} = 3” instead of something like “{createdfrom.location} = ‘Main Warehouse’.” (Hat tip to Sean G., my colleague at Prolecto who taught me that.)
Solution: Analytics Audit Trail Search
Now for the solution, in cases where the best practice advice described above was not followed and specific text was used in formulas: Use an Analytics Audit Trail Search! Navigate to Lists > Searches > Saved Searches > New and select “Analytics Audit Trail” as the search type. Below is sample criteria and results for a search I created where I was looking for formulas in Saved Searches that were referencing fields that contained a number from 1-7 with a hyphen afterwards:
Criteria Tab
Filter
Description
Formula
Formula (Numeric)
is 1
CASE WHEN {newvalue} LIKE ‘%1-%’ THEN 1 WHEN {newvalue} LIKE ‘%2-%’ THEN 1 WHEN {newvalue} LIKE ‘%3-%’ THEN 1 WHEN {newvalue} LIKE ‘%4-%’ THEN 1 WHEN {newvalue} LIKE ‘%5-%’ THEN 1 WHEN {newvalue} LIKE ‘%6-%’ THEN 1 WHEN {newvalue} LIKE ‘%7-%’ THEN 1 ELSE 0 END
Component Name
contains formula
Note: the “Component Name — contains formula” is telling the search to look for saved search “components” (= criteria, columns, or filters) that are of the “formula” type.
Note that the Formula in the Results tab creates hyperlinks to the saved searches that contain the text we are looking for. For more help in creating hyperlinks in saved searches, see this article by Marty Zigman.
Next Step: Stay ahead of the curve
If there are custom lists or other records in the environment that are likely to be referenced in Saved Searches, it also can be valuable to use the above saved search as a link in a second saved search which can drive an email alert to warn a user about the change and provide an actionable list. For example, if you know that the location names are often referenced in Saved Searches, you can set up an alert every time a Location name changes with a list of other saved searches that are impacted.
Conclusion
The Analytics Audit Trail Search is a really handy resource to stay on top of changes that affect existing reports. If you would like to learn more about this feature or would like creative solutions for NetSuite problems, I’d be happy to hear from you!
Learn how to use SuiteQL to achieve advanced reporting beyond the grasp of Saved Searches
Introduction
A recent client gave us a challenge: is it possible to create a report that shows what the average cost of Kit items were as of a given point in time? As will be explained below, this was not a simple request, and the data was not reachable using Saved Search functionality. Instead, we used a combination of the recently accessible “SuiteQL” SQL query ability in NetSuite and custom reporting to solve the request. If you would like to see how to expand your reporting toolkit using these tools, read on!
“Kit” Items
Kit items in NetSuite are essentially a group of other items that can easily be sold together on a Sales Order, without the need to formally “assemble” the components into an Assembly. Many clients that sell packages or bundles of different products prefer to use Kits rather than Assembly Items, since unlike Assembly Items, with Kits, there is no need to maintain work orders, bills of material, and the like. The component items remain as separate items in inventory and are available to be sold on their own. For this reason, a “Kit” does not really have a cost in NetSuite. It can almost be thought of as a bill of materials without any actual assembly taking place until the “Kit” is sold – at that point, NS consumes the component items based on the quantity of each component in the Kit and the quantity of Kits sold.
Given the above, when we talk the “cost” of a Kit, what we really are after is the cost of all the component parts multiplied by the number of each component within the Kit. We essentially want to artificially construct the Kit and project what the cost would be if it were sold at a given point in time.
Understanding the Table Structure
Before we figure out how to solve the problem, we need to map out where the data lives. I like to use Excel to create sample tables to visualize the work that NetSuite will need to do to present the data in the desired output. The sample tables that I create in Excel do not necessarily need to line up perfectly with NetSuite’s tables at this point; I actually find it more efficient to initially create tables based on my general assumptions. I then can think about my “attack plan” – where my main report data lives and what elements I can bring in using joins. Once I have a general idea of how to build a search or query, I then get the specific tables using NetSuite tools such as the Analytics Browser or the Record Catalogue, and then can think about whether a Saved Search can get to the data, or I need to use a Query Tool (more about the Query Tool below…).
Kit Avg Cost Tables
In our example, we need to bring together the following tables:
The “transaction” table
The “transactionLine” table
The “transactionAccountingLine” table
The “account” table
The “item” table
The “itemMember” table
Looking at the above, it quickly became clear that Saved Search would not be possible, since if I would start from a transaction search I would not be able to hop over to the item table and from there to the “item member” table to find the quantities and parent kits. Starting from an item would also not be possible, since it would require starting from the Kit items, joining to another table to get the components and their quantities within each kit, and then a third hop to the transactions of the component items.
Prolecto SuiteQL Query Tool
Thankfully, we have a couple of new tools at our disposal that are in many respects much more powerful than Saved Searches. These are the Prolecto SuiteQL Query Tool (free of charge to our clients as part of the Prolecto Utilities Bundle) and more recently, the Prolecto Query Renderer (see Marty Zigman’s article here). These tools build upon the “N/query module” that NetSuite has made available in 2019 to provide the ability to reach virtually any data in the database. In my opinion, these tools and others like them that are emerging in the marketplace may eventually replace Saved Searches completely.
After much trial and error, I was able to get accurate as-of date reporting, showing the Average Cost of Kit items. Here is the main Query used to create the report:
SELECT
type
,internal_id
,kit
, SUM(avg__kit_cost*comp_qty) as kit_avg_cost
FROM
( SELECT
BUILTIN.DF(im.parentitem) as kit
, BUILTIN.DF(i.itemtype) as type
,i.id as internal_id
, BUILTIN.DF(im.item) as component
, MAX(t.trandate)
, AVG(im.quantity) as comp_qty
, SUM(tl.quantity) as trx_qty
, SUM(tal.amount) as trx_amt
,(SUM(tal.amount)/NULLIF(SUM(tl.quantity),0)) as avg__kit_cost
FROM
itemMember im
INNER JOIN transactionline tl ON tl.item = im.item
INNER JOIN transactionaccountingline tal ON tl.id = tal.transactionline AND tl.transaction = tal.transaction
INNER JOIN transaction t ON t.id = tl.transaction AND t.id = tal.transaction
INNER JOIN account a ON tal.account=a.id
INNER JOIN item i on im.parentitem = i.id
WHERE
BUILTIN.DF(i.itemtype) = 'Kit/Package'
AND
a.sspecacct='InvtAsset'
AND
t.posting = 'T'
GROUP BY
BUILTIN.DF(im.parentitem),BUILTIN.DF(i.itemtype), i.id, BUILTIN.DF(im.item)
)
GROUP BY
type, internal_id, kit
In the end, due to the data volume in this particular client’s account, the query could not run quickly enough and would frequently time out. I then worked with Boban D., my senior colleague at Prolecto who created a custom record structure to hold monthly results, allowing the query to run smaller chunks of data at a time. That solution deserves an article in its own right, though.
Conclusion
I hope this article helped demonstrate the power of using tools that are possible using the SuiteQL methods, and more importantly, the strength of being part of a very talented group of NetSuite consultants. If you would like to learn more about how our creative custom tools can help you break out of the limits of native reporting, we’d be happy to hear from you!
Conversations with Meir Bulman 