dynamic saved search labels
BLOOD ALONE MOVES THE WHEELS OF HISTORY! Have you ever asked yourselves in an hour of meditation – which everyone finds during the day – how long we have been striving for dynamic search labels?
Not only the years we’ve been at war, the war of work, but from the moment as a child, when we realize the search could be dynamically labeled. It has been a lifetime struggle - a never-ending fight, I say to you and you will understand that it is a privilege to label. WE ARE LABELLERS!
Searchmen of Netsuite, I ask you once more rise and be worthy of this historical hour!
No search is worth anything unless it can label itself. Some people will tell you saved search is a bad word. They’ll conjure up SQL queries of disordered results, and database charlatans. This is our duty to change their perception.
I say, searchmen and women of the world... unite. We must never acquiesce, for it is together… TOGETHER THAT WE PREVAIL! WE MUST NEVER CEDE CONTROL OF THE MOTHERLAND...!
Now that we have the dramatic intro out of the way, we can get to work.
Imagine there's no heaven. It's easy if you try. you have a saved search with a bunch of columns, each column per month, twelve in total. The first column displays data for the current month and each next column refers to the current month - 1, until you reach the previous year.
This is rather easy to achieve, but how will you label them? Someone, typically the all around least favorite person in your team, can be appointed to manually rename the labels every 1st of the month.
Or, if the least favorite person in your team is also the most incompetent one and you don't trust them even with a task this mundane, you can just have a script do the labelling for you.
I have put together a quick little search to demonstrate. Below screenshot shows sum of items sold in each month. First column is the current month (March, at the time of writing), each next column is the previous month.
The labels obviously look like shit and don't tell us anything useful, leaving the user to figure it out.
So here comes our Scheduled Script to save the day.
Type is obviously scheduled, as it will run every first day of the month. It will have one parameter, which is our saved search.
First of all, we load our search.
/**
* @NApiVersion 2.1
* @NScriptType ScheduledScript
*/
define(['N/log', 'N/runtime', 'N/search'],
/**
* @param{log} log
* @param{runtime} runtime
* @param{search} search
*/
(log, runtime, search) => {
/**
* Defines the Scheduled script trigger point.
* @param {Object} scriptContext
* @param {string} scriptContext.type - Script execution context. Use values from the scriptContext.InvocationType enum.
* @since 2015.2
*/
const execute = (scriptContext) => {
try
{
let inputSearch = search.load({id: runtime.getCurrentScript().getParameter({name: 'custscript_input_search'})});
log.audit({title: 'inputSearch', details: inputSearch});
}
catch (error)
{
log.error({title: 'execute', details: error});
}
}
return {execute}
});
Result:
As we see in the Execution Log, we have our columns with the default labels. Now we're gonna have to filter out only the columns that are actually using the numeric formula, because we don't wish to rename any other columns.
And in one fell swoop we can already rename the labels and save the search.
const execute = (scriptContext) => {
try
{
let inputSearch = search.load({id: runtime.getCurrentScript().getParameter({name: 'custscript_input_search'})});
log.audit({title: 'inputSearch', details: inputSearch});
inputSearch.columns.forEach((column, i) => {
if (column.name == 'formulanumeric')
{
let date = new Date();
let prevMonth = new Date(date.getFullYear(), date.getMonth() - i, date.getMonth());
let label = formatMonth(prevMonth);
log.audit({title: 'label', details: label});
column.label = label;
}
});
let inputSearchId = inputSearch.save();
log.audit({title: 'inputSearchId', details: inputSearchId});
}
catch (error)
{
log.error({title: 'execute', details: error});
}
}
We loop through the columns array of our search object. If the column's name is formulanumeric, we rename the label.
First, we get the current date. From that date we substract the current iteration i number of months, which is originally zero, leaving us therefore with current month. As the i increases each loop, it will give us previous month, two months before, and so on until we reach the last year.
We format the label (function is shown below) based on the prevMonth and rename the column's label.
Then there is nothing else left to do other than save the search.
The end result:
Everything seems to be in order here. And here is the entire code:
/**
* @NApiVersion 2.1
* @NScriptType ScheduledScript
*/
define(['N/log', 'N/runtime', 'N/search'],
/**
* @param{log} log
* @param{runtime} runtime
* @param{search} search
*/
(log, runtime, search) => {
/**
* Defines the Scheduled script trigger point.
* @param {Object} scriptContext
* @param {string} scriptContext.type - Script execution context. Use values from the scriptContext.InvocationType enum.
* @since 2015.2
*/
const execute = (scriptContext) => {
try
{
let inputSearch = search.load({id: runtime.getCurrentScript().getParameter({name: 'custscript_input_search'})});
log.audit({title: 'inputSearch', details: inputSearch});
inputSearch.columns.forEach((column, i) => {
if (column.name == 'formulanumeric')
{
let date = new Date();
let prevMonth = new Date(date.getFullYear(), date.getMonth() - i, date.getMonth());
let label = formatMonth(prevMonth);
column.label = label;
}
});
let inputSearchId = inputSearch.save();
log.audit({title: 'inputSearchId', details: inputSearchId});
}
catch (error)
{
log.error({title: 'execute', details: error});
}
}
function formatMonth(month)
{
try
{
const months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
return months[month.getMonth()] + ' ' + month.getFullYear();
}
catch (error)
{
log.error({title: 'formatMonth', details: error});
}
}
return {execute}
});
The very last thing you may need to do is to schedule your script to run every first day of every new month.