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

Rookie Asked on September 4, 2020 in Saved Searches.
Add Comment
10 Answer(s)

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

Intermediate Answered on September 4, 2020.
Add Comment

Here is my search that shows me orders how many items have been sold with just a single line item on them

RE: Add a formula(numeric) as an available filter in a saved search

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.

RE: Add a formula(numeric) as an available filter in a saved search

 

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.

 

Rookie Answered on September 4, 2020.
Add Comment

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 o Formula (Text), which can be added as a filter.

Thanks,

Chris

Intermediate Answered on September 4, 2020.
Add Comment

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?

Rookie Answered on September 4, 2020.
Add Comment

Hi,

Would that not be the same for Formula (Numeric) too?

What happens if you leave it blank in this instance?

Thanks,

Chris

Intermediate Answered on September 4, 2020.
Add Comment

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?

Intermediate Answered on September 4, 2020.
Add Comment

so:

CASE WHEN {totalamount}-NVL({taxtotal},0)-{shippingamount}-{amount}=0 THEN 0 ELSE 1 END

Rookie Answered on September 4, 2020.
Add Comment

Yeah, that’s it. If that doesn’t work then we might need a bit of a rethink on the approach.

Intermediate Answered on September 4, 2020.
Add Comment

If this is correct, then it doesn’t work. 0 gets my single line but 1 gets zero results

RE: Add a formula(numeric) as an available filter in a saved search

Rookie Answered on September 4, 2020.

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.

 

on September 5, 2020.
Add Comment

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.

Rookie Answered on September 4, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor