Pages

2013/10/16

Saved Search Formulas - An Easy Example

Note: this is the first in a series of blog posts relating to Formulas in NetSuite.

Saved Search Formulas in NetSuite may look daunting or even scary if you've never used them before. That's normal: it's a complete language in itself (Oracle SQL). That being said, I will show you an example in this article that I guarantee anyone can understand and use!

As a NetSuite consultant, I'm often tasked to set up Dashboard Lists. One of the limitations of Dashboard Lists is that you can only show a certain number of columns.

Here's a standard portlet showing a list of Corporate Leads:


Now let's look at how the Saved Search driving that list is set up:

As you can see, due to NetSuite's column limitation in List portlets, the Status field is not displayed in the portlet despite being defined in the Saved Search.

The good news is we can work around that using formulas!

Without going into details, Saved Search Formulas allow you to do hundreds of cool things, like conditions (display a certain value based on another value), HTML (create a dynamic link to a record based on your results) and stick multiple columns into one. In this case, the latter will help us achieve our goal of overcoming the maximum column limitation, by taking two columns (let's say ID and Primary Contact) and merging them into one.

To start off a Saved Search Formula, when editing the Saved Search, go to the Results tab and choose one of the Formula Field types in the Field column:


The type you must choose depends on the end result of your column. In this case, we will be merging two columns together, so the result will be Text. Thus, we choose Formula (Text).

The next step is to start the formula. Since NetSuite gives you very little space in the Formula column, your best bet is to click in the Formula Column, then click on the little Pop-Up icon:


In the Formula pop-up, you will see three fields:

  • The Function drop-down will allow you to insert Oracle SQL functions into your formula. We won't use it in this example.
  • The Field drop-down will allow you to insert NetSuite fields directly into your formula. We will use that in a second.
  • The Formula field is a large text area where you will be building your formula.
Alright, so we want to build a formula that will concatenate the ID and Primary Contact fields. To start off, choose ID in the Field drop-down. It should copy {entityid} into the Formula field:



In a formula, NetSuite fields are represented by their IDs surrounded by curly braces, i.e. {}. So the field ID is represented by {entityid}.

Next, click in the Formula field and add the "double pipe operator", i.e. ||. If your keyboard layout is standard US, the pipe character is done with Shift+Backslash (Shift+\). Go ahead and enter two pipe characters after the {entityid}, like so:


The double-pipe operator allows you to concatenate (or merge) two text values together. So if your formula was 'hello '||'world', you're telling NetSuite to take 'hello ' and 'world' and merging them into 'hello world'.

Our last step is to add the other field we want to merge, in this case Primary Contact. Click on the Field drop-down and select Primary Contact. Your formula should then look like this:


Looks pretty good, let's try it out! Hit the Set button, move your column to the top of the Results list and remove the ID and Primary Contact columns:


Now we only have 7 columns instead of 8, so everything should show up in the List portlet, including the last column, Status. Hit Save and take a look at your new List Portlet:


Great news, the Status is showing up! However, we seem to have a few problems with our first column:

  • First, the label for the column says "Formula (Text)". That's not very useful.
  • Second, although the merging worked, there's no space between the two values (ex: Patel CafeAnil Patel, there should be some space between Cafe and Anil)
Solving the first problem is simple: Go back to the search, and in the Custom Label column of the Formula (Text) field, enter "Company & Contact":


While we're here, let's solve our second problem: having a space between the ID and Contact. On the Results tab, click on the Formula and once again click on the Pop-up button. Change your formula to look like this: {entityid}||' - '||{contact}


This formula can be translated as such: Take the ID, append ' - ' to it, then append the Primary Contact to that. So if the ID is "Wolfe Electronics" and the Primary Contact is "Alan Wolfe", then the formula will give us "Wolfe Electronics - Alan Wolfe". Let's see if it works: Hit Set in the pop-up, Done on the formula row, then Save on the search:


Well, it looks great for rows where there's both an ID or Primary Contact, but not so great when there's no Primary Contact, as we end up with a random hyphen at the end (ex: Woods Publishing Co - ).

Next time, I will show you how we can use the CASE statement in a formula to only show that hyphen when there's a Primary Contact. Until then, I hope I've given you a chance to get your feet wet in the wonderful world of Formulas!


2 comments:

  1. <> I think the column to use here is "Custom Label."

    ReplyDelete