You need to sign in to do that
Don't have an account?
#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!
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)
IF(LEN(Preferred Truck__c) = 0, 0,Truck__r.Truck_Fee__c)
IF(LEN(Preferred Truck__c) < 1, 0,Truck__r.Truck_Fee__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)
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.