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

This article is part two of a series that explores different ways to auto-assign subsidiaries to vendors in a NetSuite OneWorld environment. This series encourages NetSuite business analysts and developers to discover more efficient solutions to common challenges. If you missed Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 1, read it first for context.

Let’s check in with Larry at Asoville. Implementing the solution presented in Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 1 went smoothly. In fact, two days ago, he deployed it in the Sandbox and Kim is currently leading User Acceptance Testing (UAT) with the AP team. So far, so good. But something interesting happened… Larry had one of those “aha!” moments in the shower this morning. When he got to his (virtual) office, he pinged Kim: “How’s it going, Kim? Can you hold off with UAT on that vendor subsidiary solution I developed? I think there’s an easier way to solve this problem and I’d like to explore it this morning. If it works, I’d prefer we deploy that solution as it will be easier overall.” Kim accepted.

As a mature developer, Larry is constantly seeking ways to improve his skills and produce better output with less effort. Kim is curious to know what Larry is up to this time.

Larry is definitely up to something. There is indeed an alternative that reduces the amount and complexity of scripting required and still meets all the requirements. Let’s explore his findings.

TL;DR

• By moving the process to background (map/reduce) and leveraging cross-joins to identify which records need updates, this approach achieves our objectives with fewer scripts and less complexity.
• SuiteQL enables solutions previously difficult or impossible to implement. But, it often requires a mindset shift in problem-solving.

Before presenting the new solution, let’s briefly review NetSuite’s vendor-subsidiary model, as understanding it is essential.

Understanding NetSuite’s Vendor-Subsidiary Relationship Model

The Subsidiary sublist under a vendor record looks like any other sublist. However, the database model, at least the way it is exposed, is slightly different. Vendor-subsidiary links are managed via a hidden vendor-subsidiary record. Though not accessible through the UI, it can be interacted with via script, search, and query.

Contrast this with the transaction-item relationship, for example. A transaction can have multiple lines in the item sublist. However, there is no linkage record exposed to us to capture this relationship. (It might very well be that the underlying database is modeled using a transaction-item relationship table but we have no access to it.)

This structure enables two methods for assigning subsidiaries to a vendor.

Linking Vendors and Subsidiaries via a Sublist Operation

This conventional approach populates the sublist. Here’s a sample user event script to add all subsidiaries to a new vendor upon saving it:

const afterSubmit = (scriptContext) => {
    const fn = `afterSubmit.${scriptContext.type}.${scriptContext.newRecord.id}`;
    
    if (['create'].includes(scriptContext.type)) {
        // Check if we're missing any sub and add them.
        if (scriptContext.newRecord.getValue("category") === "3" /*Tax agency*/) {
            log.debug(fn, `[IGNORED] Tax agency vendors may not have only one subsidiary`)
            return;
        }

        const q = `
            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)
        `;
        
        const queryResults = query.runSuiteQL({
            query: q, 
            params: [scriptContext.newRecord.id]
        }).asMappedResults();

        log.debug(fn, `Query results: ${JSON.stringify(queryResults)}`);

        if (queryResults.length > 0) {
            let rec = record.load({
                type: scriptContext.newRecord.type,
                id: scriptContext.newRecord.id,
                isDynamic: false // Also works in dynamic mode
            });

            queryResults.forEach(result => {
                if (rec.getValue('representingsubsidiary') == result.id) {
                    log.debug(fn, `Ignored representing subsidiary as NetSuite will reject the linkage with error: 
                    "The system cannot process changes of the entity-subsidiary relationships because of the following: Represents Subsidiary must be different than Subsidiary"`);
                    return;
                }

                rec.insertLine({
                    sublistId: 'submachine',
                    line: 0
                });

                rec.setSublistValue({
                    sublistId: 'submachine',
                    fieldId: 'entity',
                    line: 0,
                    value: scriptContext.newRecord.id
                });

                rec.setSublistValue({
                    sublistId: 'submachine',
                    fieldId: 'subsidiary',
                    line: 0,
                    value: result.id
                });
            });

            rec.save({ ignoreMandatoryFields: true });

            log.debug(fn, 'Successfully added all missing subsidiaries to the vendor');
        }
    }
}

Observations:

• [Lines 7 – 15] SuiteQL allows us to easily find missing subsidiaries by querying the vendorsubsidiaryrelationship table for missing entries.
• [Lines 26 – 51] Results are updated in the subsidiaries sublist via regular operations. Amusingly, the sublist is called submachine.
• Upon saving the vendor, NetSuite then creates the Vendor Subsidiary Relationship records in the background.

