Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 1

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.

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

1. Automatically assign all active subsidiaries when a vendor is created or reactivated.
2. Automatically assign any new or reactivated subsidiary to all active vendors.
3. 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. Lastly, attempting to add the “Represents Subsidiary” on an intercompany vendor will result in an error: “The system cannot process changes of the entity-subsidiary relationships because of the following: Represents Subsidiary must be different than Subsidiary”. Thus, this scenario needs to be accounted for.

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:

1. 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.
2. Performance: Because we need the internal ID of the newly created vendor/subsidiary to perform the check, the code must be run in the afterSubmit event. This is costly as the vendor has to be reloaded from the database a second time to commit the subsidiary changes.
3. In general, more scripting introduces complexity, requiring additional error handling.
4. 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.
5. Missed records: What if an error prevents the update from happening e.g. a bug in the code? There will be no easy way to recover. You’d probably need a status field to track the error and have to wait until the vendor is touched again for the logic to fire. This might result in confusion for users or dumb instructions like “If you do not see all the subsidiaries, just load and save the record without making any changes”.
6. 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:

1. Upon saving any vendor associate it with all active subsidiaries
2. 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:

1. 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.
2. It overlooked scalability considerations. The UE deployed to the subsidiary record would fail on a handful of vendors due to governance limits.

Nevertheless, in Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 2, I 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.

Other Posts You Might Find Interesting

1. Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 2
2. How to Change a NetSuite Vendor’s (or Customer’s) Primary Subsidiary After Booking Transactions
3. Improved Method for Identifying NetSuite System Users
4. The SuiteScript Developer’s Guide on NetSuite Subrecords: Part 1