Item Tricks – NetSuite Saved Search Tips N’ Tricks (Part 2)

Things you did not know …?

In my previous blogpost Saved Search Tips N’ Tricks (Part 1) I explained how to use the Records Browser as a helpful tool when building sophisticated Saved Searches. In this blog I will demonstrate how to:

1. Expose Items on GL Impact page
2. Group Items on one Saved Search result line

Learn more about our NetSuite capabilities

Expose Items on GL Impact page

Issue

In a distribution or light manufacturing setting, the users frequently need to review the GL Impact page of a posting transaction to ensure that the item configuration results in a correct GL Impact when the transaction posts to the system’s general ledger.

NetSuite out of the box, does not typically show items related to the posting lines. This can be an issue when you are trying to decipher the cost of a finished good on an Assembly Build or when you are trying to determine COGS for a specific SKU on an item fulfillment transaction.

This is what the typical GL Impact screen will look like (i.e.: Assembly Build transaction)

As evident, the cost of building this product is bundled up into one lump sum amount (Amount – Debit) with no clear understanding of the cost breakdown of components used to build this item (Amount – Credit).

Solution

In case you did not know, 90% of NetSuite’s interfaces and pages are powered by Saved Searches. They are responsible for views, elements that you see within your dashboard portlets, results that you see on your forms, etc. Hence, we turn to Saved Searches to solve this conundrum.

1. Create a ‘Transaction’ Saved Search and expose the fields that you need to see in the GL Impact view. Below are some of the columns I found relevant for this example:
   • Document Number
   • Item
   • Quantity
   • Account
   • Amount (Debit)
   • Amount (Credit)
   • Department
   • Class
2. Run your Saved Search and you should get something that looks like this:
   
   
   
3. Here comes little ‘hocus pocus’! Navigate to the address bar of your browser, please don’t move away or refresh this page.
   
   Capture the id of this Saved Search and see the highlighted section of the image above. Save that id somewhere as you will need it in the next step.
4. Navigate to your transaction of choice (a posting transaction, preferably) and hover over ‘Actions’ and click on ‘GL Impact’.
   
   As it is shown in one of the images above, you will see a simple GL Impact page with insufficient or confusing information.
   Before
   
   
5. Now, let’s turn to the address bar again and try to locate the searchid As mentioned before NetSuite uses Saved Searches to present meaningful data to the user.
   
   Notice, the default ‘searchid’, replace this id with the one you recorded in step iii (don’t forget to delete ‘-‘ sign, you won’t need it). Now, hit enter on your keyboard.
   
   You should see a transformed view with all the SKU’s / Items listed for you.
   

Not bad, right!?

Learn more about our NetSuite capabilities

Grouping Items on one Saved Search result line

Issue

One of my clients was frustrated with a specific Saved Search problem. He called me to tell me that he could not figure out how to show all the items listed on the order without repeating the rows on a Saved Search results screen. He was getting this:

Solution

Upon doing some research, I discovered a wonderful hidden NetSuite method that resolves this issue. The method is to be used with grouped results only (summary type column of ‘Results’ tab). Before I get into details let’s see what the result of our Saved Search needs to be.

As you can see from the image above, we have combined individual result rows into one while still exposing all SKU’s that belong to this order.

The following are the ‘criteria’ and ‘results’ specifics that helped us achieve this.

1. Criteria
   

   Filter	Description
   Type	Is Sales Order
   Main Line	Is False
   Shipping Line	Is False
   Tax Line	Is false

    

    
2. Results
   

   Field	Summary Type	Function	Formula
   Document Number	Group
   Date	Maximum
   Name	Maximum
   Item
   Formula (Text)	Maximum		Replace(ns_concat({item}), ‘,’ , ‘<br>’)
   Quantity	Sum
   Amount	Sum

‘ns_concat’ will concatenate item id’s in one field. ‘Replace’ function will replace ‘commas’ generated by NetSuite with an html ‘<br>’ tag. Lastly, Formula text with its summary type ‘Maximum’ will expose only one instance of the concatenated (combined text) on the ‘Results’ page.

I hope these two tips were helpful for NetSuite Saved Search Formula. Please stay tuned for more blogs in this series. If you have any questions or queries, leave a comment below. You can also connect with our BOLDEnthusiasts by clicking here.