1. Define the Search Type
Create a Transaction Saved Search as invoices are categorized under transactions in NetSuite.
2. Set Criteria
Apply the following filters to ensure relevant data is retrieved:
- Main Line → True (This ensures that only the main transaction lines are fetched.)
- Type → Invoice (Restricts results to invoices only.)
- Add any additional criteria as required
3. Configure Results Columns
The challenge is obtaining the last two invoice dates dynamically. To achieve this, we use summary functions and formulas:
Customer Name
- Field:
Customer (MainLine): Name - Summary Type:
Group(This groups results by customer.)
Last Invoice Date
- Field:
Formula (Text) - Summary Type:
Maximum - Formula: TO_CHAR(MAX({datecreated}), ‘YYYY-MM-DD’)
Explanation:
MAX({datecreated})retrieves the latest invoice date.TO_CHAR(..., 'YYYY-MM-DD')formats it intoYYYY-MM-DD. You can use any date format as required.
Second Last Invoice Date
To find the second last invoice date, we first concatenate unique dates in descending order and then extract the second entry.
- Field:
Formula (Text) - Summary Type:
Maximum - Formula:
REGEXP_SUBSTR(
LISTAGG(DISTINCT TO_CHAR({datecreated}, ‘YYYY-MM-DD’), ‘, ‘)
WITHIN GROUP (ORDER BY TO_CHAR({datecreated}, ‘YYYY-MM-DD’) DESC),
‘[^,]+’, 1, 2
)
Logic
Generating the List of Invoice Dates:
LISTAGG(...)creates a comma-separated list of unique invoice dates, sorted in descending order.- Example output:
"2025-05-15, 2025-04-10, 2025-03-25"
Extracting the Second Entry:
REGEXP_SUBSTR(..., '[^,]+', 1, 2) works as follows:
[^,]+→ Matches any sequence of characters that are NOT commas.1→ Starts from the beginning of the string.2→ Fetches the second match, which is the second latest invoice date.
Alternative Date Formatting
If needed, the second last invoice date can be formatted differently:
TO_CHAR(
TO_DATE(REGEXP_SUBSTR(
LISTAGG(DISTINCT TO_CHAR({datecreated}, ‘YYYY-MM-DD’), ‘, ‘)
WITHIN GROUP (ORDER BY TO_CHAR({datecreated}, ‘YYYY-MM-DD’) DESC),
‘[^,]+’, 1, 2), ‘YYYY-MM-DD’)
, ‘DD/MM/YYYY’)
This ensures proper date formatting according to business requirements.