This article is relevant if you are having trouble with NetSuite saved searches and reporting because of changes in your customer and project hierarchies.
Background
NetSuite is a highly relational database-driven ERP system. Because of its relational nature, it is essential to remember what can happen to our reporting when we make changes to master record structures.
In my 2021 article, Best Practice to Retire a NetSuite Item Definition, I discuss the reporting implications and suggest structural enhancements to maintain reporting when we retire or create substitute items. The article’s key innovation is to create a custom “reporting” field to act as a database key to rollup related transactions that now have different master structure representations from when the transactions were created.
More recently, one of our multi-subsidiary clients wanted to have a separate working relationship with our firm to provide NetSuite innovation and support. We had leveraged NetSuite parent/child relationships to create the following simple hierarchy before they requested the change:
- Modern Media (Parent Company)
- Modern Media: Tre Milano (Child Subsidiary)
- Modern Media: Banana Bros (Child Subsidiary)
Click the image to see the old and new customer hierarchy. Once you break the parent relationship, all the customer records are independent of each other.
Invoices on One Customer; Payments on Another Customer
Our historical invoices were stamped with the child customer reference “Modern Media: Tre Milano” or “Modern Media: Banana Bros.” Fundamentally, the invoices were stamped with the respective child customer internalid, and the parent reference to “Modern Media” is only held as a master structure; meaning, the transactions have no information about the master record “Modern Media”. This situation is not a problem.
However, if you activate the NetSuite “Consolidated Payments” feature, you can accept payments from the parent customer to be applied to child customers. With this popular feature activated, customer payments are stamped with the parent internalid versus the child customer internalid as the invoice. Because related transactions have different customer references, saved search and reporting trouble can begin.
Master Record Structure Change Challenge in Reporting Balance Sheet Saved Searches
In my 2014 article, Produce NetSuite Balance Sheet Amounts via Saved Search, I illustrate the technique to faithfully reproduce the balance sheet via Saved Search. The customer account receivable aging can be reproduced in a balance sheet-based saved search. In my 2020 article, Calculate NetSuite Days Sales Outstanding (DSO) with Saved Search, I discuss how to create a Days Sales Outstanding report which serves as the basis for an enhanced aging report. I subsequently wrote about how to also build a saved search aging that includes customer deposits in 2021, Get a NetSuite Accounts Receivable with Customer Deposit Aging.
These balance sheet-saved search reports assume that the customer records are static. To account for invoices in the child customer but the payments in the parent customer, we take advantage of a NetSuite native field called “Top Customer,” which allows us to roll up all records at the parent despite having different customer internalids.
This Top Parent reference is terrific — until you make changes to the customer hierarchy! In our case, we simply wanted to remove the reference to the parent reference from the child’s customer records. NetSuite allows you to do that as a natural requirement to shape evolving customer hierarchies. But once you remove that master record reference, you no longer have a shared key to roll up related transactions — effectively, the historical invoice and the payment transactions appear to be from unrelated customers.
Click on related images to better understand the situation.
NOTE: making changes to the customer hierarchy will not change payment transactions — but if you edit an existing parent payment, it will disconnect the previously saved applied child invoice records as they are no longer related. This is an essential concern for anyone that opens up previous periods and modifies transactions for any reason.
Avoid Hard Coding Saved Searches
One approach to solving the reporting challenge is to look for the parent/child situation and modify the problem report to use Saved Search Formula-based Case statements. While this approach may be expedient because it gets the reporting working again, it is hardly elegant. Other reports will need this “hard coded” logic; over time, more report challenge situations will present themselves. Instead, we want to leverage the power of the relational database so our reports can remain dynamic.
Implementing a Custom NetSuite Reporting Field
Similar to the item reporting reference discussion, we need to do the following to solve our challenge:
- Create a custom NetSuite Entity Reporting field: I call the field PRI (for Prolecto Resources Inc.) Reporting and I activate it to be used against customers and projects. Note that NetSuite projects are very close to sub-customers and participate in hierarchy logic.
- Update the Database: We need to get data into the new field to share a common key. A simple way to update the database is to use NetSuite’s CSV tools. I won’t elaborate on this as I assume readers understand NetSuite’s CSV Import tool.
- Keep the Custom Reporting Field in Sync: Keeping the custom NetSuite Reporting entity field in sync may be more of a challenge. Fortunately, we have invented some utilities that we give our clients free-of-license-charge that can take the result of a saved search and update the database whenever a customer or project record is edited and saved. See my 2021 article, Overcome NetSuite Summary Search Fields to Store Value. Click on the related image to see the saved search formula.
- Modify Saved Search Reports: Once the database is updated, it is straightforward to modify the references to the Top Parent Customer and reference the custom Reporting Field instead. Click on the related image to see the saved search definition.
The good news is that the reporting field can be used with other entities, such as vendors, contacts or partners, where the database situation demands it.
Work with Innovative NetSuite Professionals
I consider this custom field approach to solving reporting a relatively straightforward NetSuite innovation — collectively, with all the license-free tools we have built since 2008, our clients share that they enjoy working with our professional team. Our success is derived from listening carefully to client concerns and developing scalable and forward-thinking solutions.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. Perhaps you are a NetSuite power user or administrator and would like to join a professional team that values your background. Or you would like to connect with high-caliber talent as you address various NetSuite challenges. Reach out to me so we can have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
