Netsuite Aggregate CASE Values – Script Everything

If you want to show in your Saved Search results an aggregation of your results from a CASE statement then you will want to apply the Sum aggregation type in the Group section of your saved search field.

Let’s illustrate this through the use of an example.

For example, let us assume I want to find out the BALANCE of an Account on what has been charged for our customers and what remains pending in scheduled Sales Orders that have a future invoice date.

To perform this type of task I would create a Transaction Saved Search.

In the criteria area I need to apply the following:

Account     25XXX; 25XXX (Whatever you GL Account Number is)
Date        This fiscal year (Or any other date range or condition you would like)

Then in the Results area create the following fields:

Name                Group
Formula (Number)    Sum     ** see code #1 below **
Formula (Number)    Sum     ** see code #2 below **

As you want to cluster the results based on the customer name, we aggregate the data by grouping the Name field. Therefore, our next two fields will be the aggregate SUM of each customer according to the criteria above.

Our first CASE statement tries to calculate a balance by adding all the invoices created and subtracting any credits applied:

// CODE #1:
CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN
    ROUND( ABS( {amount} ), 0 )
WHEN {type} = 'Credit Memo' or {type} = 'Cash Refund' THEN
    ROUND( -1 * {amount}, 0 )
END

Notice how the CASE statement just returns the {amount} of the invoice/credit. Therefore, if we have the following transactions in our results:

ID	GL A/c	Amount
INV001	A/C 25XXX	-$200
CM001	A/C 25XXX	$200

As these transactions progress through CODE #1 case statements the results will be:

ROUND( ABS( -$200 ), 0 )
+
ROUND( -1 * $200, 0 )

With the result being:

ROUND( 200, 0 ) + ROUND( -200, 0 ) = 0

The next formula CODE #2 is a little more complicated, but is a good example of demonstrating nested case statements:

CASE WHEN {customer.status} = 'Active' THEN
    CASE WHEN {type} = 'Sales Order' and {status} = 'Pending Billing' THEN
        ROUND( ABS( {amount} ), 0 )
    WHEN LENGTH( {createdfrom} ) > 0 and {createdfrom.status} = 'Pending Billing' THEN
        CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN
            ROUND( {amount}, 0 )
        WHEN {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN
            ROUND( -{amount}, 0 )
        END
    END
END

Let’s take this CASE statement apart:

CASE WHEN {customer.status} = 'Active' THEN

Our first main condition is to determine if the Customer’s status field of the customer is they are still 'Active' .

CASE WHEN {type} = 'Sales Order' and {status} = 'Pending Billing' THEN

Our first nested condition for Customer’s who are active are to determine if the type of the Transaction is a Sales Order, and the status of this Sales Order is Pending Billing .

If this is true then we want to round the absolute value of the transaction’s amount .

WHEN LENGTH( {createdfrom} ) > 0 and {createdfrom.status} = 'Pending Billing' THEN

The next condition applies to the remaining transactions which are not Sales Orders that are still pending billing. Here we apply further nested conditions:

CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN

If the transaction is a Cash Sale or Invoice then we round the amount field.

WHEN {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN

Or if it is a Cash Refund or Credit Memo type of transaction then we want to apply the rounding of the negated {amount} field.

Then we make sure we’ve closed properly all our nested statements.

Conclusion

In this article you have learned how to aggregate saved search results by using the SUM group function and applying the Formula field with CASE statements to further refine your result set.

In our CASE statement formula you saw how you can apply nested CASE statements.

If you need something simpler you might want to read about the DECODE function.