Linking Vendors and Subsidiaries via the Vendor-Subsidiary Relationship Record

Since the underlying vendorsubsidiaryrelationship is accessible via scripting, we can alternatively create entries in that table to achieve the linkage. The following snippet illustrates that pattern.

const {entity, subsidiary} = getDataFromSomeWhere()

const id = record.create({
    type: record.Type.VENDOR_SUBSIDIARY_RELATIONSHIP,
})
.setValue('entity', entity)
.setValue('subsidiary', subsidiary)
.save({ ignoreMandatoryFields: true })

Observations:

• The governance cost for the sublist approach is constant (O(1)), while direct record creation is proportional to the number of subsidiaries (O(n)), making it more resource-intensive. Thus, the latter approach is not recommended for execution in a user-event script. However, it very useful in other scenarios as discussed next.

Solution 2: Auto-Assign Subsidiaries to Vendors Using Background Processing Facilitated By Cross-Join

The solution proposed in Part 1 involved two user event scripts and a map/reduce script, achieving the goal but with drawbacks:

1. User Experience and Performance: User event scripts execute directly on records which means that they can slow down record load/save operations and affect user experience. In this case, the action must be performed in the afterSubmit event of the vendor record, leading to an additional record load/save to persist the new subsidiaries (via a sublist operation as described above).
2. Additional Logic and Error Handling: We’d require logic to limit the execution to the applicable scenarios only (creation or activation of a vendor/subsidiary) and to handle edge cases (e.g. if the script fails for whatever reason, how do we retrigger the process without requiring the user to do a blind save?)
3. Script Maintenance: We will have three scripts to maintain which is not an issue by itself except if we can achieve more with less.

In contrast, this new approach moves everything to background processing, using NetSuite’s map/reduce infrastructure for heavy lifting.

The revised map/reduce script is similar to the one proposed in Part 1. However, there is a crucial difference: It is not initiated by changes to a single vendor or subsidiary. Thus, we need to find all vendors missing any subsidiary and process them. Gladly, SuiteQL gives us the necessary tools to produce such a query using a cross-join. Here’s my query:

SELECT 
	v.id vendor, 
	s.id subsidiary,
	v.entityid vendor_name,
	s.name subsidiary_name
FROM
	vendor v
CROSS JOIN
	subsidiary s
WHERE
	v.isinactive = 'F'
	AND s.iselimination = 'F'
	AND s.isinactive = 'F'
	AND v.id > 0 /* exclude system-generated vendors */
	AND (v.category IS NULL OR (SELECT istaxagency FROM vendorcategory WHERE id = v.category) = 'F') /* Tax agency vendors cannot be multi-subsidiary */
	AND (v.representingsubsidiary IS NULL OR v.representingsubsidiary != s.id) /* Interco vendors cannot be linked to their representing subsidiary */
	AND NOT EXISTS 
		(SELECT 1 FROM vendorsubsidiaryrelationship r WHERE r.entity = v.id AND s.id = r.subsidiary)

Let’s walk through the query:

• [Lines 6-9] Why a cross-join? Cross-joins allow us combine tables that do not have a direct relationship in the database. In our case, the vendor and subsidiary tables do not have a direct relationship but we need to combine them to find the gaps. If you are not familiar with this SQL feature, there are several articles online that explain it. I found this one helpful to visualize the concept using day-to-day items.
• [Lines 11-16] We filter out inactive records and handle various special vendor categories identified in Part 1.
• [Lines 17-18] This is the crux of the query. Our cross-join gave us a full data set from both tables including “holes” i.e. vendors that are missing a relationship with a subsidiary. Thus, this part of the query helps us find those holes.

Let’s visualize this query using a simple example. Suppose we have Vendor A and Subsidiaries 1, 2, and 3. Let’s assume that Vendor A is currently linked to only Subsidiaries 1 and 3. After the cross-join, we will have something like this:

Thus, the SQL statement NOT EXISTS (SELECT 1 FROM vendorsubsidiaryrelationship r WHERE r.entity = v.id AND s.id = r.subsidiary) will return only row 2. I hope this makes sense.

With the query in place, the final script is surprisingly straightforward:

