RE: Add a formula(numeric) as an available filter in a saved search
I want to use formula (numeric) as an available filter in a saved search. My search can be either a transaction or item search. I want to show the item name, a sum of the sales for it and how many times it was on a sales order. I have the search just fine but I have one for single lines and one for all transactions. I’d like to do it all on a single search. I referenced http://www.netsuiterp.com/2018/09/formula-text-field-does-not-work-as.html and https://www.sikich.com/insight/using-formula-values-as-available-filters-in-netsuite-saved-searches/#:~:text=Using%20Formula%20Values%20as%20Available%20Filters%20in%20NetSuite%20Saved%20Searches,-January%2016%2C%202019&text=Saved%20Searches%20are%20a%20powerful%20tool%20to%20solve%20problems%20in%20NetSuite.&text=So%20the%20trick%20here%20is,as%20on%20your%20Criteria%20tab.
But I cannot get this to work. I have my formula(numeric) as a criteria and when I try to mark the formula as “show in filter region”, I am unable to do that.
My formula for single line transactions is: Formula (numeric) {totalamount}-NVL({taxtotal},0)-{shippingamount}-{amount} is equal to 0
If it was Formula (Numeric) then I don’t think the situation would be any better. Presumably that formula returns a lot of different values which are non-zero.
I think to continue down this route, you’d need to return a single value when the formula is non-zero, e.g. 1.
A CASE statement should help with this and then entering 1 in the filter should get you everything else.
Do you need any further details on the CASE statement?
