Sign up to receive SuiteScript examples and advice directly in your email inbox.

Columns from Related Records

Created: July 12, 2017

Let's look at how to retrieve data from related records through our Search Columns.

We retrieve data from related records in our Searches using Join Columns.

There are two different ways to specify a Join Column:

  1. Using the Object syntax for a Column, specify the join property
  2. Using the String syntax for a Column, specify "joinName.columnName"

Here is an example showing both notations. This Customer Search retrieves the First Name and Email Address of the Customer and the same fields from the Sales Rep associated to the Customer:

s.create({
  type: s.Type.CUSTOMER,
  filters: [ /* filters here */ ],
  columns: [
    "firstname", // the Customer's first name
    "email", // the Customer's email address
    {
      name: "email",
      join: "salesrep"
    }, // Join Column for the associated Sales Rep's email address
    "salesrep.firstname" // Join Column for the Sales Rep's first name
  ]
})

When you retrieve the value from a Join Column using getValue, you need to again specify the join property:

var salesRepEmail = result.getValue({
  name: "email",
  join: "salesrep"
})

To find the available Joins for the Record Type you are searching, check the "Search Joins" section of the Records Browser for that particular Record Type.