How To: NetSuite Saved Search: Look Up Records from Another List

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.