Pulling Sales Orders with Related Transactions in Search and SQL

Sometimes, you need to pull sales order information and also include the estimate and/or invoice, and sometimes even an item fulfillment. For advanced users, it is very easy to build such search, but it may be more complicated than it sounds if the advanced user is not available.

Using Saved Search:

Disclaimer: Due to how transactions and transaction lines in NetSuite are linked, you will get duplicate lines even after following many of SuiteAnswers articles, especially if you are trying to show many different linked transactions in our search. My instruction below returns the best result in my personal opinion. Also, please note that this method will NOT work if multiple transactions of the same type are associated with the sales order.

1) Go to Reports > Saved Searches > All Saved Searches > New (or your preferred method to get to New Saved Search screen)
2) From New Saved Search screen, select Transaction.
3) Under Criteria tab, add the following filter - you can also add Main Line = T or F, depending on the type of information you are trying to extract, but keep in mind that estimate information is at header only, created
   Type = Sales Order
4) Under Results tab, add the necessary fields, and you can add the fields listed below for Invoice, and Estimate information:
   For Invoices:
   Billing Transaction
   For Item Fulfillment:
   Fulfilling/Receiving Transaction
   For Estimate (you can use one of the two):
   Applied To Transaction (Will not work if Main Line = F is set under criteria)
   Created From (will work regardless of criteria)
5) Adding an Item Fulfillment column is little more tedious than it seems. NetSuite offers "Fulfilling/Receiving Transaction"field, but it also pulls all other types of transactions (i.e. Invoice) that is associated with this sales order, duplicating every line. I am not sure if this is a defect, but I recommend using Formula (Text) field:
   Formula (Text) >  Formula: CASE WHEN {fulfillingtransaction.type} = 'Item Fulfillment' THEN {fulfillingtransaction} END
6) Because how  transactions  to how each line is linked, we will need to aggregate the result fields. I recommend summarizing them with "Maximum"

If you are familiar with SQL, it is probably easier to obtain such information, except one pain point - because all transactions regardless of the type are stored in one huge TRANSACTIONS table, you will need to join the TRANSACTIONS table to TRANSACTIONS table. Below is a sample SQL that I would use.

SELECT t.tranid AS 'Sales Order #'

       ,e.tranid AS 'Estimate #'

       ,i.tranid AS 'Invoice #'

       ,f.tranid AS 'Item Fulfillment #'

FROM TRANSACTIONS t

    JOIN TRANSACTIONS e ON t.created_from_id = e.transaction_id

    JOIN TRANSACTIONS i ON t.transaction_id = i.created_from_id AND i.transaction_type = 'Invoice'

    JOIN TRANSACTIONS f ON t.transaction_id = f.created_from_id AND f.transaction_type = 'Item Fulfillment'

WHERE t.transaction_type = 'Sales Order'

I hope this post helps you with transaction reporting reporting issues you may have at your organization. What are some of the other key challenges you encounter from day-to-day usage of NetSuite? I'm waiting to hear from you!