How to use formula inside Saved Search and Workflow in Netsuite ?

Note: Dear reader if you are looking for something particular in formula, please use our FB group or
comment under comment section.


Hi All,

In this post, we are going to discuss about formula and its uses.

To read our blog anytime use our Android APP. You can find jobs , referrals and freelancing jobs also. 

To download Netsuite Guru App click .

User can get body field's value and line item field's value directly from Saved Search column
and also we can use functionality and add Filter.
But what about logical , computational part? Is there any way we can add logic inside
Saved Search or Workflow ?
Answer is Yes. Netsuite provide us functionality to add formula and use
 PL SQL functions.

Here I want to add that not all functions are accepted , also not in exact way
 but Netsuite support many similar to PL SQL.

If you are making Saved Search, then functions can be used based on return type,
so If you are using formula(Text) then you can only use those formula where end
 result is Text or End Result of every thing on that row is Text.

Same way we can add Formula in Workflow also. Here I want to add that
Workflow is like User Event in Netsuite. Now we can add workflow action
script and process line items also.

Numeric Functions
Function	Syntax	Description	Example
ABS	ABS(n)	returns the absolute value of n	ABS({amount})
ACOS	ACOS(n)	returns the arc cosine of n	ACOS(0.35)
ASIN	ASIN(n)	returns the arc sine of n	ASIN(1)
ATAN	ATAN(n)	returns the arc tangent of n	ATAN(0.2)
ATAN2	ATAN2(n1 { , | / } n2)	returns the arc tangent of n1 and n2	ATAN2(0.2, 0.3)
BITAND	BITAND(expr1, expr2)	computes an AND operation on the bits of expr1 and expr2	BITAND(5, 3)
CEIL	CEIL(n)	returns smallest integer greater than or equal to n	CEIL({today}- {createddate})
COS	COS(n)	returns the cosine of n	COS(0.35)
COSH	COSH(n)	returns the hyperbolic cosine of n	COSH(-3.15)
EXP	EXP(n)	returns e raised to the nth power, where e = 2.71828183	EXP({rate})
FLOOR	FLOOR(n)	returns largest integer equal to or less than n	FLOOR({today} -{createddate})
LN	LN(n)	returns the natural logarithm of n	LN(20)
LOG	LOG(n2, n1)	returns the logarithm, base n2, of n1	LOG(10, 20)
MOD	MOD(n2, n1)	returns the remainder of n2 divided by n1	MOD(3:56 pm -{lastmessagedate},7)
NANVL	NANVL(n2, n1)	returns an alternative value n1 if the input value n2 is not a number	NANVL({itemisbn13}, '')
POWER	POWER(n2, n1)	returns n2 raised to the n1 power	POWER({custcoldaystoship} ,-.196)
REMAINDER	REMAINDER(n2, n1)	returns the remainder of n2 divided by n1	REMAINDER({transaction.totalamount} , {transaction.amountpaid})
ROUND (number)	ROUND(n [, integer ])	returns n rounded to integer places to the right of the decimal point	ROUND(({today}- {startdate}), 0)
SIGN	SIGN(n)	returns the sign of n	SIGN({quantity})
SIN	SIN(n)	returns the sine of n	SIN(5.2)
SINH	SINH(n)	returns the hyperbolic sine of n	SINH(3)
SQRT	SQRT(n)	returns the square root of n	SQRT(POWER({taxamount} , 2))
TAN	TAN(n)	returns the tangent of n	TAN(-5.2)
TANH	TANH(n)	returns the hyperbolic tangent of n	TANH(3)
TRUNC (number)	TRUNC(n1 [, n2 ])	returns n1 truncated to n2 decimal places	TRUNC({amount}, 1)

 

