Learn NetSuite Two Dot Saved Search Notation for Distant Joins

This article is meaningful if you are looking to get to NetSuite hard to reach saved search information that is through a table join.

Background

It is well known through the community that the NetSuite Saved Search technology only allows for a single join to get to related tables. I wrote about this in in 2012 in my article, Solve Distantly Related NetSuite Saved Search Join Challenges.

More recently, Boban, one of our Senior Consultants, wrote to me to share that he discovered some syntax that gave him a bit more saved search reach. This helped him solve a specific saved search problem related to duplicate records. I too stumbled on this discovery with our Content Renderer Engine but did not link the concepts tightly in mind until Boban shared his thinking.

While the new SuiteAnalytics Workbooks show great promise, Saved Search is here to stay and taking care of many day-to-day challenges. Thus, this article may help the NetSuite Administrator seeking to get a bit more query capacity.

NetSuite Saved Search Custom Formula Join Syntax

The general way we use custom formulas to produce join syntax looks as follows:

{joinfield-recordname.reference-field}

Hence, we are accustomed to understanding that the “dot” is the indicator to produce the join.

NetSuite Saved Search Two Dots for Joined InternalID Syntax

However, NetSuite appears to offer one more dot to get to the internal ID of a record when the target record is a list type of yet another record.

Thus, this syntax appears to be supported:

{joinfield-recordname.reference-list-field.id}

If you click on the images, you will see the concept in play. In the illustrated example, the target custom record definition has the “name” portion turned off. So the conventional syntax returns the internal ID. However, if you look carefully at the syntax, we are able to get more reach with the extra “.id” syntax

Finally, I tried to reach to other fields without luck. My suspicion is that we can get to more information — it simply is not documented.

Never Stop Learning When it Comes to NetSuite

When working with NetSuite, it’s a good idea to be open-minded to be willing to learn new things. It’s not so good to be too certain around the platform — better to ground your thinking with actual examples and be a bit daring to do unconventional approaches in the attempt to get things done.

If you found this article helpful, feel free to subscribe to notifications of new articles. If you have thinking about better NetSuite saved search challenges, feel free to reach out to me for a conversation.

Be Sociable, Share!

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

| Category: NetSuite, Reporting | 11 Comments

11 thoughts on “Learn NetSuite Two Dot Saved Search Notation for Distant Joins

  1. David says:

    One instance of this two-dot join that I use is for subsidiary.
    When I’m creating a criteria formula (to account for duplicates) I don’t have to worry about hierarchy vs non-hierarchy naming (as I’m comparing the ID, i.e. “{inventorylocation.subsidiary.id}”)

    The same would apply for entities (like customers), where relying on names for criteria would be hazardous (for example from a sales transaction record you can get “{customer.parent.id}”)

    Great post, thanks

  2. Marty Zigman says:

    Thank you David. It’s good to learn about other use cases.

  3. Tiny typo’ above:
    it says “to product join” where I think you meant “to produce join”

  4. I need to do this:

    Sales Order -> Item Fulfillment -> Which Invoice did that Item Fulfillment land on

    Does the dot syntax work in reverse, meaning:

    invoice.fulfillment.order

    ?

    Thank you so much. As always.

  5. Marty Zigman says:

    Thank you Jason!

  6. Marty Zigman says:

    Hi Jason,

    My understanding of NetSuite is that you can’t figure out the invoice which was actually as a result of an item fulfillment. See these articles where I talk about it and how we solve it for our clients:

    https://blog.prolecto.com/2016/04/11/how-to-produce-additional-transactional-financial-information-when-learned-late-in-process/
    https://blog.prolecto.com/2019/05/26/best-practices-for-automating-netsuite-invoice-generation/

    Marty

  7. Ryan Price says:

    Hi Marty

    I have a transaction search returning a Sales Order as the primary record and also data from the applying Cash Sale. I am also trying to pull data from the Payment Event record attached to the Cash Sale.

    The search needs to pull the Sales Order as the primary record.

    I’m trying to use the two dots approach to get at data in the Cash Sale’s Payment Event record, but not getting there.

    Do you have any thoughts on whether the approach you outline in this article can work in the scenario I have described?

    Many thanks in anticipation.

    Regards
    Ryan

  8. Marty Zigman says:

    Hello Ryan,

    I suspect the two dot approach won’t work here. You have options. 1) use CASE WHEN {type} syntax and {createdfrom} elements to help you assess what you need. 2) Try to use the Workbooks feature to get the reach 3) at worst, depending on what you are trying to achieve, you can link saved searches together with our Content Renderer Engine.

    Marty

  9. Jeremy says:

    This doesnt work now.

    I tried to get the name field internal id in my deposits record Payment line. it giving me an error >
    Error

    An unexpected error has occurred. Please click here to notify support and provide your contact information.

  10. Marty Zigman says:

    Jeremy,

    You may need to experiment with the syntax to get it right. There is no guarantee that this technique will work. But most of the time, it does.

    Marty

Leave a Reply

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