Exploiting formula columns in saved searches

Introduction#

Formula columns in saved searches can exploit many features of Oracle SQL and HTML. The examples show how these features can be used, as well as pitfalls to avoid.

Oracle SQL CASE statement in a Netsuite formula

Using a CASE statement, conditionally display an expression in the column based on values found in another column, a.k.a. “my kingdom for an OR”. In the example, the result is obtained when the status of the transaction is Pending Fulfillment or Partially Fulfilled:

CASE DECODE( {status}, 'Pending Fulfillment', 1, 'Partially Fulfilled', 1, 0 )
WHEN 1 THEN expression-1
END

Parsing a hierarchical record name using a regular expression

Using a regular expression, parse a record name that might be hierarchical. The expression looks for the final colon in the name. It returns what follows the colon, or the entire name if none:

regexp_substr( {name} , '[^:]*$' )

Build a complex string by concatenating multiple fields

The example builds a string from the name of the parent record, the name of this record, and the memo of this record.

{createdfrom} || ' ' || {name} || ' ' || {memo}

Customize the CSS (stylesheet) for a column by inserting a DIV element

'<div style="font-size:11pt">' || expression || '</div>'

Protect string formulas from corruption and injection attacks

In a string formula field, consider that some values might contain substrings which look to the browser like HTML. Unless this is intentional, it is important to protect the values from corruption. This is useful to avoid injection attacks: it prevents someone from entering HTML into a comment field in a web order that later gets interpreted on the desk of the customer service rep.

htf.escape_sc( expression )

Protect field values from corruption when passing through a URL

utl_url.escape( expression )

Test the value of mainline in an SQL CASE statement

In a saved search formula, the possible values of mainline are designed to be useful in an HTML context. When mainline is true, the value of {mainline} is the 1-character string * (asterisk). When mainline is false, the value of {mainline} is the 6-character string   (non-breaking space, HTML encoded as a character entity reference). These string values can be compared with string literals in an SQL context.

CASE
WHEN {mainline} = '*' THEN expression-when-true
WHEN {mainline} = ' ' THEN expression-when-false
END

Complex, real-world-like example

The following example combines several of the techniques covered here. It puts a hyperlink in a custom formatted column which, when clicked, opens the sales order record associated with a row. The hyperlink is designed to open the record in a new window or tab when clicked, and to display a tooltip when hovered. The internalid field used in the URL is protected from URL encoding. The customer name, when available, is displayed in the same column, protected from HTML encoding.

'<div style="font-size:11pt">'
 ||
CASE {mainline}
WHEN '*' THEN '<br>' || htf.escape_sc( regexp_substr( {name} , '[^:]*$' ) ) || '<br>'
END
 ||
'<a alt="" title="Open the order associated with this line." '
 ||
'href="javascript:void(0);" onClick="window.open('''
 ||
'https://system.na1.netsuite.com/app/accounting/transactions/transaction.nl?id='
 ||
utl_url.escape( {internalid} )
 ||
''' , ''_blank'' )">'
 ||
{number}
 ||
'</a>'
 ||
'</div>'

Count records with with and without a value provided in a field (count missing and non-missing values)

Using Oracle SQL’s NVL2() function, you can create a display column which contains one value if a field contains data and another value if a field does not contain data. For example, in an Entity search, turn the presence of a primary e-mail address into a text display column:

NVL2( {email} , 'YES' , 'NO' )

This lets you count records subtotaled by the presence or absence of an email address:

Field: Internal ID
Summary Type: Count

Field: Formula (Text)
Summary Type: Group
Formula: NVL2( {email} , 'YES' , 'NO' )