I've got some custom records that I'm trying to search that are basically a way of comparing a custom budget to a custom profit. The search needs to return all customers, regardless of if there is a transaction, so I can't use the date as a filter or customers with zero transactions in the filtered period will have their budgets omitted as well. So right now I have a search that comes up with budgets and results from our beginnings of NetSuite, and I need to filter them properly using the formulas.
The problem is that I don't know the proper syntax for relative dates inside formulas, and NetSuite's help about this stuff has been less than...helpful. Here's the formula I have right now:
CASE WHEN {custrecord_tran_customer.custrecord_tran_date} != ‘within last month’ THEN ({custrecord_tran_customer.custrecord_tran_est_gp} * 0) WHEN {custrecord_tran_customer.custrecord_tran_type} = 'Credit Memo' THEN ({custrecord_tran_customer.custrecord_tran_est_gp} * -1) ELSE {custrecord_tran_customer.custrecord_tran_est_gp} ENDThe formula works if I omit the first part, noted here:WHEN {custrecord_tran_customer.custrecord_tran_date} != ‘within last month’ THEN ({custrecord_tran_customer.custrecord_tran_est_gp} * 0) But doesn't filter the results appropriately. Is there another way to accomplish what I'm trying for here?