QuickStart of using Formula fields in NetSuite Saved search with Examples

There are different types of formula fields in Saved search – Formula(date) , formula(date/time) , formula(numeric) , formula (percent),formula(text)

Lets start with formula(text).

Example 1: 

Say, we want to fetch the customer in sales order record in saved search. 

We can do this by two different methods. 

Method 1 is customary & Method 2 using Formula field.

First navigate to list >saved search> New> select ‘Transaction’

Fig 1: Navigate to saved search

Method 1:  Select Date from the dropdown under “Results”subtab

Method 2: Using formula field:  Check Fig 2.

Fig 2: Formula text in saved search result tab

Method 1 and Method 2 basically gives the same result.

Formula fields opens to wide range of database functions (Check below) and the details of each function can be easily found online. ( Oracle Help center – Use this link to search formula and its syntax)

# Functions in Formula text
1) ABS(n),
2) ACOS(n),
3) ADD_MONTHS(date, integer),
4) ASCII(char),
5) ASIN(n),
6) ATAN(n),
7) ATAN2(n1 { , | / } n2),
8) BITAND(expr1, expr2),
9) CASE { expr WHEN comparison_expr THEN return_expr [ WHEN comparison_expr THEN return_expr ]… | WHEN condition THEN return_expr [ WHEN condition THEN return_expr ]… } [ ELSE else_expr ] END,
10) CEIL(n),
11) CHR(n [ USING NCHAR_CS ]),
12) COALESCE(expr [, expr ]…),
13) CONCAT(char1, char2),
14) COS(n),
15) COSH(n),
16) DECODE(expr, search, result [, search, result ]…[, default ]),
17) DENSE_RANK() OVER (PARTITION by expr [,expr…] ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]… ),
18) DENSE_RANK(expr [, expr ]…) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]… ),
19) EXP(n),
20) FLOOR(n),
21) INITCAP(char),
22) INSTR(string , substring [, position [, occurrence ]]),
23) KEEP(DENSE_RANK { FIRST | LAST } ORDER BY expr [ NULLS { FIRST | LAST } ]),
24) LAST_DAY(date),
25) LENGTH(char),
26) LN(n),
27) LOG(n2, n1),
28) LOWER(char),
29) LPAD(expr1, n [, expr2 ]),
30) LTRIM(char [, set ]),
31) MOD(n2, n1),
32) MONTHS_BETWEEN(date1, date2),
33) NANVL(n2, n1),
34) NEXT_DAY(date, char),
35) NULLIF(expr1, expr2),
36) NVL(expr1, expr2),
37) NVL2(expr1, expr2, expr3),
38) POWER(n2, n1),
39) RANK() OVER (PARTITION by expr [,expr…] ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]… ),
40) RANK(expr [, expr ]…) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]… ),
41) REGEXP_INSTR(source_char, pattern [, position [, occurrence [, return_option [, match_parameter ]]]]),
42) REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ]]]]),
43) REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ]]]),
44) REMAINDER(n2, n1),
45) REPLACE(char, search_string [, replacement_string ]),
46) ROUND(date [, fmt ]),
47) ROUND(n [, integer ]),
48) RPAD(expr1 , n [, expr2 ]),
49) RTRIM(char [, set ]),
50) SIGN(n),
51) SIN(n),
52) SINH(n),
53) SOUNDEX(char),
54) SQRT(n),
55) SUBSTR(char, position [, substring_length ]),
56) TAN(n),
57) TANH(n),
58) TO_CHAR({ datetime | interval } [, fmt [, ‘nlsparam’ ] ]),
59) TO_DATE(char [, fmt [, ‘nlsparam’ ] ]),
60) TO_NUMBER(expr [, fmt [, ‘nlsparam’ ] ]),
61) TRANSLATE(expr, from_string, to_string),
62) TREAT(expr AS [ REF ] [ schema. ]type),
63) TRIM([ { { LEADING | TRAILING | BOTH }[ trim_character ] | trim_character} FROM ]trim_source),
64) TRUNC(date [, fmt ]),
65) TRUNC(n1 [, n2 ]),
66) UPPER(char),

Example 2 demonstrate to use case statement in saved searches.

Lets say , Requirement is to assign different labels for each status of sales order in  saved search results. i.e ‘Stage 1’ for Pending approval , ‘Stage 2’ for  Pending Fufillment and all other status of sales order as ‘After shipping’

Fig 3: Saved Search –  Result tab showing column using case statement

Note:Use ‘custom label’ to name the Formula text column in saved search results

Fig 4: Saved search results