Netsuite Aggregate CASE Values


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:

IDGL A/cAmount
INV001A/C 25XXX-$200
CM001A/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.

Ryan

Author of scripteverything.com, Ryan has been dabbling in code since the late '90s when he cut his teeth by exploring VBA in Excel when trying to do something more. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP. When he is not behind a screen, Ryan enjoys a good bush walk with the family during the cooler months, and going with them to the beach during the warmer months.

Recent Posts