Marty Zigman - The NetSuite Expert

Conversations with Marty Zigman

Certified Administrator • ERP • SuiteCloud

Prolecto Labs Accelerator Templates

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

ERP NetSuite Reporting Technical

Tags: , , , , , , ,

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.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

About Marty Zigman

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

Biography • Website • X (Twitter) • Facebook • LinkedIn • YouTube

6 thoughts on “How To: NetSuite Saved Search: Look Up Records from Another List

  1. Great timing, sir, I was just wrestling with this issue, and, wow, presto, your blog post. I love it when that happens.

  2. Marty Zigman says:

    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

  3. Leslie says:

    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!

  4. Leslie says:

    The SQL code above would have to be replaced with the correct fields, but that structure would solve my problem. Thanks!

  5. David Lyle says:

    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

  6. Marty Zigman says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *