This article is the first in a two-part series that explores different ways to auto-assign subsidiaries to vendors in a NetSuite OneWorld environment. This series challenges NetSuite business analysts and developers to explore more efficient, innovative solutions to common problems.
Table of Contents
Kim approaches Larry with a request: Asoville has expanded significantly, adding subsidiaries worldwide. NetSuite’s OneWorld allows them to manage their entire business in one instance. They activated the multi-subsidiary vendor feature to streamline accounts payable, but the AP team complains that vendors are often not set up with the correct subsidiaries, causing delays during bill processing. They need a way to auto-assign subsidiaries to vendors.
Kim formulates the problem as follows: The project goal is to meet the following requirements.
Requirements
- Automatically assign all active subsidiaries when a vendor is created or reactivated.
- Automatically assign any new or reactivated subsidiary to all active vendors.
- Perform a one-time mass update of all existing vendors.
As a savvy business analyst, Kim recognizes that a scripted solution is necessary. Hence, he turns to Larry, a seasoned NetSuite developer. Kim’s research led him to Mercedes Lerena’s 2021 article titled “NetSuite: Auto-add Subsidiaries to Vendors with SuiteScript,” which seems like an ideal match. If Larry follow the steps outlined there, Kim is confident that the project will be successful.
Problem Analysis
The functional requirements are straightforward, but the difference between a good solution and an excellent one often lies in the non-functional (or quality) requirements. Let’s consider some key factors:
Scalability: Large OneWorld accounts can have thousands of vendors and numerous subsidiaries. Our solution must handle high volumes without hitting governance limits.
User-Experience: Should updates occur in real-time, or is a background process sufficient?
Furthermore, there are a few nuances that relate to how NetSuite works that were not explicit in the requirements.
Elimination Subsidiaries: These special-purpose subsidiaries, used to eliminate intercompany transactions at a consolidated level, cannot be linked to vendors and must be excluded.
Special Category Vendors: Certain vendor categories, such as tax agencies, can only be associated with one subsidiary and must be excluded. Furthermore, as described in my article “Improved Methods for Identifying System Users“, there is a system-generated “-Accountant-” vendor which will show up in search/query results but cannot be modified.
Solution 1: Conventional Approach – Auto-Assign Subsidiaries to Vendors Using Multiple Scripts
The diagram below illustrates the typical pattern to auto-assign subsidiaries to vendors using SuiteScript.
Note: Lerena’s article referenced above offers a similar approach albeit with a different implementation – we do not need to load the vendor in dynamic mode, we can be more efficient using SuiteQL instead of saved searches, etc. Furthermore, it might be hard to conceptualize the pattern in one’s mind without a visual.
We won’t provide a complete implementation here, as Part 2 will present a more elegant solution. However, here are some snippets that represent the key building blocks for an implementation of this approach.
Query to Find All Subsidiaries Not Assigned to Vendor
The following query can be run in the User Event script deployed to the vendor record to find any missing subsidiaries. Notice that we’re joining a vendorsubsidiaryrelationship table. That is an interesting table which we will cover in more detail in Part 2 of this series.
SELECT
s.id
FROM
subsidiary s
WHERE
s.iselimination = 'F'
AND s.isinactive = 'F'
AND NOT EXISTS (
SELECT
1
FROM
vendorsubsidiaryrelationship r
WHERE
r.entity = ?
AND s.id = r.subsidiary
)
Query to Find All Vendors Not Associated with a Subsidiary
The following query can be run in the User Event script deployed to the subsidiary record to find all unassociated target vendors. Notice how we exclude special-purpose vendors as discussed earlier.
SELECT
v.id
FROM
vendor v
WHERE
v.isinactive = 'F'
AND v.id > 0 /* exclude system-generated records e.g. "-Accountant-" */
AND (
v.category IS NULL
OR BUILTIN.DF(v.category) <> 'Tax agency'
)
AND NOT EXISTS (
SELECT
1
FROM
vendorsubsidiaryrelationship r
WHERE
r.entity = v.id
AND r.subsidiary = ?
)
Note: For the mass update of existing vendors, we could temporarily allow the script on the vendor record to execute unconditionally in edit mode and use a dummy mass update action or scheduled workflow to trigger the script without altering data i.e. just to “touch” eligible vendors.
Key Observations
The main takeaways from the conventional solution outlined above are:
- It requires multiple scripts – user event (UE) scripts deployed to the subsidiary and vendor records, and a background processor map/reduce script to handle high volume gracefully. Note: While one may choose to implement the UEs in the same JavaScript file; it conceptually remains two script deployments targeting the vendor and subsidiary records, respectively
- In general, more scripting introduces complexity, requiring additional error handling.
- Extra business logic is needed to distinguish between vendor/subsidiary reactivation and other edit actions. While this is not difficult, the code will be more involved to handle these edge cases.
- Rather than always offloading the work to the background map/reduce script, a developer might contemplate a more intelligent solution where, if the number of vendors is low, the vendor update is executed directly from the user event script.
The above solution works but involves more scripting than necessary. Stay tuned for Part 2, where we explore a simpler solution. In the meantime, share your thoughts on other possible approaches.
ChatGPT’s Take
I posed a simpler version of the problem to ChatGPT:
How can I implement the following in NetSuite using scripting:
- Upon saving any vendor associate it with all active subsidiaries
- Upon adding a subsidiary, associate it with all active vendors
ChatGPT suggested deploying User Event scripts to the vendor and subsidiary records, which aligns with the solution above. However, it missed key aspects:
- It missed the mark on the vendor-subsidiary relationship model and fabricated a non-existent ‘subsidiaries’ field which we would presumably set to assign subsidiaries to a vendor. In fairness, it added a comment indicating that it was assuming such a field exists.
- It overlooked scalability considerations. The UE deployed to the subsidiary record would fail on a handful of vendors due to governance limits.
Nevertheless, in Part 2, I will highlight how ChatGPT helped craft my refined solution. Asking the right questions and making grounded assessments of the responses provided by Large Language Models (LLMs) like ChatGPT is the key to getting the most value from them!
Final Thoughts
The beauty of working with a rich ERP system like NetSuite is that nearly every problem has multiple solutions. The art of the craft is finding the optimal solution given the requirements and constraints. While anyone (even ChatGPT) can propose solutions, experts help you find the right solution for your business needs.
NetSuite Insights is proud to partner with Prolecto Resources Inc. – the unrivaled #1 NetSuite Systems Integrator and thought leader in the space! Learn more about how Prolecto can supercharge your NetSuite experience and deliver the best return on your NetSuite investment.
