You need to sign in to do that
Don't have an account?
lujreyes
Catch error on formula fields
Hi,
I have this formula field. The return type of the field is Text and for some reason all that it shows is #Error!, surely an exception is happening somewhere in the formula. I just have some simple comparison on an if statement. I don't know what could possibly be failing and i don't know how to catch an Exception.
Any suggestions on what approach could i take?
Thanks in advance for any helps.
Luis
hi there what other fields is your formula referencing? Are they pehaps lookup fields? Be good if you could paste the formula as well
Paul
Can you post your Formula using the Code Clipboard and list all of the Fields and their Datatype and Picklist Values (if there are any) that the Formula is referencing?
Yes, there are other formulas that I'm making references to. Some of those formulas also generate errors. The error on that formula is a Div by 0, i can't prevent it because i cannot optimize more and if i add validations i overflow the limit on compiled size.
The strange thing is that even when the referred formula (CPI) throws an error, sometimes the related formula (Project_Cost_Status_Text) throws an error and sometimes it doesn't.
There are a lot of formulas, i paste them here to see if you can help me optimize them. Just for the records and relating to my original question. Is there a way to catch errors in formula fields?
Any help will be greatly appreciated
Formulas:
Cost = Autogenerated by an App
Complete = Autogenerated by an App
BLND_Work_In_Actual_Hours = Autogenerated by an App
BLND_Duration_In_Business_Days = Autogenerated by an App
ExternalProjectCostStatus = Picklist which returns a text
PlannedCostPerHour = BLND_Cost__c / (BLND_Duration_In_Business_Days__c * 8 )
EV = BLND_Cost__c * BLND_Complete__c
RealCost = BLND_Work_In_Actual_Hours__c * BLND_PlannedCostPerHour__c
CPI = BLND_EV__c / BLND_RealCost__c
Note: Sometimes RealCost is 0. this formula throws an error which i cannot prevent or verify because there a lot of formulas that uses it and doesn't compile because a size too big.
ForecastedCost = if(BLND_CPI__c=0,0,
if(BLND_CPI__c != 0,BLND_RealCost__c + ((BLND_Cost__c - BLND_EV__c)/BLND_CPI__c),0))
Note: Sometimes this formula throws an error too.
ProjectCostStatusText = if(TEXT(BLND_ExternalProjectCostStatus__c) = "Dentro de presupuesto", "Green",
if(TEXT(BLND_ExternalProjectCostStatus__c) = "Riesgo", "Yellow",
if(TEXT(BLND_ExternalProjectCostStatus__c) = "Fuera de presupuesto", "Red",
If( BLND_CPI__c < 0.91, "Red",
If( BLND_CPI__c < 0.96, "Yellow",
If (BLND_CPI__c >0.95, "Green", "Yellow"))))))
Note: Somestimes this formula throws an error too
Okay, without being able to see inside your SFDC.org my guess is that one of your formulas is trying to perform an Arithmetic Function on a field that is blank (NULL).
Check to see how your Formula is set to Treat Blank Fields. If it is set to "Treat Blank Fields as Blank" change it to "Treat Blank Fields as Zeroes"
Hey, Thanks for your reply
Everything is set to "Treat Blank Fields as Zeroes"
What other approaches could i try?
Thanks again
Then my guess is that somewhere in the record you have an Arithmetic Error (I can't help you there) I think you're gonna have to pull out a calculator and test the formula manually to verify the results.
Yes, there surely is an arithmetic error somewhere. In the CPI formula sometimes a div by 0 occurs, that's why i wanted to see if there is a way to catch errors. I cannot modify the formulas without provoking a "Formula too big" compilation error.
Is there a way to catch errors in formulas?
Thanks for your answer,
Best Regards
Luis
The only thing I can suggest is that you add an escape if the value is 0
ok, that's not possible because of the problems with the size of the formula that i'm facing.
Please, i just want to know if there is a way to catch an error in a Formula?
Thanks again
Unfortunately I don't think there is one