Netsuite: How to search records, related records, operators, type of search fields in Ruby.

Hey guys how’s it going?

Why Netsuite does not publish the available operators for searching and makes our lives easier? well I don’t know but today I’m going to share with you how to search custom records by a custom field within Netsuite in my case for instance: I want to search all the Sales order that have an empty custom field you know a custom field is not a default field within the Netsuite Schema and my custom field is called: ‘custbody_order_number’, so based in that this is the code I have created:

Btw I’m using for that this gem: https://github.com/NetSweet/netsuite

Searching all type of sale orders records that do not have a empty custom field

def search_empty_sales_order_number
[
    {
        field: 'customFieldList',
        value: [{
                    field: 'custbody_order_number',
                    type: 'SearchStringCustomField',
                    operator: 'empty'
                }]
    }
]
end

search_result = NetSuite::Records::SalesOrder.search(
  criteria: {
    basic: search_empty_sales_order_number
  }
)
search_result.results

searching only “salesOrder” types  that do not have a empty custom field

def search_empty_sales_order_number
[
    {
        field: 'customFieldList',
        value: [{
                    field: 'custbody_order_number',
                    type: 'SearchStringCustomField',
                    operator: 'empty'
                }]
    },
    {
        field: 'type',
        operator: 'anyOf',
        type: 'SearchEnumMultiSelectField',
        value: ['_salesOrder']
    }
]
end

search_result = NetSuite::Records::SalesOrder.search(
  criteria: {
    basic: search_empty_sales_order_number
  }
)

Searching by multiple custom fields

def search_empty_tracking_link_fullfilments
[
    {
        field: 'customFieldList',
        value: [{
                    field: 'custom_tracking_number',
                    type: 'SearchStringCustomField',
                    operator: 'notEmpty'
          },
          {
                    field: 'custom_transaction_id',
                    type: 'SearchStringCustomField',
                    operator: 'empty'
          }
        ]
    },
    {
        field: 'type',
        operator: 'anyOf',
        type: 'SearchEnumMultiSelectField',
        value: ['_itemFulfillment']
    }

]
end

search_result = NetSuite::Records::ItemFulfillment.search(
  criteria: {
    basic: search_empty_tracking_link_fullfilments
  }
)
search_result.results

Search all the billed sales order within Netsuite

def search_orders
[
    {
        field: 'status',
        operator: 'anyOf',
        type: 'SearchEnumMultiSelectField',
        value: ["SalesOrd:G"]//stands for billed :(
        // search the full order code status here
        //http://blog.prolecto.com/2013/08/30/netsuite-searchfilter-transaction-internal-status-list/
    }
]
end

search_result = NetSuite::Records::SalesOrder.search(
  criteria: {
    basic: search_orders
  }
)
search_result.results

As you see I’m using the “empty” operator but you have available the operators as follow:

• contains
• doesNotContain
• doesNotStartWith
• empty
• hasKeywords
• is
• isNot
• notEmpty
• within
• noneOf

Searching all the sale order transactions(records related) including ItemFulfillments, invoices, customer deposits, etc

def search_order_transactions
[
    {
        field: 'createdFrom',
        operator: 'anyOf',
        type: 'SearchRecordField',
        value: [ NetSuite::Records::SalesOrder.new(internal_id: 47383) ]
    }
]
end

search_results_fullfilments = NetSuite::Records::Transaction.search(
  criteria: {
    basic: search_order_transactions
  }
)
search_results_fullfilments.results

Searching sale order item fulfillments

def search_order_transactions
[
    {
        field: 'createdFrom',
        operator: 'anyOf',
        type: 'SearchRecordField',
        value: [ NetSuite::Records::SalesOrder.new(internal_id: 47383) ]
    },
    {
        field: 'type',
        operator: 'anyOf',
        type: 'SearchEnumMultiSelectField',
        value: [ "_itemFulfillment" ]
    }
]
end

search_results_fullfilments = NetSuite::Records::Transaction.search(
  criteria: {
    basic: search_order_transactions
  }
)

search_results_fullfilments.results

Available fields types for searching within Netsuite

• SearchBooleanCustomField
• SearchBooleanField
• SearchColumnBooleanCustomField
• SearchColumnBooleanField
• SearchColumnCustomField
• SearchColumnCustomFieldList
• SearchColumnDateCustomField
• SearchColumnDateField
• SearchColumnDoubleCustomField
• SearchColumnDoubleField
• SearchColumnEnumMultiSelectCustomField
• SearchColumnEnumSelectField
• SearchColumnField
• SearchColumnLongCustomField
• SearchColumnLongField
• SearchColumnMultiSelectCustomField
• SearchColumnSelectCustomField
• SearchColumnSelectField
• SearchColumnStringCustomField
• SearchColumnStringField
• SearchColumnTextNumberField
• SearchCustomField
• SearchCustomFieldList
• SearchDateCustomField
• SearchDateField
• SearchDoubleCustomField
• SearchDoubleField
• SearchEnumMultiSelectCustomField
• SearchEnumMultiSelectField
• SearchLongCustomField
• SearchLongField
• SearchMultiSelectCustomField
• SearchMultiSelectField
• SearchRecord
• SearchResult
• SearchRow
• SearchRowList
• SearchStringCustomField
• SearchStringField
• SearchTextNumberField

That’s it hope you find useful this post, and as always thanks for reading.

H.