ANS-0013 · SAVED SEARCHES & FORMULAS
How to Create a NetSuite Date Filter for Next Week and Beyond?
This guide provides a SQL formula to accurately filter NetSuite saved search results for records dated from the beginning of the upcoming week.
Short answer
To filter NetSuite records for dates starting next week and extending for a specified period, use a SQL "between" clause. Set the start date as TRUNC(sysdate, 'iw')+7 to capture the beginning of the next week. The end date will be TRUNC(sysdate, 'iw') + (number of days after) to define the duration from the current week's start.
Scenario
Users often require NetSuite saved searches to display records with dates that fall within a specific future period. A common requirement is to filter data starting from the beginning of the upcoming week and extending for a defined number of subsequent weeks or days. Standard relative date filters may not precisely meet this need, necessitating a custom SQL formula.
Solution
To implement a date filter that begins next week and covers a subsequent period, apply the following SQL formula within your NetSuite saved search criteria. This formula utilizes the TRUNC(sysdate, 'iw') function to identify the start of the current ISO week, then calculates the start of the next week and the desired end date. Use the following expression in your date field's criteria: {your date} between (TRUNC(sysdate, 'iw')+7) AND (TRUNC(sysdate, 'iw') + (the number of days after)) Replace {your date} with the actual date field ID you are filtering (e.g., TRANDATE). Adjust (the number of days after) to define the exact end of your desired future period, relative to the start of the current week.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support