function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Tracy Oden 10Tracy Oden 10 

Custom formula field receives error when a value is left blank.

I am receing a #Error in many of my formula fields that were working properly for moths until the last two days. I then received a bunch of unknow field update errors. I deactivated the workflow, now I am getting errors on formula fields when one of the fields referenced in the formula is left blank. As stated this was not happening before. I have many fields that this is happening to. I tested the formula and treid both 'Blank field handling options'. Can someone help?
Best Answer chosen by Tracy Oden 10
Ketankumar PatelKetankumar Patel
IF( 
     AND (  Miles_per_gallon__c =0, Gas_Price_per_gallon__c = 0, 
                Commute_Days_Per_Week__c = 0, 
                Avg_Number_Practioners_Local_Commute__c = 0
              ),
    0, 
       (Driving_Distance_Round_Trip_miles__c / Miles_per_gallon__c * Gas_Price_per_gallon__c * Commute_Days_Per_Week__c * 52 * Avg_Number_Practioners_Local_Commute__c)
    )

If you still get #Error then you have to go the formula referenced in this formula field and fix those field. Basically you have to fix stuff from bottom to top. (Referenced Formula fields first and then top formula fields which use refereced formula result in them). 

I hope this would help. 

All Answers

Pramodh KumarPramodh Kumar
could you please share the exact formula and error message you are getting.
Ketankumar PatelKetankumar Patel
Hi Tracy oden, 

You are facing Division by zero error in your formula that is why it shows like #Error in formula fields. In your formula there should be logic that devides some value with zero. You can avoid by this error if you check division value is zero or null or blank then don't devide and default some values in your formula or change its value to 1 and then devide. 

if you post your formula logic then I can help more. 
Tracy Oden 10Tracy Oden 10
HI,

Thanks for your response.  I have many formula fields on various records. Many of which now have this error. I don't understand how after nearly 6 months these formulas worked without a hitch only to have them no longer work starting two days ago.  Here is an eoample of one of them:

Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients__c + Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Radio__c + Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Print__c + Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Web__c+ Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_TE__c+ Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_PR__c+ Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_WP__c+ Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_VM__c

If one of the fields referenced above is blank, I get the #Error! message in the field rather than the results.  Thanks in advance for your help!​​
Ketankumar PatelKetankumar Patel
Hi Tracy, 

After looking at your formula I thought this could also cause #Error in your formula result. You should check in your formula field what the blank field handling setting is. My guess is, it has been selected "Treat blank fields as blanks" you should change it to "Treat blank fields as zeroes" and see if this resolves your issue. 

User-added image





 
Tracy Oden 10Tracy Oden 10
Hi, it is already set to treat as zeros. I tried both. Neither is working.
Tracy Oden 10Tracy Oden 10
User-added image
Ketankumar PatelKetankumar Patel
Hi, Is this field (Total Anticipated Calls Daily) giving you the #Error or any other formula field? 
Pramodh KumarPramodh Kumar
Hey Tracy,

Can you try this. 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients__c) +
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Radio__c ==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Radio__c ) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Print__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Print__c) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Web__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_Web__c) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_TE__c==null,0,Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_TE__c) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_PR__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_PR__c) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_WP__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_WP__c) + 
if(Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_VM__c==null,0, Productivity_Benefits_Forecast__r.Promotions_Revenue_Forecast__r.Avg_Daily_Purchasing_Clients_VM__c)

Thanks,
pRAMODH.
 
Tracy Oden 10Tracy Oden 10
I just tried your formula. I got another error message. See below: Error: Invalid Data. Review all error messages below to correct your data. Formula(s) that reference this field are no longer valid: Compiled formula is too big to execute (5,113 characters). Maximum size is 5,000 characters (Related field: Formula)
Pramodh KumarPramodh Kumar
remove the last condition and try it. If that works change the api names to smaller one
Tracy Oden 10Tracy Oden 10
I am more of a declarative developer (just for background purposes). I am not sure of what ‘condition’ you are referencing.
Tracy Oden 10Tracy Oden 10
After re-reading; I understood what you were asking. I removed the last condition of the formula. I saved the record successfully. However, I still have the same error.
Tracy Oden 10Tracy Oden 10
User-added image
Ketankumar PatelKetankumar Patel

Hi Tracy, 

Are these Avg_Daily_Purchasing_Clients__c, Avg_Daily_Purchasing_Clients_Radio__c, Avg_Daily_Purchasing_Clients_Print__c ...and all others 
fields on Promotions Revenue Forecast object formula fields? 

if yes then you should fix those first (they should be facing Division by zero error)  or show me what's the formula for these fields?
Tracy Oden 10Tracy Oden 10
Here is an example of the errors. The field in which the formula I provided is shown at the top.There are many fields on this one object and several others. See below

User-added image

Can you give me an example of a Division by zero formula.
Ketankumar PatelKetankumar Patel
One reason that #Error! can be produced in a formula result is when a formula tries to divide by 0

