This article is relevant if you need to work with a NetSuite Saved Search or Mass Update and you want to provide a list of data, internal ids, or information to drive your query.
Background
When working with NetSuite, sometimes we encounter situations where the information we need to filter with is provided by an external list. Suppose you have a list of internalids offered in a spreadsheet and now you want to use that list to lookup records. In a recent case, I needed to write a custom mass update for my client to change the values on a number of Sales Orders. They had the record internal ids for the Sales Orders in a spreadsheet. Internalids are very valuable in NetSuite search operation.
NetSuite Saved Search provides the foundation for performing a query on the business platform. NetSuite’s Saved Search is similar to a SQL SELECT statement. One of the great things about SQL is that you can perform a query where you provide a list of values as your where clause criteria. For example, “SELECT * FROM orders WHERE internalid IN (2978, 3105, 3086)” will retrieve the records where the internalid matches on the three values. But how do you perform the equivalent in NetSuite?
Use a Spreadsheet to Craft a NetSuite Formula
Since NetSuite sits on top of the Oracle database system, you can use PL/SQL (Oracle’s Database Programming Language) to generate specialized queries. Using some simple spreadsheet formulas, I was able to quickly craft the foundation of a CASE statement. The CASE statement can be used to act like a SQL IN operator. See image for the basic syntax.
Once I have crafted a portion of the CASE statement, I can cut-and-paste to my notepad editor and add the starting and ending syntax elements. See below for a sample.
CASE {internalid}
WHEN 2978 THEN 1 WHEN 3105 THEN 1 WHEN 3086 THEN 1 WHEN 3099 THEN 1 WHEN 3113 THEN 1 WHEN 3114 THEN 1 WHEN 3102 THEN 1 WHEN 3103 THEN 1 WHEN 3109 THEN 1 WHEN 3116 THEN 1 WHEN 3118 THEN 1 WHEN 3120 THEN 1 WHEN 3123 THEN 1 WHEN 3124 THEN 1 WHEN 3126 THEN 1 WHEN 3127 THEN 1 WHEN 3128 THEN 1 WHEN 3129 THEN 1 WHEN 3130 THEN 1 WHEN 3131 THEN 1 WHEN 3132 THEN 1 WHEN 3133 THEN 1 WHEN 3135 THEN 1 WHEN 3136 THEN 1 WHEN 3137 THEN 1 WHEN 3138 THEN 1 WHEN 3139 THEN 1 ELSE 0 END
Once you have the CASE statement, you can feed this string to a Formula (numeric) Filter and check for the value of 1. See screen shot for approach.
Get More out of NetSuite
The key to understanding the power of NetSuite Saved Search is to visualize that the Query tool is crafting a SQL query to the Oracle database. Hence, if you are comfortable with these concepts, you can drive the tool to produce results that may otherwise be challenging to obtain. Use these types of techniques to produce your Mass Updates. If you would like to work with professionals who have superior capacity to think and act with the NetSuite business platform, let’s have a conversation.
Hi Marty,
I use a similar trick but as a variation, if you use INSTR and search for the record ID within a list of the IDs you want, you can get more IDs within the character limit. All you need is a list of the ID values, split by a delimiter on both sides so that you don’t pick up partial IDs (so that looking for 9 doesn’t get a hit on 99 for example). Eg. INSTR(‘$123$124$127$129$134$’,’$’||{internalid}||’$’) is greater than 0
Hi Will,
That’s an interesting approach and didn’t consider it. The delimiter approach is cool as well. Thank you. Makes me now wonder if we could leverage this PL/SQL Function: REGEXP_COUNT(source, regexp, position, modes)
You could just use a regular PL/SQL “IN” operator –
CASE WHEN {internalid} IN (...) THEN 1 ELSE 0 ENDHello Michoel,
Now that would be even easier! I need to give that a try as using the IN clause was my first attempt.
Marty
I do the same thing with ANY:
CASE WHEN {internalid} = any(1111, 2222, 3333) THEN 1 ELSE 0 END
Nice Jeff! I like yours as it is easy to understand!
Marty
Is there a limit to how many internal Id’s you can look up at once? Character limit in the criteria field?
I suspect there is and it probably is a function of the size of the “formula” text. But I don’t know what the limit is. Let us all know what limit you find.
Marty
You can use an “OR” expression between criteria to group the IDs in to chunks that fit within the character limit – if there is a limit on the number of criteria lines,I’ve never come across it as the search will time out/not run long before you could reach it.
Great suggestion Will.
Marty
Hi Marty,
This is the great approach to filter out the NetSuite record based on InternalID, but NetSuite formula field has its own limitation of 1000 characters only.
Any suggestion on this.
Thanks
Hi Ravishanker,
Wouldn’t we go into an expressions and use ORs to express criteria chunks?
Marty
Hi,
Great trick! But can I use filters in script with multiple internal id ? If yes ,Can I know how?
Hi Marty,
is there any way to use this method with “Item Number/SKU”, when I try (and change the {internalid} with {name} I get an error.
Yes, you should be able to. But you may need to reference the right field. Depending on how you are getting at the information, you may need to learn the path. I typically use the formula builder to lookup the field I am interested in to get the fully qualified field name.
I have a task with over 8,000 internal IDs to work with. So a big job to chunk down into sets of 1,000 characters. Is it possible to refer the formula to a text file containing all the IDs? And thanks for the article; has helped me a lot.
Hi Stephen,
The only way I know how to work with this via a file is to use SuiteScript to read in the value and then dynamically craft the search. So, not too easy.
Marty
It’s 2022 and this page never fails to help me out.