Oracle NetSuite and Saved Searches
Oracle NetSuite is one of the most used ERP systems in the world with powerful resources and features. In this article, I’m going to show you 3 simple ways you can use Saved Searches that will save you lots of time.
One of the things that I like the most about this ERP, is the possibility to create and develop specific-tailored saved searches in its core. This gives add to users freedom in interpreting their data in the way they like the most. Summaries or Ledgers which ones suits you the most?
Introduction
Oracle NetSuite is one of the most used ERP systems in the world with powerful resources and features. In this article, I’m going to show you 3 simple ways you can use Saved Searches that will save you lots of time.
One of the things that I like the most about this ERP, is the possibility to create and develop specific-tailored saved searches inside its core.
What’s best than empoweringa saved search and have everything under control?
Personally, I’ve spent entire days working with the saved search engine, and here is where people get really frustrated. However, I didn’t stop at the first problem but I kept studying and trying and most of the time, I eventually succeeded. Here’s my humble promise: like most of the other things, if you keep trying, you’ll get your stake in return. That’s guaranteed.
The only real thing that you need to know before starting is that like for every ERP, data and transactions are stored in tables. Those tables are linked together inside the database engine and if you learn how to leverage those links, you’ll be able to create powerful reports and searches to get up-to-date snapshots of your supply chain in no time.
And now, nothing else is required from now on, so, ready to dive in?
Case#1: The Batch/Lot Tracker Issue
What happens if you have a faulty batch, and you want to track where some of the faulty products have been used?
This was one of the first challenges I had to face in my current job. As I work in a Soil Association certified company, the SA auditor was looking for a robust end-to-end Batch/Lot tracking system across our entire Supply Chain. I bet that having something like this will save you time, money, and lots of headaches.
This Saved Search uses the main table “Transaction” to retrieve two crucial information:
- where the faulty batch has been used in your manufacturing process
- related document numbers linked with its use.
So, once you headed over Report > new saved search> scroll all the way down to transaction and smash “Transaction” button.
Now click on “more option” to personalize the search and select these two criteria as per my picture below:
Then, move to the “Results” tab where you want to include what’s relevant for you to analyze, more specifically:
- Document Number (this will be used to track the entire flow of documents).
- Date: (Will be used to ordering chronologically the result for a better overview).
- Type: (To show the type of processes in which the lot number was used).
- Name: (Eventually, it will be populated with your customer’s name).
- Quantity.
Finally, head to the “Available Filters” subtab and add and check the box under “Show in field region” for the following two:
- Item
- Transaction Serial/Lot number
And here the results:
Say that LOT 15038 is your faulty batch, under documents 2248 and 2249 you can see two assembly builds. This is where the batch has originated since it’s where it has been assigned and used for the first time.
Following the assemblies, we can see eight inventory transfers, two Item fulfillment, and finally, a Cash Sale that represents the end of the flow.
Interestingly, you might want to run this report for any of your lot item components or assemblies, simply by using the available filters just above the results. You’ll be able to see all the way down to their end-to-end usage across the entire Supply chain, from the original purchase order to you’re cash sale/invoice raised against your customers.
Case #2: Orders with Backlog reminder
What if I ask you to tell me in 10 seconds which purchase orders are carrying backlog?
Even though NetSuite offers some standard searches by default, this simple question makes everyone drive crazy, mainly for two reasons:
- There are too many “dates” in the system, and no one knows which one is the one you should look at.
- Your company’s organization has several subsidiaries and/or several locations and this makes things more complicated to read.
Facing both problems at the same time, I found a solution applying two “Formula (Numeric)” in one of my saved searches.
As I was starting from scratch, the first step to tackling this challenge down was identifying the main table I needed to use.
Even though it might be your first idea, you will be disappointed in realizing that there is no Purchase order table when looking at your saved search options.
So, what is the most suitable starting point this time? Well, you might think to rephrase the question this way:
“In my Purchase Orders, is there any open transaction that is also past due?”
Under this point of view, you might have guessed that the answer features the “transaction” table once again.
So, head to Reports > New Search> select “transaction” and then more options to customize the report.
Now, after adding all the other standard conditions you’ll see below, let’s work out the final criteria, this time using a Formula (Numeric) function:
Please note that with the following Formula (Numeric) conditions
{quantity}-{quantityshiprecv} >0
you’ll see in return any POs for inventory items still open (I chose everything but “fully received”) where this formula is satisfied:
This is in order to include not just fully open orders but also partially received POs.
Please, consider whatever is relevant for you in this instance to make your query more suitable for your case.
Now that you’ve sorted the first bit, let’s talk about “Dates”.
In 99% of the cases, two are the dates you’ll need to use in your POs queries:
The field “Date”, which refers to the date the transaction was created in the system (Format DD/MM/YY), and “Expected Receipt Date” which is the date that the system uses as the date you will receive the goods. To make it clearer, the date you use in this field will be used as the date your goods will be available in your warehouse to be processed.
Now, going back to the search, you’ll definitely need:
- Document Number
- Item Name
- Date
- Expected Receipt Date
- And maybe Amount
Also, we will be using another Formula (Numeric) as per below:
ROUND(({expectedreceiptdate}-{today}),0)
leveraging the {today} NetSuite’s function that returns today’s date, you’ll always have an up-to-date report. Finally, by rounding up the formula ({expectedreceiptdate}-{today}) to zero decimals, the system will return how many days the open line is late compared to its original expected delivery date.
This will return a pretty cool and self-explanatory report as per below:
Time to ring your suppliers!
Case #3: The Cumulative Sum Challenge
Standard Saved Searches are good but what if we want to add more details to it?
Here’s the last challenge: how can you show into a transactional report or sublist a cumulative total?
This was definitely one of the things that I wanted the most, in too many cases I was finding myself using my good old calculator. Here are some examples:
- What’s the total demand for this item, in this specific time bucket?
- What’s the total outstanding amount for that specific supplier?
- What will be my stock on hand balance, for this ingredient two weeks from now?
I’m sure you’ve come across these questions as well, at least once in your life.
Since life in Supply Chain is measured in “weeks”, having this information automatically from a saved search, seemed to me, a real time-saving solution, especially if you plan your activities in buckets.
I was desperate to find a solution and eventually, I found one on the web thanks to Marty Zigman’s post, who deserve the credit. Here you can find the original post.
So, wanna see how it looks like?
Scenario
My final objective was to have the cumulative demand for one of my selling items, along with its weekly demand, all in one single Saved Search. This doesn’t come along by default with standard saved search in NetSuite, as the system doesn’t aggregate the quantities but shows only the demand for the relative week (or month), with no running total.
Here is what I mean by that and to make it more clear, let’s define our bucket equal to the entire Q4, from 01/09/2021 to 31/12/2021:
Even though the total amount is shown at the very bottom, it might be the case that you want to break down the entire Q4 quantity in more than 1 production run, so it would be perfect to have an additional column showing the cumulative sum of the weekly demands. The picture below will help you to understand better what I mean:
The solution to this was made possible by changing the way the system is using the SUM function in a non-aggregate search.
In fact, using the comment in the formula field, NetSuite is no longer thinking that you are performing a search and passes the string to the next statement, which is actually the one doing the job.
Here is the structure of the formula you want to replicate in this or a similar scenario:
sum/* comment */({quantity})
OVER(PARTITION BY {item}
ORDER BY {demanddate}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
So, now that you know the solution, head over to your new saved search button, and include the following field in the results subtab:
- Demand Date
- Item
- Location
- Quantity
Then add a “Formula (Numeric)” field with the formula just mentioned:
sum/* comment */({quantity})OVER(PARTITION BY {item} ORDER BY {demanddate}ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
Remember to show on your “Available Filters” the fields “Item” and “Demand Date” to allow you to sort your query by item and bucket if you need.
Here is how the result looks like.
Note: you might want to apply this to similar case studies and perform your cumulative total using different fields. Here is how you should rearrange the fields keeping the same format in the Formula (Numeric):
sum/* comment */({field that you want to sum})
OVER(PARTITION BY {Field that you want to group by}
ORDER BY {Field that you want to order by}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Conclusioni
In this post, I’ve shown you 3 Powerful ways of using NetSuite Saved Search that will save you lots of time. I’m sure that you’ve already come across some of these and, if you haven’t, you will surely one day. So, you’ll remember this post and you will already know how to take these challenges down.
And you, what kind of challenges have you solved using Saved Searches?
Marco Maria Peli
Founder and Developer at Auron Digital