Function	Syntax	Description	Example
CHR	CHR(n [ USING NCHAR_CS ])	returns the character having the binary equivalent to n as a VARCHAR2 value	CHR(13)
CONCAT	CONCAT(char1, char2)	concatenates char1 and char2 into one string	CONCAT({number}​,​ CONCAT(​'​_​'​,​{​line}​)​)
INITCAP	INITCAP(char)	returns char, with the first letter of each word in uppercase, all other  letters in lowercase	INITCAP({customer.​companyname}​)
LOWER	LOWER(char)	returns char, with  all letters lowercase	LOWER({customer.​companyname}​)
LPAD	LPAD(expr1, n [, expr2 ])	returns expr1, left- padded to length n characters with the sequence of characters in expr2	LPAD({line},3,'0')
LTRIM	LTRIM(char [, set ])	removes from the left end of char all of the characters contained in set	LTRIM({companyname},'-')
REGEXP_REPLACE	REGEXP_REPLACE(source_char, pattern	lets you replace a sequence of characters (source_char) that matches a regular expression pattern with another set of characters (replace_string)	REGEXP_REPLACE({name}, '^.*:', '')
	[, replace_string
	[, position
	[, occurrence
	[, match_parameter ]
	]
	]
	]
	)
REGEXP_SUBSTR	REGEXP_SUBSTR(source_char, pattern	lets you extract a sequence of characters that matches a regular expression pattern from the source string (source_char)	REGEXP_SUBSTR(​{​item}​,​'​[​^​:​]​+​$​'​)
	[, position
	[, occurrence
	[, match_parameter ]
	]
	]
	)
REPLACE	REPLACE(char, search_string	returns char with every occurrence of search_string replaced with replacement_string	REPLACE({serialnumber}, '&', ',')
	[, replacement_string ]
	)
RPAD	RPAD(expr1 , n [, expr2 ])	returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary	RPAD({firstname},20)
RTRIM	RTRIM(char [, set ])	removes from the right end of char all of the characters that appear in set	RTRIM({paidtransaction.​externalid}​,​'​-​Invoice'​)
SOUNDEX	SOUNDEX(char)	returns a character string containing the phonetic representation of char	SOUNDEX({companyname})
SUBSTR	SUBSTR(char, position [, substring_length ])	returns a portion of char, beginning at character position, substring_length characters long	SUBSTR({transaction.​salesrep}​,​1,​3)
TRANSLATE	TRANSLATE(expr, from_string, to_string)	returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string	TRANSLATE({expensecategory}, ' ', '+')
TRIM	TRIM([ { { LEADING | TRAILING | BOTH }	lets you trim leading or trailing characters (or both) from a character string	TRIM (BOTH ',' FROM {custrecord_assetcost})
	[ trim_character ]
	| trim_character
	}
	FROM
	]
	trim_source
	)
UPPER	UPPER(char)	returns char, with all letters uppercase	UPPER({unit})

Character Functions Returning Number Values

Function	Syntax	Short Description	Example
ASCII	ASCII(char)	returns the decimal representation in the database character set of the first character of char	ASCII({taxitem})
INSTR	INSTR(string , substring [, position [, occurrence ] ])	searches string for substring	INSTR({messages.message}, 'cspdr3')
LENGTH	LENGTH(char)	returns the length of char	LENGTH({name})
REGEXP_INSTR	REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] )	lets you search a string for a regular expression pattern	REGEXP_INSTR ({item.unitstype}, '\d')
TO_NUMBER()	TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])	converts a formatted TEXT or NTEXT expression to a number	TO_NUMBER({quantity})

Datetime Functions

Function	Syntax	Short Description	Example
ADD_MONTHS	ADD_MONTHS(date, integer)	returns the date plus integer months	ADD_MONTHS({today},-1)
LAST_DAY	LAST_DAY(date)	returns the date of the last day of the month that contains date	LAST_DAY({today})
MONTHS_BETWEEN	MONTHS_BETWEEN(date1, date2)	returns the number of months between date1 and date2	MONTHS_BETWEEN(​SYSDATE,​{​createddate}​) See also Sysdate.
NEXT_DAY	NEXT_DAY(date, char)	returns the date of the first weekday named by char that is later than the date	NEXT_DAY({today},'SATURDAY')
ROUND (DATE)	ROUND(date [, fmt ])	returns date rounded to the unit specified by the format model fmt	ROUND(TO_DATE('12/31/2014', 'mm/dd/yyyy')-{datecreated})
TO_CHAR()	TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])	converts a datetime or interval value to a value of VARCHAR2 datatype in the format specified by the date format fmt	TO_CHAR({date}, 'hh24')
TO_DATE()	TO_DATE(char [, fmt [, 'nlsparam' ] ])	converts a formatted TEXT or NTEXT expression to a DATETIME value	TO_DATE('31.12.2011', 'DD.MM.YYYY')
TRUNC (DATE)	TRUNC(date [, fmt ])	returns date with the time portion of the day truncated to the unit specified by the format model fmt	TRUNC({today},'YYYY')

NULL-Related Functions

Function	Syntax	Short Description	Example
COALESCE	COALESCE(expr [, expr ]...)	returns the first non-null expr in the expression list.	COALESCE({​quantitycommitted}​,​ 0)
NULLIF	NULLIF(expr1, expr2)	compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.	NULLIF({price}, 0)
NVL	NVL(expr1, expr2)	lets you replace null with the second parameter.	NVL({quantity},'0')
NVL2	NVL2(expr1, expr2, expr3)	If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.	NVL2({location}, 1, 2)

Decode

Function	Syntax	Short Descriptions	Example
DECODE	DECODE(expr, search, result [, search, result ]... [, default ] )	Compares expr to each search value one by one. If expr is equal to a search, the corresponding result is returned. If no match is found, default is returned.	DECODE({systemnotes.name}, {assigned},'T','F')

Sysdate

Function	Syntax	Short Description	Example
SYSDATE	SYSDATE	returns the current date and time set	TO_DATE(SYSDATE, 'DD.MM.YYYY') or TO_CHAR(SYSDATE, 'mm/dd/yyyy') See also TO_DATE and TO_CHAR in the Datetime Functions.

Case

Function	Syntax	Short Description	Example
CASE	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	returns value based on different conditions	CASE {state} WHEN 'NY' THEN 'New York' WHEN 'CA' THEN 'California' ELSE {state} END or CASE WHEN {quantityavailable} > 19 THEN 'In Stock' WHEN {quantityavailable} > 1 THEN 'Limited Availability' WHEN {quantityavailable} = 1 THEN 'The Last Piece' WHEN {quantityavailable} IS NULL THEN 'Discontinued' ELSE 'Out of Stock' END

Analytic and Aggregate Functions

Function	Syntax	Short Description	Example
DENSE_RANK	DENSE_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) or DENSE_RANK() OVER (PARTITION by expr [,expr...] ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... )	Computes the rank of a value with respect to other values and returns the rank as a NUMBER. Always results in consecutive rankings.	DENSE_RANK ({amount}) WITHIN GROUP (ORDER BY {AMOUNT}) or DENSE_RANK() OVER (PARTITION BY {name} ORDER BY {trandate} DESC)
KEEP()	KEEP(DENSE_RANK { FIRST | LAST } ORDER BY expr [ NULLS { FIRST | LAST } ])	Qualifies an aggregate function indicating that only the FIRST or LAST values of the function are returned.	KEEP(DENSE_RANK LAST ORDER BY {internalid})
RANK	RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [, expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... ) or RANK() OVER (PARTITION by expr [,expr...] ORDER BY expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] [,expr [ DESC | ASC ] [ NULLS { FIRST | LAST } ] ]... )	Computes the rank of a value in a group of values. Can result in non-consecutive rankings if values are the same.	RANK() OVER (PARTITION by {tranid} ORDER BY {line} DESC) or RANK ({amount}) WITHIN GROUP (ORDER BY {amount})

 Let me know if above methods are not making sense to you.

Thanks
Netsuite Guru