Tips and Tricks in Using Saved Search Formulas

FORMULA OVERVIEW:

A formula in saved searches may contain the following elements:

  1. Texts or Strings
  2. Numbers
  3. NetSuite fields
  4. Oracle functions and Operators.

A formula can be used not only to display results, but also to filter a search.

These are different types of NetSuite Fields.

  1. Formula (Text)
  2. Formula (Numeric)
  3. Formula (Currency)
  4. Formula (Date)
  5. Formula (Date/Time)
  6. Formula (Percent)

When do we use these types of Formula fields?

The Formula Field Type to be used is based on the expected result. If the expected result is a text, the Formula Field type should be Formula (Text). If the expected result is a number, the Formula Field type should be Formula (Numeric). The same is true if the expected result is currency or date.

An ‘Invalid Expression’ error messages is encountered when the incorrect Formula Field Type is used.

These are the different Comparison and Arithmetic Operators:

Comparison Operators:

< For less than

<= For less than or equal to

> For greater than

>= For greater than or equal to

= For equal to

!= or <> For not equal to

Arithmetic Operations:

+ For addition

– For subtraction

* For multiplication

/ For division

Best Practices in Generic Use of Formulas:

  1. The Formula Field Type to be used is based on the expected result.
  2. Use single quotes in the formula to display Text result.
  3. Use the standard operators (+, -, /, *) in the formula to calculate Numbers.
  4. Do not use commas in Numbers.
  5. Enclose calculations in the formula with parentheses.
  6. Use || (double pipes) in the formula to concatenate.

NETSUITE FIELDS IN FORMULAS:

The field ID format of the target field is searchcolumn. If the field is coming from a related record or a joined field, use searchjoin.searchcolumn.

Here are following ways on how to find the NetSuite ID to use in a formula:

  1. Formula Picker. You can pick the formula field in the column list, and then pick the field needed under the Field dropdown list.
  2. SuiteScript Records Browser. The standard fields are listed here.
  3. Field Help. Use the field id found when you right click the label of the field on the forms.

Best Practices in Adding a NetSuite Field:

  1. Always enclose a NetSuite field in curly braces.
  2. Do not enclose a NetSuite field in quotes.
  3. The NetSuite field must be in lowercase.

CASE WHEN STATEMENT:

The CASE WHEN statement is the equivalent of the IF ELSE statement in programming. It evaluates the given expression, then it compares it to your condition. As an output, it will return the value set or the defined default option. The IN condition in CASE WHEN Statement is like is a membership condition where it checks if the value exists or if it is equal to a specified list.

The use of wildcards: % (percent) and _ (underscore) finds matches in string comparisons. The use of AND and OR logic in conditions evaluates multiple conditions. The use of IS NULL and IS NOT NULL conditions returns empty and non-empty values. The use of the Checkbox field value as ‘T’ or ‘F’ in the condition filters the saved search.

The Main Line is a reporting concept in NetSuite where it describes the fields present on the Header or Primary information section of the Transaction record. It is necessary that to use this Main Line filter to see the summary details or the lines details of the transactions and to avoid duplicates.

A syntax has different parts which are:

the opening CASE clause

the WHEN comparison

its equivalent THEN value

the ELSE default value

closed by the END clause

Sample Use Case Scenario:

  1. Create an Item search wherein if the quantity on hand is more than 10, mark it as: ‘Enough Stock’, if not, mark it as, ‘Needs to Replenish’.

Formula (Text) = CASE WHEN {quantityonhand} > 10 Then ‘Enough Stock’ ELSE ‘Needs to Replenish’ END

  • Using a Case statement, check if ‘test’ and ‘TEST’ are equal or not.

Formula (Text) = CASE WHEN ‘test’ = ‘TEST’ THEN ‘Equal’ ELSE ‘Not Equal’ END

  • In a Transaction Search, identify if the transaction Type is either a Sales Order, a Cash Sale or an Invoice. If yes, return the Amount of the transaction. Otherwise, return Null.

Formula (Text) = CASE WHEN {type} = ‘Sales Order’ THEN {amount} WHEN {type} = ‘Cash Sale’ THEN {amount} WHEN {type} = ‘Invoice’ THEN {amount} ELSE NULL END

–or–

Formula (Text) = CASE WHEN {type} IN (‘Sales Order’, ‘Cash Sale’, ‘Invoice’) THEN {amount} ELSE NULL END

  • Identify all Items with the word: ‘Pasta’ OR ‘Coffee’ on their name. If the item name contains ‘Pasta or Coffee’, return ‘Needs Promotion’. Otherwise, return empty.

