NetSuite Tip: Formula Fields Referencing other Formula Fields

NetSuite calculated fields are quite useful.  However, be careful using formula fields that reference other formula fields.

Saved Search Error: Invalid Expression

If you create a custom formula field that references another custom formula field, you will likely get an “Error: Invalid Expression” when referencing the custom field via Saved Search.

What may cause confusion is you can produce formula fields that reference other formula fields.  These custom formula fields will work fine if you use them to display values on forms.  They even work when you reference them in SuiteScript.  But don’t get caught thinking your formula field is fine until you test it via Saved Search.

To resolve the  Invalid Expression error, make sure your formula field references all native fields that store actual data.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , , , | Category: Marketing, Technical | 17 Comments


  1. Gabriel Lawrence
    Posted January 30, 2014 at 9:10 am | Permalink

    Hi Marty,

    I was wondering if you could expand on this point. Are you saying it is impossible to add two custom formula fields text together in a saved search?

    What’s the best way to get around this constraint? Can you add two formulas in one in a custom formula field?

    I couldn’t get concat to work when adding another date formula.



  2. Posted February 2, 2014 at 9:59 pm | Permalink

    Hello Gabriel,

    In my work with NetSuite Custom Formula Fields, all the references in the formula must reference data that is actually stored. It can not reference other custom calculated fields. This breaks during NetSuite Saved Search but it will appear to work right on Forms. That’s how you get in trouble — you think it is working, deploy it, and then learn you made a mistake.

    Sometimes, we build NetSuite scripts on the AfterSubmit user event to store the calculated values in the custom fields. The problem then is solved.

    Let me know if this helped.


  3. Steve
    Posted July 13, 2016 at 9:36 am | Permalink

    hi Marty,

    I am trying to display a dashboard icon in my saved search. I have created a custom URL so clicking on the link will take the user to a custom dashboard. The problem is using the following tag creates the error, “ERROR: Field Not Found”.


    here’s the full url:

    ‘ ||{dashboard}||’

    The above code is added to the FORMULA column in a saved search.

    Note: when adding fields there is a “Project : Dashboard” field.

    thanks in advance for any help.


  4. Posted July 16, 2016 at 5:31 pm | Permalink


    The key is to ensure the links work when generating the saved search natively. See this article on how to do that:


  5. Gabriel Lawrence
    Posted April 3, 2017 at 1:26 pm | Permalink

    Hi Marty

    I have a NetSuite field that references the ‘casetaskevent’ field.

    It gives an error of “ERROR: Field ‘casetaskevent’ Not Found” when you create a time tracking record without going through a case.

    I want the field to have a formula like excel’s iferror(formula,””)

    Is there a way to do a case when ‘error’ THEN ‘NULL’ END ?

    I want the field to be empty if it can’t find the ‘casetaskevent’ instead of giving an error message

    Thanks for your help


  6. Posted April 3, 2017 at 7:11 pm | Permalink

    It may be a configuration in your account and this is not available. Are you able to search and get values from the field when using it directly? In general, I use the NVL function to test for null to return back a value to avoid an error.

  7. Gabriel Lawrence
    Posted April 4, 2017 at 6:43 am | Permalink

    Thanks Marty, I haven’t tried NVL yet I’ll give it a go. I was able to use a condition on the workflow that the field is greater than 100 (it was an interalID field) and that was okay with the error. So it fixed my immediate issue.

    Thanks for writing back

  8. Nathan Gill
    Posted August 2, 2017 at 6:45 am | Permalink

    Hello Marty, I’m actually have the exact opposite problem. It’s showing up great on my saved search but on my form it’s showing up as Error Invalid Expression. When I edit and re-save the form it will fix it on the form. Any idea why/how this could occur?

    Thank you!

  9. Posted August 5, 2017 at 1:52 pm | Permalink

    Likely there is a value that is needed on the form that when you save, it is there. For example, when the form is in the initial Create state, it has no internal ID to pass to the search. Thus, it fails. But once you save it, the internal ID exists and all is good.

  10. Brian
    Posted October 11, 2017 at 12:51 pm | Permalink

    I’m working on a saved search of Knowledge Base Topics in NetSuite. I can find where the results shows me fields but the field for Detailed Descripton is not in the list. When i look at the information on the field it’s called longDescription. The real interesting think is I can import CSV information to this field I just can’t pull the data back out int o a search result. Is there a way to use a Formula(text) to return the value of this Field?

  11. Posted October 11, 2017 at 1:54 pm | Permalink

    I don’t have much experience with the “topic” object to say specifically. I do see that the recordbrowser is offering “longdescription” as a field. That seems strange to me that you can’t get to the data. Perhaps go to NetSuite Support?


  12. Dave M
    Posted November 9, 2017 at 12:20 pm | Permalink

    Hi Marty,

    I’m trying to pull a report where a new value is compared to an old value. The Numeric forumula I’ve been banging my head against:

    case when {systemnotes.oldvalue} < {systemnotes.newvalue} then 1 else 0 end

    All my other criteria appear to work properly, but this one line is confounding me.

    Any ideas?


  13. Posted November 11, 2017 at 4:23 pm | Permalink

    Most of the time this is a data type issue. Make sure you convert values using to_char(), to_number() and NVL() functions. Also, make sure your formula type is numeric if you want to test for a 0 or a 1.


  14. Jacob
    Posted June 28, 2018 at 8:00 am | Permalink

    I am trying to pull a saved search on JE’s, and I need to reference the ‘Eliminate’ checkbox on the line-level. You can see this in the transaction XML code, but the saved search keeps returning ERROR: Field not Found

    My formula is: case when {eliminate}=’T’ then ‘Eliminating’ else ‘Not Eliminating’ end

    I took the ” off of the T as well, and received the same error.

    Any suggestions?

  15. Posted June 30, 2018 at 8:08 am | Permalink

    Good question. I hope I am wrong but it appears that a transaction search does not appear to support the eliminate column as a result set. Did you contact NetSuite Support?

  16. Jacob
    Posted July 3, 2018 at 8:14 am | Permalink

    Yes, and they said it is being considered in future enhancements.

    Since we are unable to pull that, it’s hard to make correcting JE’s… Any suggestions on how to get an account to STOP eliminating? When you inactivate (even if balance is 0), the system gives an error during period close.

  17. Posted July 7, 2018 at 6:19 am | Permalink

    Hi Jacob. Your question is going off topic. Feel free to contact me directly. I suspect we can help you dial in your intercompany processing with one of our senior consultants.

Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>