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.
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
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
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
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
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
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.
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
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!
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.
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?
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
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
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
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?
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?
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.
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.
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?
Hi Mitisha,
It’s likely has to do with the way strings are quoted or referenced. Can you post the segment that fails?
Marty
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!
Hi Lisa,
What values are measuring to get the total hours that have actually worked?
Marty