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)

GL Impact screen

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:
    Run your Saved Search
  3. Here comes little ‘hocus pocus’! Navigate to the address bar of your browser, please don’t move away or refresh this page.
    hocus pocus
    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’.
    Actions
    As it is shown in one of the images above, you will see a simple GL Impact page with insufficient or confusing information.
    Before
    simple GL Impact
  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.searchidNotice, 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.default searchidYou should see a transformed view with all the SKU’s / Items listed for you.transformed view

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:

Saved Search results screen

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.

Saved Search Solution

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.