+ Start a Discussion
Jeff TalbotJeff Talbot 

#Error! on formula field pulling data from a related object

In my custom "Jobs" table, I have a field "Preferred Truck", a lookup relationship to my custom "Trucks" Table. It is not a required field.

 

I want to show the Truck Fee (from the Trucks Table) on the Jobs Table. So I created a custom formula field (currency) in the Jobs table that gets its value from the "Truck Fee" currency field in the Trucks table.

 

Here's the problem:

If the Preferred Truck field is populated, the formula produces the correct result. But if it is not populated, the result is "#Error!" rather than $0.00.

 

Changing the option in "blank field handling" doesn't change anything.

 

Anyone have any insight on this? Thanks!

JakesterJakester
It's always nice to share the formula you made when you want help with it. Since you didn't, I'll start with the obvious and ask if you are using if(isnull() or, even better, the nullvalue() function. Please remember to use the Code button (it's near the happy face button on the toolbar) to share your code.
Jeff TalbotJeff Talbot

Thanks Jakester. My bad assumption that I didn't need to post my simple formula. The formula itself does not produce any syntax errors.

 

Truck__r.Truck_Fee__c

 

I also thought of and tried your suggestions. But neither of these formulas made any difference in the formula result. I still get "#Error!" as the formula result if the "Preferred Truck" lookup field is not populated.

 

 

IF(ISNULL(Preferred Truck__c ), 0, Truck__r.Truck_Fee__c)

 

NULLVALUE(Truck__r.Truck_Fee__c, 0)

 

Jeff TalbotJeff Talbot
NOTE: 2nd formula listed in prev msg, I do actually have the missing underscore (Preferred_Truck__c) in my formula.
SteveMo__cSteveMo__c
I'm just throwing this out there, but what about using LEN?

IF(LEN(Preferred Truck__c) = 0, 0,Truck__r.Truck_Fee__c)

 
Message Edited by Stevemo on 06-24-2009 11:09 AM
Message Edited by Stevemo on 06-24-2009 11:11 AM
SteveMo__cSteveMo__c
or this one

IF(LEN(Preferred Truck__c) < 1, 0,Truck__r.Truck_Fee__c)

 

JakesterJakester
I think both of Stevemo's formulas should do the trick, but I lean towards the =0 vs the <1 solution. Great job, Stevemo!
SteveMo__cSteveMo__c

The old quote "Even a broken clock is right twice a day" comes to mind...

 

 

PS.   I think you might have to use < 1 instead of  = 0.  I seem to recall = 0 not giving me the correct results (I have no clue why)

Message Edited by Stevemo on 06-24-2009 11:21 AM
Jeff TalbotJeff Talbot

I found the problem and the fix. I apologize for the somewhat of a wild goose chase. The problem (and the fix) was in details that I didn't provide.

 

My original "Truck Fee" formula, and each suggested formula, all work. The problem was that in my formula, I was referencing a currency formula field in the Truck table , and that currency formula field was producing "#Error!" on some Truck records (due to a simple divide/0 problem that I hadn't fixed). Once I fixed that formula so that it didn't produce "#Error!" on any Truck records, I got the expected result from the Jobs "Truck Fee" formula ("$0.00") when the "Preferred Truck" lookup field was null.

 

Again, sorry for the missing details. I didn't provide them originally because my logic told me that if there was no value in the "Preferred Truck" lookup field, then there was no numbers that calculate in the formula. How can the result be "#Error!" if there are no numbers to calculate?

 

To add to the confusion, if I reference a standard currency field in the formula, it does not behave like this. If the lookup field is null, the formula simply produces 0.00.

 

It's challenging to write this up, explaining it well and keeping it simple, so hopefully I've explained this well enough for someone else to understand. Maybe there is even a logical explanation for why the behavior is different with a custom field and a custom formula field?

 

Thanks again for the feedback and suggestions.