Netsuite Transaction Saved Search: Item Join?

Question

I'm trying to get all of the items associated with a purchase order. I've created the saved search in the UI, but I need to pass the PO's internal ID to get the correct list of items. I tried doing this in suitescript, but I can't find the correct join table for Item? Looking at the record browser, I couldn't find item. Is this not possible?

var poItemSearch = search.create({
                type: 'transaction',
                filters : [
                   search.createFilter({
                       name     : 'type',
                       operator : search.Operator.IS,
                       values   : 'Purchase Order'
                   }),
                   search.createFilter({
                       name     : 'internalid',
                       operator : search.Operator.ANYOF,
                       values   : poID
                   }),
                   search.createFilter({
                       name     : 'item',
                       join     : 'item',
                       operator : search.Operator.IS,
                       values   : 'Inventory Item'
                   })
                ],
                columns: [
                   search.createColumn({name : 'item'})
                   //search.createColumn({name : 'email', join : 'custrecord_sdr_prod_pref_customer'}),
                   //search.createColumn({name : 'subsidiary', join: 'custrecord_sdr_prod_pref_customer'}),
                   //search.createColumn({name : 'custrecord_sdr_prod_pref_item'}),
                   //search.createColumn({name : 'custrecord_sdr_prod_pref_qty'}),
                   //search.createColumn({name : 'quantityavailable', join: 'custrecord_sdr_prod_pref_item'})
                ]
            });

I get an error that Item is not an option for a join. Am I just using the wrong name?

Otherwise, how do I add a filter to an existing saved search? I can go about it this way to just filter my search results on the purchase order internal id.

erictgrubaugh erictgrubaugh · Accepted Answer · 2017-04-03T23:03:59

item should be the right name for the join, but I don't think it's the right name for the name of the filter. It looks like you're trying to filter on itemtype, though I'm not sure whether it will be "Inventory Item" or "inventoryitem".

It is puzzling that item is not listed as a join in the records browser. For example, this works just fine when run in console:

require(["N/search"], function (search) {
    var purchaseorderSearchObj = search.create({
       type: "purchaseorder",
       filters: [
          ["type","anyof","PurchOrd"]
       ],
       columns: [
          search.createColumn({
             name: "itemid",
             join: "item"
          })
       ]
    });
    purchaseorderSearchObj.run().each(function(result){
       console.log(result.getValue({"name":"itemid","join":"item"}));
       return true;
    });
});

BTW I generated this code by creating a Saved Search in the UI, then using this Chrome plugin to export it to code: https://chrome.google.com/webstore/detail/netsuite-search-export/gglbgdfbkaelbjpjkiepdmfaihdokglp?hl=en It's a very useful tool to have around.

Netsuite Transaction Saved Search: Item Join?

1 Answers