Formula (Text) = CASE WHEN {name} LIKE ‘%Pasta%’ OR {name} LIKE ‘%Coffee%’ Then ‘Needs Promotion’ ELSE NULL END

  • Find all Items with the word: ‘K*te’ on their name. Then return ‘K Promo’ else empty. For example, it returns matches whose first name is Kate, Kite, Kyte, Kete, Kute, etc.

Formula (Text) = CASE WHEN {name} LIKE ‘K_te’ Then ‘K Promo’ ELSE NULL END

  • Find all customers who have no phone AND no email on their records and mark them as ‘For Review Or Inactivation’.

Formula (Text) = CASE WHEN {phone} IS NULL AND {email} IS NULL THEN ‘For Review and Inactivation’ ELSE NULL END

  • Find all customers who have existing phone OR email on their records and mark them as ‘For Nurturing’.

Formula (Text) = CASE WHEN {phone} IS NOT NULL OR {email} IS NOT NULL THEN ‘For Nurturing’ ELSE NULL END

  • Search for all Inactive customers and mark them as ‘Reviewed’.

Formula (Text) = CASE WHEN {isinactive} = ‘T‘ THEN ‘Reviewed’ ELSE NULL END

HANDLING NULL VALUES:

The NULLIF is a function which accepts 2 arguments. If these arguments are equal, then it will return NULL. If it is NOT equal, then the function will return the 1st argument.

Syntax:

NULLIF(expr1, expr2)

In Case statement translation:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

The NVL is also a function which accepts 2 arguments. If the 1st argument is NULL, then it returns the 2nd argument. If the 1st argument is NOT NULL, then it returns the 1st argument.

Syntax:

NVL(expr1, expr2)

In Case statement translation:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

The NVL2 is a function which accepts 3 arguments. If the 1st argument is NOT NULL, then it returns the 2nd argument. But if the 1st argument is NULL, then it returns the 3rd argument.

Syntax:

NVL2(expr1, expr2, expr3)

In Case statement translation:

CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE expr3 END

Sample Use Case Scenario:

  1. Create a Customer search which shows the assigned Sales Rep. If the customer is currently assigned to ‘Sales Rep – PlaceHolder’, then return empty.

Formula (Text) = NULLIF({salesrep}, ‘Sales Rep Placeholder’)

  • Avoid the Divide by Zero error message. If {quantity} = 5 and {quantityshiprecv} = 0, rewrite: {quantity}/{quantityshiprecv} to return as NULL, instead of an Error.

Formula (Percent) = {quantity}/NULLIF({quantityshiprecv}, 0)

  • Create a Customer search which shows the assigned Sales Rep. If the customer is currently unassigned to then return ‘Unknown’.

 Formula (Text) = NVL({salesrep}, ‘Unknown’)

  • Create a Customer search which shows the assigned Sales Rep. If the customer is currently assigned to an existing Sales Rep, then return ‘Nurtured by {salesrep}’, where {salesrep} is the assigned Sales rep. If the customer is unassigned, then return ‘Needs reassignment’.

 Formula (Text) = NVL2({salesrep}, ‘Nurtured By ‘ || {salesrep}, ‘Needs Reassignment’)

NS CONCAT:

The NS_CONCAT function is similar with the WM_CONCAT function in Oracle. The NS_CONCAT enlists the summary details in one field column. Currently, the NS_CONCAT function is limited to 4,000 characters only.

Sample Use Case Scenario:

  1. Create a Transaction search which returns all Sales Order transactions ID, per Customer, in one line.

Under Criteria tab, add fields:

  • Type = Sales Order
  • Main Line = True
  • Tax Line = False
  • COGS Line = False
  • Shipping Line = False

Under Results tab, add fields:

  • Name : Summary Type = Group
  • Formula (Text) = NS_CONCAT({number}) : Summary Type = Maximum
  • Create a Transaction search which returns all the items ordered in Sales Order transactions, in one line.

Note: Remove extra Tax Lines, COGS lines, Shipping lines on the Results to avoid duplicates.

Under Criteria tab, add fields:

  • Type = Sales Order
  • Main Line = False
  • Tax Line = False
  • COGS Line = False
  • Shipping Line = False

Under Results tab, add fields:

  • Document Number  : Summary Type = Group
  • Formula (Text) = NS_CONCAT({item})  : Summary Type = Maximum

Leave a comment

Your email address will not be published. Required fields are marked *