This article is relevant if you are a NetSuite power user and you are seeking to automatically update summary values on records.
Background
One of our many clients has a strong sales culture. The client needs some good summary information to help them think about their clients in different categories. Depending on the last transaction activity and the revenue produced, our client will design different promotion campaigns. To help them with information requirements, they need easy-to-reach aggregate data to act on clients categorically.
The traditional approach to gaining access to summary data in NetSuite is to use Saved Search. However, as many in the community know, Saved Search has limitations in its data reach. Yes, SuiteAnalystics Workbooks are promising; they still, at the time of this writing, do not match the simplicity and functionality of NetSuite’s tried-and-true Saved Search.
With these NetSuite Saved Search limitations, in 2020, I wrote the article, Learn NetSuite Two Dot Saved Search Notation for Distant Joins, to help others understand how to get more reach beyond a single join to related tables. The good news is the SuiteQL capacities (the technology under SuiteAnalystics Workbooks) effectively solve the traditional data reach challenge. Yet, SuiteQL is far from being easily within the reach of most NetSuite users. One approach, offered in my 2021 article, Render NetSuite SQL Queries Like Saved Searches, solves the data reach challenge by bridging SuiteQL queries and saved search capacities. This tool is free of license charge to all Prolecto Resources clients.
However, another approach that has long been used in the NetSuite community is to create custom fields to hold (summary) information, effectively giving extended reach to the beloved Saved Search tool. So the question becomes, how do you get the data into the custom fields? NetSuite offers up the “Creating Custom Fields with Values Derived from Summary Search Results” pattern. And it works reasonably well when you load a NetSuite form. But it suffers greatly from a) offering nothing when you want to reach the custom field data from Saved Search and b) impeding record load performance if you define too many custom summary fields.
We need a powerful approach to populate the custom fields so that we can extend the use of Saved Searches.
Solving for Updating Hard-to-Reach NetSuite Fields without Scripting
I will demonstrate a pattern that can be used to update fields on custom records. Using our License Free tools, no scripting is necessary. The solution involves three major steps:
- Define Custom Fields: add conventional custom fields to the record; do not use Netsuite Summary Search results. Be sure to mark the field with the data type. There is nothing special here, so I won’t elaborate. Click the related image to see how our client’s fields were defined.
- Develop SuiteQL (SQL): create a SQL statement that will aggregate the information we are interested in. See below for more information on the pattern.
- Update Fields: based on the results of the SQL aggregate, update the records using a ready-to-go map/reduce scheduled script.
Develop Specialized SuiteQL Statement
The real power comes in the way of crafting an aggregate SQL statement that will give us the data we need. The first set of work is to create a SQL statement that produces the aggregate. Then, by using a Having clause that compares the results currently stored in each summary field (from the last time they were updated) with the amounts being returned from the query, we can find the aggregate amounts that have changed, which signal which records need to be updated. Below is the actual query to be used as inspiration for other administrators crafting similar queries.
SELECT t.entity id,
'customer' recordtype,
Max(CASE
WHEN t.type IN ( 'CustInvc', 'CashSale' )
AND Nvl(t.custbody_pri_cd_invoice_type, 0) != 2 THEN
t.trandate
WHEN t.type = 'SalesOrd'
AND t.custbody_thm_legacy_transaction = 'T' THEN t.trandate
END) custentity_thm_csd_invoice_date,
Max(CASE t.type
WHEN 'SalesOrd' THEN t.trandate
END) custentity_thm_csd_order_date,
Max(CASE
WHEN t.type IN ( 'CustCred', 'CashRfnd' ) THEN t.trandate
END) custentity_thm_csd_credit_date,
Sum(CASE
WHEN t.type IN ( 'CustInvc', 'CashSale' )
AND Nvl(t.custbody_pri_cd_invoice_type, 0) != 2 THEN
t.foreigntotal
WHEN t.type = 'SalesOrd'
AND t.custbody_thm_legacy_transaction = 'T' THEN
t.foreigntotal
END) custentity_thm_csd_revenue_to_date,
Sum(CASE
WHEN t.type IN ( 'CustCred', 'CashRfnd' ) THEN t.foreigntotal
END) custentity_thm_csd_credits_to_date,
Sum(CASE t.type
WHEN 'SalesOrd' THEN t.foreigntotal
END) custentity_thm_csd_order_value_to_date,
Count(CASE t.type
WHEN 'SalesOrd' THEN t.foreigntotal
END) custentity_thm_csd_order_count,
FROM TRANSACTION t
JOIN customer c ON t.entity = c.id
GROUP BY t.entity
-- below is the work to find the records that have changed
HAVING Sum(CASE
WHEN t.type IN ( 'CustInvc', 'CashSale' )
AND Nvl(t.custbody_pri_cd_invoice_type, 0) != 2 THEN
t.foreigntotal
WHEN t.type = 'SalesOrd'
AND t.custbody_thm_legacy_transaction = 'T' THEN
t.foreigntotal
END) != Nvl(Max(c.custentity_thm_csd_revenue_to_date), 0)
OR Sum(CASE
WHEN t.type IN ( 'CustCred', 'CashRfnd' ) THEN t.foreigntotal
END) != Nvl(Max(c.custentity_thm_csd_credits_to_date), 0)
OR Sum(CASE t.type
WHEN 'SalesOrd' THEN t.foreigntotal
END) != Nvl(Max(custentity_thm_csd_order_value_to_date), 0)
In 2021, I wrote an article on a NetSuite SQL Query Tool to help administrators do their SQL query work. The article also references other SQL tools offered by community members.
Update Fields from the SQL Query
Now that we have the SQL results, how do we get that data into the custom fields? Please reference my 2022 article, Learn How To Update NetSuite using SQL. This tool is available to all Prolecto clients license free.
We were astonished by the performance of the SQL Query in a good size database. Our client has hundreds of thousands of customers (with respective multiple transactions), and the system would calculate the results of the above query in a few seconds. Thus, we set the map/reduce scheduled script to run every 15 minutes as the impact on the NetSuite system was negligible.
Click the image to see how to supply the query to the script as a parameter. The first time we updated the database, it took some time to chug through the entire customer record list to produce the updates. But thereafter, it ran happily in the background, effectively unnoticed. I would like to thank Boban D., a Senior Technical Analyst on our staff, for this pattern.
Empower NetSuite Administrators with NetSuite Leadership
NetSuite Administrators are challenged to listen to business requirements and translate them into working user solutions. This article is a good illustration of real-life situations that come forth. Users that know Saved Search sometimes find they can become stuck. However, the power of the NetSuite extensible platform effectively means we are never really stuck if we know how to wield the given capacities.
The pattern I demonstrated leverages the previous work we have done for our clients. Like any committed actor, we keep working to improve our competency in our Systems Integration Practice. Our tools are reflections of our accomplishments. Our ethics are to assist our clients with our best know-how. We are often engaged by clients who have Netsuite Administrators that need extra intellectual horsepower and a set of power tools to assist.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to tackle your NetSuite data reach challenge, let’s 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
