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.
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
Thank you David. It’s good to learn about other use cases.
Tiny typo’ above:
it says “to product join” where I think you meant “to produce join”
Thank you Jason!
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.
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
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
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
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.
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
[…] lamentablemente, una búsqueda guardada no admite uniones de segundo nivel como esta. Para ver esta publicación de Prolect para obtener información sobre una solución […]