0

Wrestled with this one for a while before deciding to ask the SFDC SE community's help: Basically, a formula field on a Child object is looking up a parent Object value

IF(( ISNULL(Foo__r.Foo_Views__c) ), 
( Quoted_Rate__c * 1000 / Foo__r.Foo_Downloads__c ), 
(( Quoted_Rate__c * 1000)/ Foo__r.Foo_Views__c ) 
)

When the Foo_Views__c is not Null, the formula correctly calculates the value-- and existing fields that effectively broke this formula into two fields prove this.

However, when the value is Null (and therefore true, the value returned is an #Error)

Is there some issue or unspoken rule I keep missing? Thanks!

Edit: to confirm, the Foo_Downloads__c field has a value populated for both the records I tested with, and a separate field populates a value just using this formula: ( Quoted_Rate__c * 1000 / Foo__r.Foo_Downloads__c ) This field's data type is Currency, and while our instance is only using one currency, could this be causing an issue?

Improve this question
4
  • 2
    Is it possible that both Foo__r.Foo_Views__c AND Foo__r.Foo_Downloads__c are both empty or a zero value? That might give you a divide-by-zero error, resulting in #Error.
    – shannonsans
    Commented Jun 10, 2016 at 1:50
  • 1
    what do you want to return if Foo_Views__c is not NULL and Foo_Downloads__c is NULL?
    – Andrei.Z
    Commented Jun 10, 2016 at 2:12
  • @Andrei.Z I am fine with it Error'ing out then, because from a business-logic standpoint, if neither of those fields is populated, the field should error out. The issue is that the Foo_Downloads__c field is populated with a value, but the formula still errors out.
    – GPP
    Commented Jun 10, 2016 at 2:25
  • 1
    You can try the suggested solution. It checks all scenarios and you will sure that value of Foo_Downloads__c field is not null and 0.
    – Anurag Bhardwaj
    Commented Jun 10, 2016 at 2:40

4 Answers 4

Reset to default
1

You can use below code:

IF(( ISNULL(Foo__r.Foo_Views__c) ), ( IF(( AND(NOT(ISNULL(Foo__r.Foo_Downloads__c)), Foo__r.Foo_Downloads__c > 0) ), Quoted_Rate__c * 1000 / Foo__r.Foo_Downloads__c, 0 )), (( Quoted_Rate__c * 1000)/ Foo__r.Foo_Views__c ) ) You need to check that Foo__r.Foo_Downloads__c is not null and its value is greater than 0.

Hope it helps you

Improve this answer
9
  • Weird, these are all continuing to error out. Could there be some weird Currency data type getting involved?
    – GPP
    Commented Jun 10, 2016 at 2:48
  • 1
    What is the data type of Foo__r.Foo_Downloads__c ?
    – Anurag Bhardwaj
    Commented Jun 10, 2016 at 2:53
  • 1
    You can debug value of Foo__r.Foo_Download__c, if it seems non-numeric value then will think of another solution.
    – Anurag Bhardwaj
    Commented Jun 10, 2016 at 2:57
  • 1
    I have tried this in my org and created a currency formula field that perform operation on number. It executes successfully and value displayed on currency formula field.
    – Anurag Bhardwaj
    Commented Jun 10, 2016 at 3:19
  • 1
    Could you please do one thing. Create a Currency(formula) field on your object and try to inset value on that field when any record created. If it successfully insert value in field then it might be a different scenario.
    – Anurag Bhardwaj
    Commented Jun 10, 2016 at 3:24
1

Can you please include a null and >0 check for Foo__r.Foo_Downloads__c before the division is performed.

Improve this answer
1 
IF(( ISNULL(Foo__r.Foo_Views__c) ), 
IF(OR(ISNULL(Foo__r.Foo_Downloads__c),Foo__r.Foo_Downloads__c==0),
(0),
( Quoted_Rate__c * 1000 / Foo__r.Foo_Downloads__c )), 
(( Quoted_Rate__c * 1000)/ Foo__r.Foo_Views__c ) 
)

instead of 0 in (0) put any value you want to return when Foo_Downloads__c = null or Foo_Downloads__c = 0

Improve this answer
1
  • I tried this as well. Feel like this almost deserves a case being logged to Salesforce support...
    – GPP
    Commented Jun 10, 2016 at 2:59
0

Thank you all so much for your contributions! I ended up realizing that due to the business requirements of this object, the 'downloads' field needed to be added as a local field in the same object that this Formula field in question was in, so I was able to resolve that.

I suspect that the Error came from either an Order of Operations e.g., the Quoted_Rate__c * 1000 / Foo__r.Foo_Downloads__c statement needed additional parentheses wrapped around the multiplication operation, and because number fields are automatically written as '0' when null, I likely would have been fine using an IF(Foo__r.Foo_Views__c = 0, ...) as well.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .