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

Hi Team, I badly need your help.

I am trying to build a FAM Saved Search to display the Total Depreciation each year out of the FAM Depreciation History. I am getting an error: Invalid Expression when I am using this formula (numeric):

Case when {custrecord_deprhistdate} between to_date('01/01/2021', mm/dd/yyyy)  and to_date('12/31/2022', mm/dd/yyyy) THEN ( CASE when {custrecord_deprhisttype} = 'Depreciation' THEN {custrecord_deprhistamount} ELSE 0 END) ELSE 0 END

Anyone who can help, please? Thank you

Couple things to look at:

1. Is custrecord_deprhistamount actually a numeric field? It would be dumb if not, but I've run into instances before where fields that you'd think should be numeric are not
2. I'm not sure 'between' works in Saved Search formulas. I might try &gt;= &amp; &lt;= versus your between-and combo.

Thank you. I figured that I missed to out the two single quotes for the date format. This should be:

Case when {custrecord_deprhistdate} between to_date('01/01/2021', *'mm/dd/yyyy'*) and to_date('12/31/2022', *'mm/dd/yyyy'*) THEN ( CASE when {custrecord_deprhisttype} = 'Depreciation' THEN {custrecord_deprhistamount} ELSE 0 END) ELSE 0 END