NetSuite CASE WHEN Formula With Multiple Criteria and Date Parameters

If you are a complete beginner to using SQL in your saved searches then I advise first visiting the article NetSuite CASE WHEN Formula. In that first article we looked at a simple CASE WHEN statement that allows us to include two different types of results in one column.

In this article we are going to build on that in a couple of ways. First, we will look at how you can include multiple criteria in your search and then we will touch on using date parameters.

CASE WHEN Formula with Multiple Criteria

Lets say, for example, we want a column on a transaction search of invoices and credit memos to only pick up the values for invoices posted by me. We can start with the formula we used in the previous article which already pulls out the invoice values from the total.

CASE WHEN {type} = 'Invoice' THEN {amount} ELSE 0 END

Now what we want to do is add a second criteria to the formula so we are asking for transactions where the type is Invoice AND transactions that are posted by me. It is as simple as adding the word AND to join a second criteria. The formula would then look like this –

CASE WHEN {type} = 'Invoice' AND {createdby} = 'Aaron Meeks' THEN {amount} ELSE 0 END

Now what if we want to include Invoices posted by me and Credit Memos posted by Joe Brand? Starts getting complicated right? Wrong. If we break it down you’ll find it is much simpler than it looks. This statement is made up of distinct sections and if we are adding a second set of criteria we need to know where the first set begins and ends. I will re-write the statement but split it out into its component sections to make this more visual.

CASE
	WHEN {type} = 'Invoice' AND {createdby} = 'Aaron Meeks'
		THEN {amount}
	ELSE 0
END

So hopefully here you can see better the various components that make up the statement more easily. The 2nd line, beginning with WHEN is one set of criteria and the 3rd line starting with THEN indicates our desired result. Adding a second set of criteria will therefore look like this –

CASE
	WHEN {type} = 'Invoice' AND {createdby} = 'Aaron Meeks'
		THEN {amount}
	WHEN {type} = 'Credit Memo' AND {createdby} = 'Joe Brand'
		THEN {amount}
	ELSE 0
END

With the above statement we will have a column that only shows values for transactions that are Invoices posted by me or Credit Memos posted by Joe Brand. When a line does not meet either of these sets of criteria we will get a ‘0’. You can add to criteria as much as you like using more AND as well as instructing further sets of criteria by adding further WHEN lines.

Using Todays Date in SQL Formula

When doing transaction searches, it’s fairly common to need to use a date parameter. To reference todays date in NetSuite Saved Search formula you can use {today}. You can try these criteria to begin with –

{datecreated} = {today}
{datecreated} != {today}

{duedate} < {today}
{duedate} >= {today}

This second pair of examples is great for looking at where perhaps a custom date field is in the past or the future. You could also use this to only display invoices that are past due or use a Formula (Text) field to give a very visual indicator to management that an invoice is ‘PAST DUE’. As a side note, if you want the number of days overdue, don’t forget you already have this with the standard field {daysoverdue}.

Here is a full list of your comparison operators –

  • Equal (=)
  • Not Equal (!=)
  • Greater Than (>)
  • Less Than (<)
  • Greater Than or Equal To (>=)
  • Less Than or Equal To (<=)
  • Not Less Than (!<)
  • Not Greater Than (!>)

As you start to grow your use of saved search formulas it will be useful for you to bookmark our NetSuite SQL Formula Cheat Sheet that gives you a thorough and detailed breakdown of the ten most common comparison operators and how to use them.

Similar Posts

2 Comments

  1. you don’t cover the scenario:

    CASE (??)
    WHEN (??) THEN xx
    WHEN (??) THEN yy
    ELSE zz
    END

    and especially if “WHEN (>= ??) THEN xx”

Leave a Reply

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