This article is relevant if you are trying to craft a NetSuite Saved Search and you need to get values from another list.
Background
In NetSuite, Saved Search is the key technology to give you something close to a SQL statement. The tool is pretty good but it does have some limitations that require some creativity to get around. One of them is to lookup data in one table based on the presence of specific data in another table that may not be linked.
A good example is to look up Invoice details where there are associated Credit Memos. The challenge here is that from the Invoice perspective, there is no link mechanism to the Credit Memo. Yet, from the Credit Memo perspective, there is an Invoice link in the “Created By” referenced. However, if you need the lines on the Invoice, you won’t be able to obtain these from the Credit Memo perspective through the Created By reference as it only links to the Invoice header.
If you are a SQL developer, you may be familiar with this clause:
select distinct name from Author where title in (select title from Book where genre = "romance")
This is a very powerful SQL lookup query that does not require tables to be linked. Here would be the same syntax in NetSuite if we were able to craft this in SQL (of course, we can do this if we have NetSuite’s ODBC system, but many NetSuite customers have avoided that investment).
select distinct from transactions where internalid in (select internalid from transactions where and type = "Credit Memo" and <your criteria>)
Here is an approach to get around this limitation.
Use Formula Criteria to Retrieve the Correct Data
The trick when working with NetSuite Saved Search is to remember that the underlying database is Oracle. The idea is that we need to craft some Oracle functions that give us what we need. In this case, the Oracle PL/SQL CASE statement becomes very handy. Use a Formula (Numeric) type where if the value returns a 1, you found a row.
CASE {internalid}
WHEN 59442 THEN 1
WHEN 66697 THEN 1
ELSE 0
END
Another consideration is that the Formula tool has a limitation in the length of the string information you can stuff into the user interface. You may need to break it up into multiple criteria references. See image for an example.
Summary
While not ideal, the key to getting more out of the NetSuite platform is be creative and don’t give up. We love to innovate on the platform. If you are looking for superior help with your NetSuite system, contact us.
Great timing, sir, I was just wrestling with this issue, and, wow, presto, your blog post. I love it when that happens.
Hi Jason,
Glad it was helpful. It’s really not ideal but a workaround. I was trying to think of a way to better describe this problem as the language is a bit obscure. How do you think of this issue?
Marty
Hi Marty,
Can I get the minimum start date for each distinct customer&item combination?
I am trying to figure out how many customers had each of our “items” at historical dates. In customer records we have the list of items customers have ever had their start and end date (if there is one) but each customer may have numerous lines with the same item each with a different start date.
I could get it if I could filter to only use the min start date for each customer&item combination or if I could go like this:
SELECT INMT.INCo,INMT.Material,SUM(LastCost) FROM dbo.INMT
JOIN (SELECT INCo, Material, Mdate=MIN(LastCostUpdate) FROM INMT
GROUP BY INCo, Material) a ON a.INCo=INMT.INCo AND a.Material=INMT.Material
GROUP BY INMT.INCo,INMT.Material
Thanks for any advice!
The SQL code above would have to be replaced with the correct fields, but that structure would solve my problem. Thanks!
If I’m running a transaction query and I need to pull information from the item level, how do I reference an item field? item.locationreorderpoint doesn’t work? Thank you
David,
Are you using a formula (text) field to help you see the syntax to get to the item sublist? Are you trying to jump through to a third table? If so, then a) try the new NetSuite Search tools available now in beta for 2018.2 or b) use our Content Renderer Engine which allows you to get as deep as you need.
Marty