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?

  • 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 Jun 10 '16 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 Jun 10 '16 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 Jun 10 '16 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 Jun 10 '16 at 2:40
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

  • Weird, these are all continuing to error out. Could there be some weird Currency data type getting involved? – GPP Jun 10 '16 at 2:48
  • 1
    What is the data type of Foo__r.Foo_Downloads__c ? – Anurag Bhardwaj Jun 10 '16 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 Jun 10 '16 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 Jun 10 '16 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 Jun 10 '16 at 3:24
1

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

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

  • I tried this as well. Feel like this almost deserves a case being logged to Salesforce support... – GPP Jun 10 '16 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.