One of my favourite features within NetSuite is the saved search function, which gives me the ability to create any type of custom data set that I can dream of. As a developer saved searches provide me with various options for solving repetitive and complex issues for clients. When you have wrapped your head around how NetSuite hangs together, and where saved searches sit in between it all, saved searches can begin to resolve some very complex issues when it comes to client requirements. Below I have outlined 3 complex issues I have been able to resolve for clients with saved searches in NetSuite. Hopefully this sheds some light on the power of saved searches, whilst also offering some solutions to issues you may also be having.

 

Utilising NS_CONCAT for Search Clarity

The aggregate function Netsuite doesn’t want you to know about…..or at least doesn’t want to include in their documentation.

This is one of the most used functions we have when it comes to solving customer’s searching problems, as it essentially allows us to roll up detail into one line.

Here is an example of where NS_CONCAT has elegantly solved a customer issue. A customer wanted all Quotations and Sales orders for each US Customer.

Now, this sounds relatively simple, surely, we can just group by customer and add the necessary filters, but the Customer didn’t want to have to click into each Customer within the search to discover the transaction detail behind the grouping. They wanted this detail at the top level, and they wanted to be able to click through to each of the Sales Orders and Quotations linked to the customer in the search results. This is where NS_CONCAT and some HTML come into play.

Thanks to NS_CONCAT we could group the results by customer and then aggregate the Sales Orders or Quotations into the single Customer row. We can also pull in the transaction id and use this with the standard URL for transaction records to provide a hyperlink on each aggregated transaction. Giving us the following result:

Joining Records Which Aren’t Linked

Netsuite saved searches are essentially a wrapper around SQL queries to the underlying databases. However, this wrapper results in a lose of SQL functionality that we take for granted. Take for example two tables that are not natively linked but contain the same set of keys which could be easily used to create a reliable and effective join. In SQL this would be an easy join to make but unless there is an implicit link Netsuite’s saved search functionality will not allow you to do so. Netsuite’s saved search functionality essentially only allows one level of depth from the main record you are searching against, or does it…

We had to solve this issue for a customer when they wanted to more information on costing for Projects, what follows is a brief description of how we achieved this, but first a quick explanation of the issue we faced with Netsuite Projects.

In Netsuite projects there are three elements which allow us to utilise resource-based costing as well as billing, these are as follows;

  1. Project Tasks: The building block of a project and is the first point at which we begin to build up a cost by assigning a person or persons. In doing this we can begin to budget and cost a project
  2. 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.
  3. Time Entries: The actual effort the resource expended on the task, this is obtained through Netsuite time sheets

When a resource enters time for a project against a task Netsuite pulls in the price from the corresponding task assignment record. But how can we derive the cost for this time sheet entry? The cost comes from the Task assignment and is not implicitly linked to the time entry.

The challenge the customer had was that they needed relate this cost against the time entry, so how could we achieve this without reverting to suite script and using two searches.

If we start our search from the Project Task table we can get both the Time Entries and the Project task Assignments, but these are both child elements and are not natively linked.

To solve this, we must create an artificial link based on a common key in both, in this case we will use the Employee Id. To create this, we develop a Formula Numeric Filter and use a SQL function instr.

The function instr will return a value greater than 0 if it gets a match when doing a comparison between the employee on the Project Task Assignment record and the Time Entry record. We can then filter out any which do not result in 0, essentially creating a join between the two.

instr({
    time.employee
}, {
    projecttaskassignment.resource
})

Using Saved Searches to Circumvent Permission Concerns

We recently were tasked with building an advanced Utilisation module in NetSuite for a customer that wasn’t happy with the out the box utilisation offerings, both the means of calculation and how this information could be presented to the user.

One requirement was that when a Line Manager viewed the report they could see the utilisation reports of each of their juniors.

To address this, we created a restlet which would do a search against the employee records for any employee with a supervisor equal to the current user. This list of employees was then fed to the custom utilisation modules and the rest of work was completed.

This worked as expected but issues were encounter when the solution accessed by a user that was a line manager but was using a role that did not have access to employee records, which in the real work is most Netsuite roles that are not Human Resource related. Permission violation errors were being thrown.

We could open the employee records by giving viewers access to roles just to allow the utilisation solution to work, nor could we allow the suite script to run with admin privileges as it was a restlet. So how could we solve it?

Joining to the employee table from a less restrictive record table!

Netsuite does not check permissions when a join is made, it only checks permissions on the primary record type of the search. In this instance, we set up a join to the employee table from the Time entry table. Every employee would have at least view access to the time records, otherwise, how could they book time.

var filters = [];
filters.push(new nlobjSearchFilter('supervisor', 'employee', 'anyof', [currentUser]));
filters.push(new nlobjSearchFilter('hiredate', 'employee', 'onorbefore', today));
filters.push(new nlobjSearchFilter('releasedate', 'employee', 'notonorbefore', today));
filters.push(new nlobjSearchFilter('isinactive', 'employee', 'is', 'F'));

var columns = new Array();
columns.push(new nlobjSearchColumn('employee', null, 'GROUP'));
columns.push(new nlobjSearchColumn('internalid', 'employee', 'GROUP'));
columns.push(new nlobjSearchColumn('firstname', 'employee', 'GROUP'));
columns.push(new nlobjSearchColumn('lastname', 'employee', 'GROUP'));


var searchResults = nlapiSearchRecord('timebill', null, filters, columns);

This solved the issue and meant the employee records were not unnecessarily opened up to any role which the customer didn’t want them to be.

 

To stay in the loop for further news, guides, resources and more, sign up to "insight" our flagship weekly NetSuite and Business Software Newsletter, click here.

 

2 Comments