Netsuite Saved Search to Display Records when the date difference is greater than the 30 days and lesser than 32 days using custom formula field

Question

My Requirement:

I want to display the Sales Order Results when the payment event result is accept and the payment event date field value is greater than 30 days and lesser than 32 days i.e (31 days old).

For this i have created an saved search using the custom formula field using case when statement.

My Formula:

CASE WHEN ((FLOOR (TO_DATE( {paymentevent.date}+30) - {today} )) > 30 && (FLOOR (TO_DATE( {paymentevent.date}+30) - {today} )) > 32) THEN 1 ELSE 0 END

but this for formula is not picking any result.

Is there any possibilities to achieve this search.Thanks In advance.

Answer 1

Well I think the reason you are not getting anything is because payments are posted against invoices and not sales orders. You can just use the Payment Event Date field in the search and then use (relative) and then days ago and 31 in the search (See image).

I don't think you need a custom formula, even though yours looks good

-- EDIT

---

I was wrong about the Payment Events being created off the invoice, they are created from sales orders.

I think using the search with (Relative) 31 days ago is still a good idea.

Let me know if this is what you are looking for.

---

Criteria:

Results:

---

Preview: