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
Hi,
I’m struggling a little to picture exactly what you currently have and what you’re looking to achieve. Would you be able to share some screenshots?
Thanks,
Chris
Here is my search that shows me orders how many items have been sold with just a single line item on them
for the results you see that I have the count of document number to give the number of lines. So part A has been a single line 200 times, B has been single line 112 times, etc.
I have another search that shows me item sales with revenue by item name. What I’d like is to have a single search that I can say Part A has x amount of revenue Part B has y revenue. Cool, now I enter 1 into an available filter and it shows me those single line results where Part A’s revenue is now the revenue only when it was a single line item.
Hi,
OK, I think I understand. It may be that there are other ways to achieve this (which we can happily discuss later), however to answer your original question…
You can’t add a Formula (Numeric) but I don’t see any issue is changing to Formula (Text), which can be added as a filter.
Thanks,
Chris
I considered changing it to text but then how would I filter? I could enter 0 and that would give me all the single lines but then what value returns all the data?
Hi,
Would that not be the same for Formula (Numeric) too?
What happens if you leave it blank in this instance?
Thanks,
Chris
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?
so:
CASE WHEN {totalamount}-NVL({taxtotal},0)-{shippingamount}-{amount}=0 THEN 0 ELSE 1 END
Yeah, that’s it. If that doesn’t work then we might need a bit of a rethink on the approach.
If this is correct, then it doesn’t work. 0 gets my single line but 1 gets zero results
I’m struggling to view the thread too so will continue in the comments…
So far we’ve tried “CASE WHEN {totalamount}-NVL({taxtotal},0)-{shippingamount}-{amount}=0 THEN 0 ELSE 1 END”.
I can confirm that this works for me – I pasted exactly as above without any quotes around the one and the zero.
Blank returns no results and the others filter accordingly.
N.B. I ungrouped and added a Formula (Numeric) to the results to test before adding the grouping back in.
all zeroes. Because of the case saying is 0? could I use the “any” condition? If so, how would I say if it is ‘0’ or ‘1’?
Follow up note, I cannot see past 10 posts on this page. I click to go to page 2 and nothing happens. I had to delete one of my comments to see your answer.
