function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
lujreyeslujreyes 

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

paulmagpaulmag

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

Steve :-/Steve :-/

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? 

lujreyeslujreyes

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

 

 

 

 

 

Steve :-/Steve :-/

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"   

lujreyeslujreyes

Hey, Thanks for your reply

 

Everything is set to "Treat Blank Fields as Zeroes"

 

What other approaches could i try?

 

Thanks again

Steve :-/Steve :-/

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. 

lujreyeslujreyes

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

Steve :-/Steve :-/

The only thing I can suggest is that you add an escape if the value is 0

lujreyeslujreyes

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

Steve :-/Steve :-/

Unfortunately I don't think there is one