This article is appropriate if you are using NetSuite and you are trying to join information together that isn’t readily pre-linked.
The Distantly Joined Saved Search Problem
NetSuite Saved Search is effectively a query tool on top of SQL queries to the database. Once you understand how Saved Search is working behind the scenes, you can invent new ways to do things that appear impossible. If you feel stuck getting the NetSuite saved search tool to produce results you are looking for, there may be times that you need to invent criteria that is not so obvious. I like to think of it as “taking matters into your own hands”.
Take the case where you are seeking to join two tables that not already naturally linked together. For example, in the NetSuite Advanced Projects Module, there are three important structures that are used to drive resource based billing and costing.
- Project Tasks: Define a specific task structure for a project. Multiple people can work on a task and it is an element for budgeting and costing a project.
- Project Task Assignments: determine who is going to work on your specific task (and hence project), the number of hours, the price and the cost rate.
- Time Entries: the actual work performed on the project by each project resource.
During time entry by project employees (resources), NetSuite conveniently copies the price from the Task Assignment for the specific task worked on. But what if you want to know the cost for that time sheet entry? The only way to get the cost is through the Project Task Assignment structure.
The challenge now is to relate the time entry information to the project task assignment. There is no native way in the NetSuite saved search tool to get the Project Task Assignment from the Time Entry table. However, if you start your saved search with a Project Task , you can get to both the Time Entries AND the Project Task Assignment. Yet, the tool does not allow you to join these two child tables together.
The Solution to Join Two Child Tables Together
From the perspective of the Project tasks, the Time Entry and the Project Task Assignment structures are children. The two child tables share an employee ID. The trick now is to compare these based on the employee ID. When NetSuite brings back a child record based on ID, it brings back the name. Hence we will be working with strings. In order to produce a join, we develop a criteria based Formula (Numeric) to use a function called “instr”. These formulas are based on NetSuite’s underlying Oracle database and
here is a reference to the “instr” function use.
The instr function will return a number greater than zero if it gets a match comparing the employee referenced on the time entry with the employee referenced on the Project Task Assignment. With these elements as criteria, you have effectively joined the tables together. You can then safely pull fields from the both child tables and you won’t get duplicates (which means that the two child table information is now tied together and effectively one). Click the image to get a full view on what the join condition will look like.
Let me know of other NetSuite Saved Search situations you have solved for using this valuable technique.
Well, done, sir, well done.
Thanks.
Thank you Jason. Sometimes you have to innovate to get NetSuite to produce the data the way you want to see it. I have a bunch of ideas where I see opportunities to improve the reporting area. It would be good to enumerate this thinking in a new article.
Marty
Hi Marty,
I´m with this problem, but not at in a search, it´s in a field of a custom record.
My case:
I have a custom record named JOB and at I have 2 child custom records.
A – Services required
B – Services Available
the relation of JOB with A and B is by JOB ID.
At B, services availabe I will choose services from another record and I have a field with how many of that service is available.
At A, I have all services that the JOB requires and the quantity of it.
I want to create a field that will sum how much of this service I have in B.
DO you know how to do it without scripting?
Bruno,
I think I know what you are getting at. If you provide a screen shot of the structure, it will help. Have you tried a Summary function and grouping on the Job ID?
Marty
Hi Marty
Good article. Have you gad any success in creating a search based on Quotas and Sales? For such a basic requirement, I find myself pulling teeth to achieve this. I have even created a custom record called Sales_Budget (rendering the pointless Quotas obselete) with a Parent Record called Region which is also a parent of Transaction but with no joy!! I am pulling my hair out….
Hi Liam,
We should setup a GoToMeeting so I can look over what you are doing to offer suggestions. By any chance, are you going to SuiteWorld 2013? If so, we can connect up and review it there. If not, can it wait until the following week?
Marty
Hi Marty
Thanks for the response. Unfortunately I won’t be attending SuiteWorld this year but a GoTo after your return sounds ideal if you have the time.
Enjoy SuiteWorld and hopefully we’ll speak soon…
Liam
Hi Liam,
Let us know what the status is on your concern.
Marty
Great article.
Netsuite’s reporting is very poor. We should be able to join tables.
Any idea how to get a quote number onto a payment receipt for an invoice?
There is a very strange situation where one particular product gets paid directly from a quote and the invoice is created after. The customer never sees the invoice only a payment receipt that references the invoice number. We’d like to also put the quote number on so they can match to their documentation.
Thanks!
Hi Robert,
I would not say that the reporting is very poor. Of course, it has limitations and it is important to work around those constraints.
I am not sure I fully understand your use case. But here is what I can say.
Many times, we use custom fields to be able to “reach” information that a NetSuite feature can’t see. In this case, we would write some SuiteScript that would bring down the quote information to the payment record. Depending on the timing of the transactions, we might hook this up to the AfterSubmit event so that data is always there when we need it.
Other times, we run a nightly custom Mass Update to do all kinds of things like this to move around or produce data.
Let me know if this makes sense. One of our consultants can help you write these scripts if you need professional help.
I’m not sure I fully grasp how this eliminated duplicates.
With our support cases we track our time to a case. We also created a custom record type to enter time estimates (by employee) for assiging workloads.
What I want to do is a search that compares estimated time to actual tracked time by employee for each case.
I am starting the seach from the Case records and joining in our estimate and actual time results. The issue is that the results double up. Here is a results example that only looks at employee name and internal ID for each record:
estimate: emp estimate: id time: emp time: id"Reiter, Brad" 2 "Reiter, Brad" 1146093
"Reiter, Brad" 2 "Reiter, Brad" 1145780
"Reiter, Brad" 4 "Reiter, Brad" 1145780
"Reiter, Brad" 4 "Reiter, Brad" 1146093
"Weller, Gary" 6 "Reiter, Brad" 1145780
"Weller, Gary" 6 "Reiter, Brad" 1146093
"Wolf, Jeffrey" 7 "Reiter, Brad" 1145780
"Wolf, Jeffrey" 3 "Reiter, Brad" 1145780
"Wolf, Jeffrey" 3 "Reiter, Brad" 1146093
"Wolf, Jeffrey" 7 "Reiter, Brad" 1146093
Below is what I’d actually like returned (in this example)
estimate: emp estimate: id time: emp time: id"Reiter, Brad" 2
"Reiter, Brad" 4
"Weller, Gary" 6
"Wolf, Jeffrey" 7
"Wolf, Jeffrey" 3
"Reiter, Brad" 1146093
"Reiter, Brad" 1145780
Is there a way I can filer out record by record type? IE:
CASE WHEN recordtype=estimate then {estimate.employee} END
Ah I figured it out! The DISTINCT function has given me what I needed.
Hi Brad,
Glad you figured it out. Just for other people’s references, here is a suggested way to find duplicates:
https://blog.prolecto.com/2012/10/18/how-to-find-duplicates-in-netsuite/
Marty
I have a custom record in netsuite with several fields. One field is transaction date, another is Amount (number) and the third one is currency (USD, CAD, etc). I would like to calculate in another column the USD equivalent of the amount field based on the currency. For example 1/1/2016 CAD $1000 and from those three fields in the custom record, I would like to put the value in a fourth field that would convert the $1000 CAD to USD using the exchange rate for 1/1/2016. I have a saved search that gives me the exchange rate for each day, any idea how I could use a formula or link 2 saved searches to convert all the data to USD? I would like to avoid a script if possible.
Hi Kevin,
If you are searching on the transaction, naturally you have the exchange rate (I assume your base currency is USD). Thus easy to get the data out. To join to the currency exchange rate table for a specific date will require two searches as NetSuite doesn’t offer up the “reach”. We have solved these types of reporting challenges with our Content Renderer Engine. It is available license free.
Marty
Hi Marty,
Is it possible to join records in the results of a saved search, We are trying to get “Quantity” {quantityavailable} of a “Member Of” {memberof} Item but so far we haven’t been successful.
Thanks
Hi Shaye,
It sounds like you are trying to get the transactional or inventory information. The ‘member of’ is really about the bill of materials in item groups / kits or assemblies. These days, we solve linking saved searches with our Content Renderer Engine and then draw reports or html. You can also see this article.
Marty
I found this blog and your methods are clever. I have hopes that you have figured out UNION in NetSuite? I am trying to join the contacts of customers as companies and all leads in our system. I posted here and was told it was not possible but your join idea was impressive and I’m hoping you have an idea. [link](https://stackoverflow.com/questions/50730112/netsuite-saved-search-for-all-contacts-prospects-customers-and-leads) Thank you sir
Hi Raymond,
When we really need to combine searches, we now leverage our content renderer engine. See here how we combine two searches to get a single output. https://blog.prolecto.com/2016/05/18/how-to-insert-multiple-netsuite-aggregate-saved-searches-to-google-charts/
In short, the saved search criteria is just one big SELECT for all related records e.g. TRANSACTION records (Sales Order, Sales Invoice, or Item Fulfillment.
Indeed, in 2012, when we crafted that approach, it is still applicable today. But we have created so many tools to give to our clients. Consider this one to render NetSuite SQL Queries like saved search:
https://blog.prolecto.com/2021/11/27/render-netsuite-sql-queries-like-saved-searches/
Marty
Hi Marty,
I’m unable to get parent customer related transactions in the Netsuite savedsearch, can you please advice how can i get those parent related transactions in the saved search UI.
Hi Brus,
If you do a search starting from the transaction and reference the Customer (Main Lin) join, you can then expose the Customer (Main Line) : Top Level Parent field. This usually will do the trick.
Marty