define(['N/record'],
    (record) => {
        const getInputData = (inputContext) => {
            const q = <insert_query_shown_above>

            // Best practice: return query definition instead of running it
            // as NetSuite internally handles large results sets better.
            return {
                type: 'suiteql',
                query: q
            }
        }

        const map = (mapContext) => {
            const fn = `map[${mapContext.key}]`;
            log.debug(fn, `In - Key ${mapContext.key} | Value: ${mapContext.value}`);

            const data = JSON.parse(mapContext.value).values;
            try {
                const id = record.create({
                    type: record.Type.VENDOR_SUBSIDIARY_RELATIONSHIP,
                })
                .setValue('entity', data[0])
                .setValue('subsidiary', data[1])
                .save({ ignoreMandatoryFields: true });
                log.debug('map', `Successfully created vendor subsidary relationship with ID ${id}`)
            } catch (e) {
                log.error(fn, e)
                throw new Error(`Failed to create vendor subsidiary relationship: ${mapContext.value}. Error: ${e}`);
            }
        }

        const summarize = (summaryContext) => {
            // TODO error handling which is really only for visibility as the script will retry any failed records the next time runs
        }

        return {getInputData, map, summarize}

    });

Observations

• Notice how concise the script is! One (powerful) query in the getInputData stage and one vendorsubsidiaryrelationship block in the map stage. I did not test this but it might actually be more performant to add all subsidiaries per vendor via a sublist operation in the map stage instead of adding each individual subsidiary separately. That would require modifying the query slightly to group results by vendor and perhaps use LISTAGG to get the subsidiary IDs.
• Because we are using a map/reduce script, this solution is implicitly scalable. No matter how many vendors need to be updated, we do not have to worry about running out of governance.
• We also don’t have to worry about edge cases and missed vendors/subsidiaries as each time the query runs, it finds all entries that need to be processed.
• Lastly, there is zero impact on user experience as the script runs completely in the background.

Granted, this approach will introduce a delay of 15 minutes or more before the changes are visible on the vendor records as that is the granularity of NetSuite’s script scheduler. However, given the nature of the operation, that should be acceptable in most cases. If not it is easy to adapt the solution for near-real time updates. I will leave this up to the interested reader for practice (this series has provided all the building blocks you need).

Another potential drawback of this solution is the cross-join. This query might be slow if there are lots of vendors and subsidiaries. But, since it runs in the background, the effect will be unnoticed.

NetSuite Wisdom: The best customization (workflow, script, etc.) is the one that never got created in the first place. Thus, avoid unnecessary customizations as there is a cost to every customization.

Secondly, when scripting always consider if an action really has to be done in real-time (i.e. client/user event script) or if background execution (e.g. map/reduce or scheduled script) will suffice.

Closing

ChatGPT Helped Me A Bit

I am fairly new to SQL. Thus, my understanding of what is possible is still quite limited. I was not familiar with cross-joins. However, once I started thinking about the problem in the manner presented above, I realized that if I could somehow combine the vendor and subsidiary tables, I’d have a breakthrough. Therefore, I formulated a fictitious database scenario for ChatGPT and asked for help. It led me to the notion of a cross-join which I then read up and implemented. It would have been harder for me to formulate this query and get the right results from a regular Google search.

With the Right Tools…

At Prolecto, we have developed several accelerator templates that augment and extend NetSuite’s capacities. One of the tools in our Prolecto Utilities bundle allows creating or updating records via SuiteQL query. Thus, with that tool, the scope of this solution would be reduced even further to simply producing the query and scheduling a deployment of the tool to perform the work. That means a business analyst could solve this problem without needing a developer. Now, that’s something!

Back at Asoville, “all is well that ends well” as the saying goes. Larry is very satisfied with his discovery. Every day he gets to learn something new is a very good day. Kim, who is a strong analyst and versed in SQL, gets it right away and takes a mental note. As much as he loves working with Larry, anything he can get done on his own is a plus.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!

Other Interesting Posts

1. Master How To Auto-Assign Subsidiaries to Vendors in NetSuite: Part 1
2. How to Change a NetSuite Vendor’s (or Customer’s) Primary Subsidiary After Booking Transactions
3. How To Easily Understand The Dispatcher Script Pattern
4. Learn to Query and Reuse Script Audience Configuration
5. Understand Line ID vs. Line Sequence Number in NetSuite Transactions
6. Learn To Write Custom List Code That Works In All NetSuite Environments (Revised)
7. Learn How to Generate Meaningful Amortization Journal Entry Memo Lines
8. Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines