Custom Label Search

As with the SEARCH type, this type also uses saved searches to retrieve its data. One difference, however, is that this type requires that all columns in your search have the Custom label field set. This means that you can not use the built-in translation management that is otherwise supported in SEARCH and with the help of @label. However, this type is advantageous in some specific cases. These examples and related features are described in more detail in this section.

Make sure to read chapter Dataset Filtering on how to dynamically filter your search/data.

Group By

A known challenge with saved searches in Netsuite is the difficulty of presenting hierarchical data in a structured way. If you want to include data from several levels in a saved search, for example from parent and its children, data is repeated on each row that otherwise may only belong to the parent level. The GROUP BY function makes it easier to print this type of data in your layouts.

There are two different ways to group your data. The first and slightly simpler option is head/line where you have data from both parent and child level in the same search. The second example is even more powerful as it can handle data for more than two levels, for example when your data is structured in a matrix. One such concrete example is items and their pricing that can be done based on price levels, currencies, quantity ranges and lastly the unit prices. These two types are described in more detail below.

Follow the general instructions on how to add a new dataset and make sure that the field Type is set to Custom Label Search.

The Group By is not mandatory to use for custom label searches. However, if you do not need to utilize this functionality we would advice you to use the Search type for you dataset instead of Custom label search.

Group By: H/L

Group By H/L means that we want to create a result from our saved search that groups our data based on parent / child. The goal is that we for each parent (H) should get an object which in turn can have one or more underlying rows (L). A concrete example where this is useful is transactions where we have a main level and one or several lines. This can also be applied in searches where you want to join data from linked child records.


Creating the saved search
The below examples shows a search based on a custom record called Contract and its child record Contract Row. We start by adding the columns to our search including the fields from the child record. For all columns we assign a custom label. However, for every column that belongs to the parent level we add the prefix "H:" and for all child records we add the prefix "L:".

Create a dataset record
The next step is to create a dataset record. In the field Group By you need to define the column on which the grouping should be made. In the above search example the Internal ID of the parent record is labelled as "H:ID". In our continued example, the grouping will therefore be done based on this column.

Visualize the data
After having created our saved search as well as our dataset record we can choose to visualize the data using the context menu in the editor. As shown in the image on the left, the tool will use the labels from the saved search and create a JSON object that will include each H: prefixed column as part of the main object and all L: prefixed columns as separate child objects under rows.

Adding your data in the editor
When printing this in the code editor, the context menu and the function to insert the Complete Table will assist you with the formatting. The data is added to the PDF using the FreeMarker tags <#if> and <#list>. You can read more about these tags in the FreeMarker manual.

<#if DATASET?has_content>
<#list DATASET as HEAD>
 PRINT HEAD DATA FOR OBJECT
<#if HEAD.rows?has_content>
<#list HEAD.rows as ROWS>
PRINT LINE DATA FOR OBJECT
</#list>
</#if>
</#list>
</#if>

Group By: Asterix

The function to GROUP BY ASTERIX is useful when your data is structured in multiple levels. The following example used in this section will demonstrate the power of this grouping method using the record type Item and its pricing matrix.

If you are using muliple currencies, price levels and quantity-based pricing in your account the data structure for your items we be something like:

  • Item

    • Currency

      • Price Level

        • Quantity Range

          • Unit Price

If you for example have an item with three different currencies, four different price levels and five quantity ranges, that would give you a total of 60 unit prices. As a result, your search would also contain 60 rows for that item only. Grouping data with Asterix allows you to turn that data into a structured and hierarchical JSON object which in turn will make it much more FreeMarker friendly.

Creating the saved search
With the help of the asterix (*) you can set the nesting levels of your data. One * will be level one, ** will be level two etc.

As with all custom label searches, you must assign custom labels to all columns, including the ones that are not prefixed with an asterix.

The saved search in NetSuite will still contain all the rows but when running the search in the editor asterixes added will help in creating the hierarchical JSON object.

Placing an asterix in the label of a column means:

  • All spaces will be converted to underscore "_". (You can add the underscores yourself to the label if you want)

  • The field will be the first field in the JSON object for that level.

  • The Parent object will have a property with the same name as the field but with an "s" added to symbolize "children".

    • From example: *Item > Items, **Currency > Currencys etc.

  • All fields after this field is part of the same JSON object UNTIL next Asterix

    • From example: Name and Description will both be part of the Items object as their columns are placed after *Item but before **Currency.

  • The number of asterixes (*) defines the nesting level.

    • From example: 4 nesting levels since the maximum number of asterixes are four (****Quantity Range)

  • The asterixes will not be a part of the named properties.

  • Please note that you should not add two columns with the same number of asterixes.

Create a dataset record
The next step is to create a dataset record. In the field Group By you need to enter an asterix. See image to the right for an example. Please note that unlike the other grouping options you do not need to specify a specific column. The asterix symbol will suffice.

Visualize the data
After having created our saved search as well as our dataset record we can choose to visualize the data using the context menu in the editor. As shown in the image on the left, the tool will use the labels and the asterixes to create an hierarchical JSON object with the nesting levels being: Items, Currencys, Price_Levels and Quantity_Ranges.

The preconfigured data entry from the context menu for this grouping type is to add the data in a bullet list. However, you can choose the display the information in our ways, for example by creating a table structure as shown in the example below.

For each nesting level the <#list> FreeMarker tag is used to iterate the objects and their data.

<!-- LIST the top object array "Items" as "ItemTop" -->

<#list ItemSearch.Items as itemTop>

<table class="border fullwidth" >

<thead>

<tr>

<th><p>Name</p></th>

<th><p>Description</p></th>

</tr>

</thead>

<tbody>

<tr>

<td><p>${itemTop.Name}</p></td>

<td><p>${itemTop.Description}</p></td>

</tr>

<tr>

<td colspan="2">

<table class=" fullwidth" >

<tbody>

<!-- LIST the Currencies for each Item as CurrencyObj -->

<#list itemTop.Currencys as CurrencyObj>

<tr>

<td align="center" style="background-color:#ccccccb5"><h4>${CurrencyObj.Currency}</h4></td>

</tr>

<tr>

<td>

<table class=" fullwidth" cellborder="0" cellpadding-left="5" cellmargin-left="-2" cellmargin-right="-2">

<tbody>

<tr>

<td style="background-color:#ccccccb5" align="center"><h4>Price Level</h4></td>

<!-- LIST the first Quantity Ranges object in the Price Level object to set the Price Level headers -->

<#list CurrencyObj.Price_Levels[0].Quantity_Ranges as Quantity_RangeObj>

<!-- Add one column per Quantity Range and write the range -->

<td style="background-color:#ccccccb5" align="center"><h4>${Quantity_RangeObj.Quantity_Range}</h4></td>

</#list>

</tr>

<!-- LIST through the Price Levels for the current Currency -->

<#list CurrencyObj.Price_Levels as Price_LevelObj>

<tr>

<!-- Write the Price Level Name -->

<td style="background-color:#ccccccb5" vertical-align="top"><p>${Price_LevelObj.Price_Level_Name}</p></td>

<!-- Write the Unit Price for each Quantity Range within the current Price Level -->

<#list Price_LevelObj.Quantity_Ranges as Quantity_RangeObj>

<td align="right">${Quantity_RangeObj.Unit_Price}</td>

</#list>

</tr>

</#list>

</tbody>

</table>

</td>

</tr>

</#list>

</tbody>

</table>

</td>

</tr>

</tbody>

</table>

</#list>

Below is an example of how the below code will be outputed in the rendered PDF. As the image shows, the search and the code combined will dynamically display the currencies used, the price levels used for each respective currency and the unit prices for each such combination together with the quantity pricing.

This is just one way you can output this information but is a good example to show some of the possibilities with the tool.

Formula fields

There is no limitation when it comes to using formula fields in your custom label searches. Make sure to assign your formula column a custom label in your saved search and that column will be referenced in the code using that custom label.

Please note that all values from your searches will be returned as strings. If you want to format the values from your formulas, read the FreeMarker manual on how to format into the correct type.