'NetSuite Saved Search custom field results based on Month

My Problem: I would like to show results of a custom field based on the current Month in a single saved search.

Custom Field (PM Schedule) drop down with following selections: -Monthly -Quarterly -Annually (there are more options but this gives the picture)

Essentially what I want to do is as follows:

CASE WHEN TO_CHAR({today},'MM') = '01' THEN Show results 'Monthly', 'Quarterly', 'Annually 
WHEN TO_CHAR({today},'MM') = '02' THEN Show results 'Monthly'
WHEN TO_CHAR({today},'MM') = '03' THEN Show results 'Monthly'
WHEN TO_CHAR({today},'MM') = '04' THEN Show results 'Monthly', 'Quarterly'
ect....
END

I have 5 different combinations and my current solution is to run 5 different saved searches that run on the cooresponding Months:

Formula (Numeric): CASE WHEN TO_CHAR({today},'MM') = '01' THEN 1 ELSE 0 END
PM Schedule (custom): is any of Monthly, Quarterly, Annually

This works but I want to know if there is a way to do this in a single saved search.

netsuite


Solution 1:[1]

If you are okay with displaying the results on some Custom Page, I'd suggest to go with a Suitelet Script.

You'll need to create five functions based on your combinations.

  • Function 1 will fetch data from Saved Search and display data 'Monthly'
  • Function 2 will fetch data from Saved Search and display data 'Monthly' and 'Quarterly'

and so on..

Then based on the month, you can call the corresponding function and display results in a desired way. A single Saved Search will be used in this case.

Also if the month is February, and only need to display data monthly, you can disable the 'quarterly' and 'annually' fields on your Suitelet Page.

Give this a try. Let me know in case of any questions below.

Solution 2:[2]

Formula (numeric): is 1

DECODE({custbody_pm_schedule},
    'Annually', DECODE(TO_CHAR({today},'MM'),'01',1,0),
    'Quarterly', DECODE(TO_CHAR({today},'MM'),'01',1,'04',1,'07',1,'10',1,0),
    'Monthly', 1
,0)

PM Schedule (custom): is any of Monthly

and add PM Schedule as a filter so the user can select. In February, only 'Monthly' will return any results.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sayeesh
Solution 2 Nathan Sutherland

Related Questions