CODEWITHSUNDEEP
netsuitesuitescript2.0
Wolfgang
Wolfgang

Reputation: 3540

How do I do a joined lookup with search.lookupFields()?

I'm trying to get some information about an item, including the item's subsidiary's logo, which naturally requires joining the item to the subsidiary.

The documentation for search.lookupFields says:

You can use joined-field lookups with this method, with the following syntax:

join_id.field_name

So, I duly request the fields I want, including a join on subsidiary:

require(['N/search'], function(search) {
    var item = search.lookupFields({
        type: search.Type.ITEM,
        id: 2086,
        columns: ['itemid', 'displayname', 'subsidiary.logo'],
    });
    log.debug(item);
});

itemid and displayname are fine, but when I try to join another record I get this error:

{
  "type":"error.SuiteScriptError",
  "name":"SSS_INVALID_SRCH_COLUMN_JOIN",
  "message":"An nlobjSearchColumn contains an invalid column join ID, or is not in proper syntax: logo.",
  "stack":["doLookupFields(N/search/searchUtil.js)","<anonymous>(adhoc$-1$debugger.user:2)","<anonymous>(adhoc$-1$debugger.user:1)"],
  "cause":{
    "type":"internal error",
    "code":"SSS_INVALID_SRCH_COLUMN_JOIN",
    "details":"An nlobjSearchColumn contains an invalid column join ID, or is not in proper syntax: logo.",
    "userEvent":null,
    "stackTrace":["doLookupFields(N/search/searchUtil.js)","<anonymous>(adhoc$-1$debugger.user:2)","<anonymous>(adhoc$-1$debugger.user:1)"],
    "notifyOff":false
  },
  "id":"",
  "notifyOff":false,
  "userFacing":false
}

This seems to happen no matter which record and field I try to join. What am I missing?

Upvotes: 0

Views: 3313

Answers (5)

Krypton
Krypton

Reputation: 5231

Although you can return results from multi-select fields, you cannot join to fields on records referenced by multi-select fields (which the subsidiary field on the item record is). Also, you cannot search the logo field on the subsidiary record (not listed in Search Columns under Subsidiary in the NetSuite Records Browser).

This means you have to load the Subsidiary record to get the logo field. In other words:

require(['N/record', 'N/search'], function(record, search) {
    var item = search.lookupFields({
        type: search.Type.ITEM,
        id: 2086,
        columns: ['itemid', 'displayname', 'subsidiary'],
    });
    var subID = item.subsidiary[0].value; //internal id of *first* subsidiary
    var subRec = record.load({
        type: record.Type.SUBSIDIARY,
        id: subID
    });
    var logo = subRec.getText('logo'); //gets the file name - use getValue to get its ID instead
});

Note that if multiple subsidiaries are set on the item, this only gets the values for the first one. You could iterate through the item.subsidiary result to handle values for multiple subsidiaries if required.

Upvotes: 2

Emerson Minero
Emerson Minero

Reputation: 608

As people have mentioned, the subsidiary is not a join field available from the item record, one way to achieve what you are trying to do is:

  1. Make a lookup to get the internal id of the subsidiary belonging to the desired item.
  2. Then make a lookup to get the internal id of the logo image (file cabinet image) belonging to the previous subsidiary.
  3. Make another lookup/load the image file to get the URL of the image/logo

You can try to combine the above steps in a single saved search but I think you might need to load the image file to get the URL.

Upvotes: 1

w3bguy
w3bguy

Reputation: 2250

This won't answer your question, but this may help out in the future. The records browser shows everything that you can search and join on, columns and filters, and field IDs. Very useful when building out searches.

NetSuite Records Browser - 2018.2

Upvotes: 0

Suite Resources
Suite Resources

Reputation: 1164

You can only join to tables allowed in the Item search object. Try looking for "Subsidiary..." in the Search Results tab within the UI. It's not there. Use the Schema Browser to determine what fields and joins are available.

You cannot think of a NetSuite search as you would any regular SQL search. You have to be cognizant of which fields and which joins can be utilized via the search object.

Upvotes: 1

Martin
Martin

Reputation: 128

I believe you can't access to the subsidiary record from a lookupfield, you should do a proper search.

https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2018_2/script/record/item.html

enter image description here

Upvotes: 1

Related Questions