7 Field Lookups in SuiteScript 2.0

Created: June 12, 2017

NetSuite's lookup feature allows users to very quickly retrieve body-level data for a particular record without having to load the entire record on a page. This feature is provided by the nlapiLookupField function in SuiteScript 1.0 and the N/search module's lookupFields method in SuiteScript 2.0.

N/search#lookupFields

Assume we import the N/search module like so:

define(["N/search"], function (s) { ... });

Here we see a 1:1 relationship between the 1.0 and 2.0 lookup functions; they behave largely the same as well.

The lookup functionality is contained in the search module because, under the hood, a lookup is a search with filters on record type and internal ID. Thus you can expect that a lookup will always have similar performance to a search.

We can use lookups to retrieve a single field or multiple fields from a specific record, and we can even use it to retrieve data from related records through joined fields. Let's look at an example for each of those situations.

Example - Single Field

To put a little business context around our examples, we'll say we are building something for our Customer Support Manager. She is tasked with doing periodic spot surveys on recent Support Case work by her team members. Our job is to build something that pulls in relevant contact information from the most recent Case worked by an Employee.

One possible user flow here might be that the Manager goes to the Employee record, and we put the spot survey data into its own subtab there. In this case, we might have a User Event script executing on beforeLoad that pulls in and displays the relevant data.

To ensure that we're staying focused on the lookup functionality, I'll skip the UI portions of the code, and we'll just zero in on the lookup work.

The very first piece of information our Manager might want to see is simply " What is the Case Number of this Employee's most recent case?"

// 1.0
function getCaseData(caseId) {
    var caseNumber = nlapiLookupField("supportcase", caseId, "casenumber");
    nlapiLogExecution("DEBUG", "Case Number", caseNumber);
    return {caseNumber: caseNumber};
}

// 2.0
function getCaseData(caseId) {
    var caseData = s.lookupFields({type: s.Type.SUPPORT_CASE, id: caseId, columns: "casenumber"});
    log.debug({title: "Case Number", details: caseData.casenumber});
    return caseData;
}

We're not concerned with how we get the ID of the latest Case; we'll just assume it's retrieved elsewhere and passed in to our function appropriately.

Here we can see how we use lookups to retrieve a single body field from a record. Notice the difference in how we access the resulting data between 1.0 and 2.0 in the log statements.

In 1.0, when we only provide a single column to nlapiLookupField, then only the value of that field is returned. In 2.0, the lookupFields method always returns an Object with the columns as properties, even when there is only a single column.

The 1.0 approach here might seem more convenient to some, but let's look at what happens when we get a request for more data as part of this feature. Along with the Case Number, we want to see its Created Date, its Date Closed, and the Email Address the Customer provided:

Example - Multiple Fields

// 1.0
function getCaseData(caseId) {
    var caseData = nlapiLookupField(
        "supportcase",
        caseId,
        ["casenumber", "createddate", "enddate", "email"]
    );
    nlapiLogExecution("DEBUG", "Case Data", JSON.stringify(caseData));
    return caseData;
}

// 2.0
function getCaseData(caseId) {
    var caseData = s.lookupFields({
        type: s.Type.SUPPORT_CASE,
        id: caseId,
        columns: ["casenumber", "createddate", "enddate", "email"]
    });
    log.debug({title: "Case Data", details: caseData});
    return caseData;
}

When I changed to retrieving multiple fields, I had to rewrite a lot of my 1.0 code. Because I specified multiple columns, nlapiLookupField now returns an Object with the columns as properties, much like the 2.0 method already does.

I've modified the log message accordingly to print the entire result object, but I had to wrap it in JSON.stringify myself; notice that I did not have to do that in 2.0 as the log module automatically stringifies Objects passed in to the details property.

In 2.0, since I was already expecting lookupFields to return an Object, I merely changed my columns value to an Array of Strings and continued on accessing the data like I was previously. This makes the 2.0 API a bit more consistent than its 1.0 counterpart in that the 2.0 version always returns an Object, no matter how many columns you're retrieving.

Example - Joined Fields

I also promised that we could lookup joined fields, meaning fields from records linked to the one we are searching. In our example, the whole point is to provide the Customer Support Manager with relevant contact information from the case. We've pulled in the email address provided by the customer already, but we also want to see the Customer's main phone number as well as the primary contact's email address and phone number.

// 1.0
function getCaseData(caseId) {
    var caseData = nlapiLookupField(
        "supportcase",
        caseId,
        [
            "casenumber",
            "createddate",
            "enddate",
            "email",
            "customer.phone",
            "contact.phone",
            "contact.email"
        ]
    );
    nlapiLogExecution("DEBUG", "Case Data", JSON.stringify(caseData));
    return caseData;
}

// 2.0
function getCaseData(caseId) {
    var caseData = s.lookupFields({
        type: s.Type.SUPPORT_CASE,
        id: caseId,
        columns: [
            "casenumber",
            "createddate",
            "enddate",
            "email",
            "customer.phone",
            "contact.phone",
            "contact.email"
        ]
    });
    log.debug({title: "Case Data", details: caseData});
    log.debug({title: "Contact Phone", details: caseData["contact.phone"]});
    // ^ !!! NOT caseData.contact.phone
    
    return caseData;
}

We use the dot syntax to specify joined fields: joinId.columnId. You can use the Records Browser to identify the available joins for a record.

Also be aware that when you are using the Records Browser to identify lookup columns, you will be using the Search Columns for the record, NOT the field IDs. Remember that under the hood, a lookup is just a search, so all of your columns should come from the Search Columns list, not the field IDs list.

Lastly, I've added a second log statement in the 2.0 code so you can observe what I would consider a bit of weirdness when reading the joined fields. The key name for the join columns is the entire joinId.columnId string, with the dot included, so we have to use the square-bracket index syntax rather than the typical dot syntax for reading it.

Advantages and Limitations

The largest advantage lookups give us over loading the record is that it executes faster and uses less governance. This works on both standard and custom records. It also involves significantly less code.

Unfortunately, we cannot lookup data from sublists; if you need to retrieve data from sublist fields, you will need to perform a search or load the record.

We are also limited to only a single level of joins, as we are with all NetSuite searches.