Using Formula Fields in Searches

I added a custom ListOrRecordRef, contact, to a sales order. This presents a problem when I run an advanced search to fetch a sales order. I’m using SuiteTalk, so I’d prefer to get back an ExternalID rather than NetSuite’s default InternalID. 

Here’s how I did it. I started with 2 custom fields.

  1. Add the ListOrRecordRef, contact, as a transaction body field.
  2. Add a second Free-Form Text field that will hold the ExternalID

Custom Field Definitions

Here’s how custbody_contact is defined.

custbody_contact

Here’s how custbody_contact_externalid is defined.

custbody_contact_externalid

Make sure you uncheck “Store Value” and then set it to default to a formula. You can see that the formula I’m using is {custbody_contact_externalid}.

defaulting

When you build your advanced search, it looks like this (in SuiteTalk). I left a couple of “basic” columns in this example, just to help get a better perspective on where customFieldList is situated in the request.

Advanced Search Code

This is what the result looks like as a JSON object. I’ve blurred my ExternalID which is a GUID.

JSONized Result

This technique should work in sublists too. If you code an advanced search to pull all fields in one request, it is significantly faster than making multiple calls to resolve references like this one.

One thought on “Using Formula Fields in Searches

  1. I found later that this does not work when trying to pass a formula/calculated sublist field to an advanced print template. I got some great help from the NetSuite forums, where I was instructed to use the Sourcing and Filtering tab to set my calculated field value. That worked like a charm!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s