Here is the Solved Answer: https://developer.salesforce.com/forums/?id=906F00000008wx6IAA

I recommend you should go to each of these Avg_Daily_Purchasing_Clients__c, Avg_Daily_Purchasing_Clients_Radio__c, 
Avg_Daily_Purchasing_Clients_Print__c.....and all other formula fields and make sure you are not dividing anything with Zero Result. 
Tracy Oden 10Tracy Oden 10
Thanks, that will take quite a bit of time. I will do so. When I come across one, I’ll let you know. I’ll contact tomorrow.
Tracy Oden 10Tracy Oden 10
Hi, I am still trying to solve this Division by Zero issue. Taking your advice, here is the formula for one of my Avg. Daily Purchasing Clinets formulas.

Total_In_Promotion_Purchasing_Clients_TE__c / Total_Promotion_Operating_Days_TE__c

The above formula is returning an error because it is a division by zero formula (apparently).  To correct this formula, I worte the the following but it doesn't seem to be working:

IF(AND(Total_In_Promotion_Purchasing_Clients_TE__c , Total_Promotion_Operating_Days_TE__c <> 0.00),(Total_In_Promotion_Purchasing_Clients_TE__c / Total_Promotion_Operating_Days_TE__c),NULL)

Got an error message​: Incorrect parameter type for AND (), expected Boolean, received Number.

Can someone help me write a IF statement for the formula above so as not to return a division by zero errror.​
​
Ketankumar PatelKetankumar Patel
Hi Tracy Try this. 

IF(Total_Promotion_Operating_Days_TE__c = 0, 0, Total_In_Promotion_Purchasing_Clients_TE__c / Total_Promotion_Operating_Days_TE__c)

//IF(Your_Condition, Logic_If_Condtion_Is_True, Logic_if_Condtion_Is_False)
Tracy Oden 10Tracy Oden 10
That worked!!!!! Thanks so much. I will apply this formula to all of my other similar fields. I have some more that I will need to correct. I’ll post these later if I need help. You are awesome!!!!
Tracy Oden 10Tracy Oden 10
Well, I'm back.  A new error has occured on the other fields using the same fomula.  The formula you provided worked on one of the fields but the other ones it is not. I got the following error: 

Error: Invalid Data.
Review all error messages below to correct your data.
Formula(s) that reference this field are no longer valid: Compiled formula is too big to execute (5,208 characters). Maximum size is 5,000 characters (Related field: Formula)

(​see screenshot)​

User-added image

This was the formula:
IF(Total_In_Promotion_Purchasing_Clients_W__c = 0, 0, Total_In_Promotion_Purchasing_Clients_W__c / Total_Promotion_Operating_Days_Web__c)

​
Tracy Oden 10Tracy Oden 10
Thanks for helping. I figured out what was wrong this time. The 'Anticipated Calls Daily' field referenced above which you provided a division by zero formula example several days ago was updated to this formula back then, When I added the new division by zero formula to the 'Avg. Daily Purchasing Clients' formula that you helped with today; I got the new error above. To fix; I had to just reveert to my original Anticipated Calls formula.

The fields are now all working on one object. Thanks for your help.

As I said, whatever happened almost a week ago (I think it is on the SFDC side related to the Lightingin experience upgrades); I now have these problems.

I am now working on a new formula. I hope you can help. It may fix for this entire object as well.  I am not very good at writing IF formula's yet.  The original is:

Driving_Distance_Round_Trip_miles__c / Miles_per_gallon__c * Gas_Price_per_gallon__c * Commute_Days_Per_Week__c * 52 * Avg_Number_Practioners_Local_Commute__c

I need an IF formula to circumvent the divide by zero issue as well. 

Can you or someone help again.  Your help is immeasureable as I had so many objects and fields to fix. ​ I hope this will close out this object.

​​
​​
Ketankumar PatelKetankumar Patel
IF( 
     AND (  Miles_per_gallon__c =0, Gas_Price_per_gallon__c = 0, 
                Commute_Days_Per_Week__c = 0, 
                Avg_Number_Practioners_Local_Commute__c = 0
              ),
    0, 
       (Driving_Distance_Round_Trip_miles__c / Miles_per_gallon__c * Gas_Price_per_gallon__c * Commute_Days_Per_Week__c * 52 * Avg_Number_Practioners_Local_Commute__c)
    )

If you still get #Error then you have to go the formula referenced in this formula field and fix those field. Basically you have to fix stuff from bottom to top. (Referenced Formula fields first and then top formula fields which use refereced formula result in them). 

I hope this would help. 
This was selected as the best answer
Tracy Oden 10Tracy Oden 10
Man, you rock!!!!! That one fixed the entire object and some of the next. I get the concept of fixing bottom up. My problem is learning how to write the IF statements needed. I am new at that. I have a few errors on my next object. I will try to go it alone because I am trying to learn how to write these statements to avoid this problem in the future,, but if I need help; I hope you don’t mind one or two more. I’ll let you know. Thanks a million.