Search code examples
netsuitecase-statementsaved-searches

highlight results when item fulfillment date is not same as invoice date

I have saved search which displays invoices when the invoice date is not same as the ship date.I now only need to highlight records when the dates are in different months. For example I already have a saved search list to show invoice if the invoice date was 9/12/19 but the actual ship date on item fulfillment is 9/11/19. Now I would want to highlight this result if these two dates were NOT in the same month.

The formuala I'm using for the saved search results is case when {billingtransaction.trandate} <> {billingtransaction.shipdate} then 1 else 0 end Now I believe I need a similar case statement which will factor month difference

Solution

  • You can use the function TO_CHAR function to do this:

    CASE WHEN TO_CHAR({billingtransaction.trandate}, 'MM') <> TO_CHAR({billingtransaction.shipdate}, 'MM') THEN 1 ELSE 0 END