Understanding SuiteScript 2. x Joins

NetSuite supports the concept of joins, i.e., accessing data from a linked record. Have you ever found yourself second-guessing what syntax to use to produce your desired results? I have! And, in this article, I’ll share some tips to help you get your joins right.

TL;DR

• NetSuite supports two join syntaxes: the “dot” notation of join.name and the more verbose notation of { "join": "field_on_current_record", "name": "field_on_joined_record" }
• The key to doing proper joins is to correctly interpret the keywords: “join” is always a field on the base record that you’re searching; “name” is a field on the joined record.
• When using the search.lookupFields function, only the dot notation is supported. See the summary at the end of this article for which syntax is supported where.

NetSuite Joins By Example

While the concept itself is straightforward, especially if you have any experience working with relational databases, there is something about the way joins are expressed in NetSuite that keeps getting me confused. This article is intended to help you (and my future self) better understand NetSuite’s join syntaxes.

In the previous section, I already explained the key to thinking about joins correctly and it is worth reiterating:

Let’s now look at a few examples to drive this point home and highlight some nuances. Our example scenario is that we are searching for purchase orders and want to get some information from the vendor associated with a purchase order. In this scenario:

• Base record: Purchase Order (technically, Transaction)
• Joined record: Vendor

Note that in the examples below, I use the require function ^[I]NetSuite (October 2, 2015). Require function. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/45054. [Accessed on: June 11, 2021] so that you’re able to run the examples directly in the console. Be sure to switch the define function ^[II]NetSuite (October 2, 2015). Define Function. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/43801. [Accessed on: June 11, 2021] if you’re working in a script file.

Example 1: Verbose Search

Get all purchase orders for vendors with a given category. As output, we want to see both the PO # and the Vendor name.

require(["N/search"], function (search) {
  var poSearchObj = search.create({
    type: search.Type.PURCHASE_ORDER,
    filters: [{
      name: 'mainline',
      operator: search.Operator.IS,
      values: 'T'	  
    },{
      join: 'vendor',
	  name: 'category',
      operator: search.Operator.ANYOF,
      values: 5
    }],
    columns: [
	  search.createColumn({
        name: 'tranid'
	  }),
      search.createColumn({
		join: 'vendor',
        name: 'companyname'
      })
    ]
  });
  
  poSearchObj.run().each(function(result){
     console.log('PO #: ' + result.getValue({name: 'tranid'}) + ' | '
		+ result.getValue({join: 'vendor', name: 'companyname'}));
     return true;
  });
});

The above example is as verbose as it can get; we’ll show a more compact syntax shortly.

Notice that in lines 9 and 19, the join is vendor. As per our convention, this refers to a field on the base record that we’re running our search on. To avoid confusing myself, I like to write the join line before the name line as opposed to most examples where the name is written first. (Remember: join.name is the winning formula.)

However, if you look at any PO in the NetSuite UI, the internal ID of the Vendor field is actually entity not vendor!

Finding a Join Field Name

In most cases, the field ID in the UI is the same as the join table name but, as illustrated by this example, that is not always the case! This is a common mistake when doing joins: Do not assume the join table name. Instead, go to the SuiteScript Records Browser^[III]NetSuite (June 15, 2018). The SuiteScript Records Browser. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/74610. [Accessed on: June 11, 2021], find the record in question, and see the name listed in the “Search Join” section.

For this example, when we look up the joins for the Transaction type (the base type from which Purchase Orders derive) in the records browser, we see that the Vendor join is called vendor not entity. Please note that the list of joins in the records browser might now always be complete. For instance, item is not listed as a join for the Transaction record though, in fact, it is a valid join as explained here.

Example 2: Search Using Dot Notation

The following search is a more compact equivalent of the search in the previous example. It applies the dot notation and other shorthand formats, yet produces the exact same results:

