This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I'm creating a transaction saved search where type = Sales Order, Main line is False but am receiving an Error: Field Not Found.... any clue what could be causing this?

DECODE(TO_CHAR({‌today},'MM') - TO_CHAR({‌trandate},'MM') + 12*(TO_CHAR({‌today},'YYYY')-TO_CHAR({‌trandate},'YYYY')),0,{‌quantity})

I've tried replacing all fields in the formula with trandate and today and it's failed also which makes zero sense.

<@U028TN3227L> 
The issue seems to be related to the curly braces {‌} used with today. today is a keyword in NetSuite formulas, and you don't need to use curly braces for it.

Try updating your formula as follows:
```DECODE(TO_CHAR(TODAY,'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR(TODAY,'YYYY')-TO_CHAR({trandate},'YYYY')),0,{quantity})```

Please let me know if this works? <@U02QZU4GP46> <@U028TN3227L>

today alone like your example did not work. I tried a quote also.

I tried removing all three fields (trandate, quantity, and today) from the equation completely and still get the error.

For fun I just retyped it, and it works. But I can't see the difference!

can confirm same thing. Top copied from <@U028TN3227L> Bottom works in search.

Can you manually retype the formula, and then remove the existing one.
There might be some special characters, or Same character lookalike but with a different ASCII code which might be causing the issue

Good old NetSuite.... will give it a shot.

I put the two in a tool to convert the string to ASCII values and found there's this little guy hanging out after each open curly bracket. <https://www.codetable.net/decimal/8204>

That was it. TIL.... I deleted the opening curly bracket and first letter, then retyped them and it's all good. Appreciate everyone's time and help!

Hope you all don't mind me reviving this thread for another question... if you were to take the above formula and divide it by the number of days in the month, how would you suggest doing so?

Tried dividing the DECODE line by a TRUNC or a TO_NUM statement similar to TO_NUM(last_day({trandate}),'DD') and get an invalid expression error

Ultimately, I'm trying to calculate the average daily sales of an SKU by month for the past year

What happens if you divide by this?
``` TO_CHAR(LAST_DAY({trandate}),'DD')```
I can divide that by 2, so maybe it'd work?

Odd, I tried TO_CHAR before but it didn't work, did this time. Thanks!