Overview:

A lot of clients try to find ways to look at their sales or purchase transactions so they can get a 360-degree view of their progression. In the case of a sales order, they want to see how much was billed and of that how much has been paid or is still outstanding. In the case of Purchasing, they want to see what was purchased and how much has either been received or billed.

With saved searches, you can easily get a list of transactions of a certain type and display a record for each, however, there could be multiple invoices per sales order or multiple receipts and bills per Purchase Order. These records in NetSuite tools would display as separate records in the results of the saved search, not to mention the purchase transactions and their related bills would not display consecutively in the results. This would make it difficult to reconcile them quickly without the use of exporting into a program such as Excel.

Solution:

What if you want to see the purchase transactions and their related billing totals collapsed into a single record? What if you want to also make sure that the purchase transactions are included in your results even if there aren’t any related billing transactions yet?

This is where we can use a combination of saved search formulas and summary functions to retrieve all the pertinent transactions and relate them so that they display in a single record in our results view. This post is going to demonstrate how to create a transaction saved search so that we can see all Sales Order transaction amounts from the prior month and their related Customer Invoice amounts in a single record output for each.

Criteria:

1. Filter the results so that the transaction types are limited to both Sales Order and Invoice.
2. Set the Main Line value to True so that the search only looks at the transaction body values and not the item detail lines.
3. We will add a Formula (Numeric) filter that contains a Case Statement formula which checks the following conditions and if the conditions are true, they are included in the results:

Case When ({type} = ‘Invoice’ and {createdfrom.type} = ‘Sales Order’) Or {type} = ‘Sales Order’ then 1 Else 0 End

• If the transaction type is an invoice, then we only want to see invoices where it was created from a sales order.
  • If the transaction type is a Sales Order, then we want to include it always.

• The last filter is a Formula (Date) where we are filtering for the following in order to include the transactions:

Case When {type} = ‘Invoice’ Then {createdfrom.trandate} Else {trandate} End

• If the transaction type is Invoice, then check the date of the transaction that created it (Sales Order) and make sure it is within the previous month.
  • If the transaction type is a Sales Order, then make sure that the date of the transaction is within the previous month.
  • The date range in this filter can be changed to and relevant time frame that you would wish to use, such as this year, this month or last year.

Results:

The results tab will define the fields we want to include as our output for the saved search. In this particular type of search, which is referred to as a Summary search, we will use a combination of Formulas and Summary Types in order to create a direct relationship between the two transaction types and then group them so that the results display all related transaction values in a single result row.

1. Formula (Text)
   1. This inspects the transaction type in the formula to do the following:
      1. If the type is an Invoice, then we will get the Document Number from the sales order it was created from.
      1. If the type is a Sales Order, then we will use the Document number from that specific transaction.
      1. Using the summary type of GROUP allows us to collapse the results into a single field for both transactions.
2. Formula (Text)
   1. This inspects the transaction type in the formula to do the following:
      1. If the type is an Invoice, then we will get the Document Status from the sales order it was created from.
      1. If the type is a Sales Order, then we will use the Document Status from that specific transaction.
      1. Using the summary type of GROUP allows us to collapse the results into a single field for both transactions.
3. Formula (Date)
   1. This inspects the transaction type in the formula to do the following:
      1. If the type is an Invoice, then we will get the Transaction Date from the sales order it was created from.
      1. If the type is a Sales Order, then we will use the Transaction Date from that specific transaction.
      1. Using the summary type of GROUP allows us to collapse the results into a single field for both transactions.
4. Formula Type (Currency)
   1. This references the Total Amount of the transaction(s) if they are Sales Orders.
   1. The SUM summary type to roll up all values from all transactions.
5. Formula Type (Currency)
   1. This calculates the difference between the total summed amount of invoices and the sales order they were created from.
   1. The SUM summary type to roll up all values from all transactions.
6. Formula Type (Currency)
   1. This references the Total Amount of the transaction(s) if they are Invoices.
   1. The SUM summary type to roll up all values from all transactions.
7. Formula Type (Currency)
   1. This references the Total Amount Paid for all Invoices.
   1. The SUM summary type to roll up all values from all transactions.
8. Formula Type (Currency)
   1. This references the Total Balance Due for all Invoices.
   1. The SUM summary type to roll up all values from all transactions.

If you faced any issue or have a query? Feel free to connect with NetSuite experts, they will be happy to help you in every way possible.

Sorting:

In the results tab, we set the Sort By field to reference the Formula (Text). This will sort all of the results in the search output by the Sales Order #, since that is our first field that uses this Formula Type.

Conclusion:

Once we give this saved search a Title and Save & Run it, the results display a list of the Sales Orders from the previous month, their amounts, and the related amounts for any invoices that were created from them.

As was mentioned at the beginning of this post, this same type of summary search can be created using Purchase Orders, Item Receipts, and Vendor Bills to gain a 360-degree of the Purchasing transactions as well. To achieve this, you would just need to change the transaction type references in the Criteria and in the Formulas, and Voila!

About Us:

We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience.  We specialize in implementation, optimization, integration, rapid project recovery and rescue as well as custom development to meet any business need. If you would like more information on NetSuite or are in need of consultation for your project or implementation, feel free to contact NetSuite support.

To Contact Us Click Here