Anyone know how to write a formula field for a workbook that NetSuite Professionals #suiteanalytics

Anyone know how to write a formula field for a wor...

Juliana

07/01/2019, 5:10 AM

Anyone know how to write a formula field for a workbook that returns Amount values for only certain transaction types? For example: excluding POs from the Amounts returned, or null their values so that I can then filter them out? Or, an expression that only pulls Amounts for Bills and Journals? I know I can also do this via filters or criteria, but I only want to filter a single row's Amount values, not the entire workbook.

zvictor

07/17/2019, 9:15 PM

In Saved Search, one would use

CASE

, which is a native expression from

SQL

used to handle conditional logic at the level of the column expression (i.e., field).

zvictor

07/17/2019, 9:15 PM

Here is an example:

Copy code

CASE WHEN {type} IN ('Sales Order', 'Invoice') THEN {foreigntotal} ELSE 0 END

zvictor

07/17/2019, 9:17 PM

You would change the names of the types according to your purpose. You can have multiple conditions:

Copy code

CASE
  WHEN {name} = 'Joe' THEN 'Hey, dude!'
  WHEN {name} = 'Jane' THEN 'Hey, lady!
  WHEN {name} = 'Mike' THEN 'Ssssssup, Mike.'
  ELSE 'Who the heck are you?'
END

zvictor

07/17/2019, 9:21 PM

In my experience it's always a good idea to put in an

ELSE

. Otherwise, if there is no logical match, the expression will return

NULL

. As you know, in arithmetic or comparison, if any value is

NULL

the result is

NULL

. So, if you use

CASE

inside of a

SUM

, for example, and the result is

NULL

, check and make sure that there is an

ELSE

clause.

Open in Slack

Previous Next