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 setup a conversation.

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

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

11 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.

    Thanks,

    Gabe

  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.

    Marty

  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”.

    {dashboard}

    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.

    Steve

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

    Steve,

    The key is to ensure the links work when generating the saved search natively. See this article on how to do that:
    http://blog.prolecto.com/2012/07/19/custom-hyperlink-outputs-in-netsuite-saved-searches/

    Marty

  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

    Gabe

  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?

    Marty

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>