Display Conditional Search Values Based on Field Values Being Populated

When searching records in NetSuite, users occasionally have a need to return values based on a conditional basis. For example, many of our clients conduct business utilizing billing partners. These clients may want to create transaction searches that provide the appropriate customer record based upon fields being populated. If a billing customer is indicated on the transaction, that customer should be included in the search results. If no billing customer is identified, then the primary customer is returned. Thankfully, NetSuite saved searches provide a helpful function to allow for this conditional reporting. Using the COALESCE function, any saved search can return a value based on the first populated value from a list of fields.

Using our billing customer example above, assume we want to create a saved search that displays the customer to be billed. If the transaction contains a billing partner based on a custom field, return that customer record. If no billing partner exists, return the customer indicated on the transaction. Below is an example of how to accomplish this request.

The COALESCE function will advance through the list of indicated fields and return the first non-null value. In the example above, the custom Bill Partner field is checked first. If it has a value, that value is returned. The function then moves to the Customer field and returns that if it’s populated. The COALESCE function accepts multiple parameters, so you can check multiple fields for values.

Need some advanced saved search functionality assistance within NetSuite? Contact us using the form below!

Related Posts