CASE STATEMENT use case – Intercompany Reconciliation

Have you ever wanted or needed a quick comparative view of your Intercompany transactions between your subsidiaries?  One way to achieve this is through the use of CASE Statements in Saved Searches.  What is a CASE Statement?  A CASE Statement is similar to that of an if-then-else statement.  You may have encountered this in spreadsheets.  In NetSuite, it’s an available function that allows you to program conditions to return values when those conditions are met.  So, when a condition is true, NetSuite will provide the results based on those conditions.  You can have multiple conditions in one CASE Statement.

A use case for CASE Statements in NetSuite can be a report to show the transaction amounts between subsidiaries.  When there are receivables/payables between subsidiaries, the best practice would be to have Invoices and Vendor Bills.  And having the transaction numbers between those transactions match.  Assuming this best practice, the following steps will illustrate how you can have a simple report show you the Invoice Amount; Bill Amount; and Variance. 

1. Navigate to Reports > New Search 
2. Select Transaction
3. Click Create Saved Search
4. Add a Search Title.  Example: Intercompany Recon 
5. On the Criteria Sub-Tab (Filters) 
   1. 1. Add Main Line field.  Set to Is True 
      2. Add Posting field.  Set to Is True 
      3. Add Name field.  Add the Subsidiaries you want to compare 
         
         
   
   
   
6. On the Results sub-tab 
   1. 1. Remove all fields if necessary 
      2. Add Document Number 
         1. 1. And set Summary Type to Group 
      3. Add your first Formula (Currency) 
         1. 1. Set Summary Type to Sum 
            2. Set Custom Label as Invoice Amount 
            3. Set Summary Label as Invoice Amount 
            4. On the Formula field, type: 
               1. 1. CASE WHEN {type} = 'Invoice' then {amount} ELSE 0 END 
                  2. The above CASE Statement is saying IF the transaction type is an Invoice, then return the amount of the Invoice, otherwise return a value of zero. 
      4. Add your second Formula (Currency) 
         1. 1. And set Summary Type to Sum 
            2. Set Custom Label as Bill Amount 
            3. Set Summary Label as Bill Amount 
            4. On the Formula field, type: 
               1. 1. CASE WHEN {type} = 'Bill' THEN {amount} ELSE 0 END 
                  2. The above CASE Statement is saying IF the transaction type is an Bill, then return the amount of the Invoice, otherwise return a value of zero. 
      5. Add your third Formula (Currency) 
         1. 1. And set Summary Type to Sum 
            2. Set Custom Label as Variance Amount 
            3. Set Summary Label as Variance Amount 
            4. On the Formula field, type: 
               1. 1. CASE WHEN {type} = 'Invoice' then {amount} ELSE 0 END - CASE WHEN {type} = 'Bill' THEN {amount} ELSE 0 END 
      6. Also on the Results sub-tab, Sort by Document Number 
   
   
   
   
7. Save & Run and your results should look similar to this below:
   1. 1. You can quickly see where amounts are different, or potentially missing a transaction. 
   
   
   

I have posted another blog post that using a CASE Statement for Date/Period Mismatches.  You can find that blog here.  For more content or blogs regarding NetSuite How-To’s, go to https://blog.concentrus.com/.  If you need any help with customizing reports and saved searches, feel free to contact Concentrus.  Our consultants would be happy to guide you through the process. 

About Us

Concentrus is a complete NetSuite solutions provider that guides organizations through how to use NetSuite to reach highly focused business goals and objectives. We provide NetSuite implementation, developer, integration, and customization services to ensure that you have a long-term solution that is tailored to fit your systems, people, and processes.

Read About Our Implementation Methodology

Want more NetSuite Tips and Tricks? Check out our Short & 'Suite videos