Search code examples
netsuitesaved-searches

setting date range filter in saved search formula

I’m looking for some guidance on how to write date range filter within saved search formula. For example, below is what I have so far to see sales of certain type, but also need to add date filter so get only sales for this year to date. I understand I can set date filter in filter section, but I also need to add other columns in this report which will display transaction amounts of different types and dates. Thanks in advance for any input. Formula: CASE WHEN {transaction.type} = 'Invoice' AND {transaction.custbody1} = 'Direct' THEN {transaction.amount} end

Solution

  • You can use the SQL TO_CHAR function to return text portions of a date, for comparison with the corresponding text portions of sysdate. In the example you give, that would look like:

    CASE WHEN {transaction.type} = 'Invoice' 
        AND {transaction.custbody1} = 'Direct' 
        AND TO_CHAR({trandate}, 'YYYY') = TO_CHAR(sysdate, 'YYYY')
    THEN {transaction.amount} 
END

    You can use the same approach with different formats to get almost any date. You could also use EXTRACT to get slightly better performance in some situations. EXTRACT returns a number rather than text.

    CASE WHEN {transaction.type} = 'Invoice' 
        AND {transaction.custbody1} = 'Direct' 
        AND EXTRACT(YEAR from {trandate}) = EXTRACT(YEAR FROM sysdate)
    THEN {transaction.amount} 
END

    You may find TO_CHAR to be easier to use when you want to combine different parts of a date. E.G.: TO_CHAR({trandate}, 'YYYY-MM') = TO_CHAR(sysdate, 'YYYY-MM') rather than EXTRACT(YEAR from {trandate}) = EXTRACT(YEAR from sysdate) AND EXTRACT(MONTH from {trandate}) = EXTRACT(MONTH from sysdate)