NetSuite Tip: Formula Fields Referencing other Formula Fields

21 thoughts on “NetSuite Tip: Formula Fields Referencing other Formula Fields”

1. 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. 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. 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. Steve,

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

   Marty

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

12. 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?

    Thanks,
    Dave

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

    Marty

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

18. Hi Marty,
    I have created a custom wherein I let user to enter the formula. I pull the formula in my Suitescript and write a search with ‘formulanumeric’. It fails when there is CASE WHEN statement in formula. But i provide the same formula in Suitescript variable directly it works all fine.

    Any idea as to why CASE WHEN is failing when pulled formula from Custom field and same passes when hardcoded in script?

19. Hi Mitisha,

    It’s likely has to do with the way strings are quoted or referenced. Can you post the segment that fails?

    Marty

20. Hi Marty,

    i just want to make a simple field calculation in an entity field. My field should divide the “hr agreed to work per feek” (also a custom field) by 40.
    I tried it in many different ways but it won´t work.

    Thanks for your help!

21. Hi Lisa,

    What values are measuring to get the total hours that have actually worked?

    Marty