require(["N/search"], function (search) {
  var poSearchObj = search.create({
    type: search.Type.PURCHASE_ORDER,
    filters: [
	  ['mainline', search.Operator.IS, 'T'],
      'AND',
      ['vendor.category', search.Operator.ANYOF, 5]
    ],
    columns: [
	  'tranid',
      'vendor.companyname'
    ]
  });
  
  poSearchObj.run().each(function(result){
     console.log('PO #: ' + result.getValue('tranid') + ' | '
		+ result.getValue({'join': 'vendor', 'name': 'companyname'}));
     return true;
  });
});

Notice how we’ve removed a lot of boilerplate code by switching to filter expressions in the filters section instead of using search.createFilter(). Also, we’ve expressed our columns by name only instead of using search.createColumn(). This is much more readable (at least to me)! If you’re not familiar with filter expressions, I highly recommend this article ^[IV]Eric T. Grubaugh (July 3, 2017). Search Filters in SuiteScript 2.0. Available at: https://stoic.software/effective-suitescript/10-search-filters/ [Accessed on June 11 , 2021] by Eric T. Grubaugh on searching in SuiteScript 2.0 where, among others, he covers filter expressions.

Did you notice that although we were able to get away with the dot notation on lines 7 and 11, we were not able to use it on line 17 when accessing the results via result.getValue()?

At the time of writing, the result.getValue() function does not support the dot notation for joins. You could try the following result.getValue('vendor.companyname') but it will return null. This is one of those API inconsistencies that we have to live with.

Example 3: Lookup Search

Finally, let’s look at a lookup example. search.lookupFields()^[V]NetSuite (August 7, 2015. search.lookupFields(). Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/43711 [Accessed on: June 11, 2021] offers a quick (and cheap, in terms of governance units) way to search for one or more body fields on a record. This function supports joins via the dot syntax.

Going back to our previous example, let’s suppose we know the internal ID of the target purchase order and we want to get the vendor’s company name and transaction # as before. The lookup search could look like this:

require(["N/search"], function (search) {
  var poLookup = search.lookupFields({
    type: search.Type.PURCHASE_ORDER,
	id: 805757,
    columns: [
	  'tranid',
      'vendor.companyname'
    ]
  });
  
  console.log(JSON.stringify(poLookup)); // poLookup is a JavaScript object
  // Sample result: {"tranid":"PO19","vendor.companyname":"MyCom LLC."}
  
  if (poLookup.tranid) {
    console.log('PO #: ' + poLookup['tranid'] + ' | '
		+ poLookup['vendor.companyname']);
  }
});

Notice that, as with regular searches, we can express the join in the columns section using the dot notation (the verbose notation does not work here). Also, since the output of the lookup is a JavaScript object, we must use the dot notation to retrieve the joined field.

Wrap Up

The following table summarizes which join syntax can be used in what parts of a search:

As I stated earlier, my personal preference is to use the dot syntax as much as possible as I find it more compact and easier to understand. Moreover, it enjoys wider support than the verbose syntax as you can see in the preceding table.

Finally, while we’re at it, one of the long-standing limitations of NetSuite’s search module is that joins are limited to one level. If you need multi-level joins and/or control over the kind of join to do (e.g. inner vs. outer), the N/query module ^[VI]NetSuite (August 28, 2018). Scripting with the N/query Module. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/77545 [Accessed on: June 11, 2021] is your friend. This article provides a good comparison of N/search and N/query.

I hope you found this article insightful. Keep learning, keep sharing and remember to subscribe to get an email notification as soon as we publish new NetSuite Insights and freebies.

Related Posts

1. How To Easily Understand The Dispatcher Script Pattern
2. Improved Method for Identifying NetSuite System Users
3. Learn How To Create a NetSuite Server-Side Script Delay (“Sleep”)
4. Learn How To Effectively Organize Your SuiteScript Utility Functions
5. Learn To Write Custom List Code That Works In All NetSuite Environments (Revised)