2

I have a formula field created that should be displaying Red, Yellow, or Green (words not colors), and I'm getting a strange error that I just can't suss out. The problem comes after the word "Green" in the If statement that follows to determine Yellow or Red.

The Rank__c field is a picklist with individual values from 1 to 50, then ranged values such as "51 - 75". If the Account is ranked 1-50, or has one of the excluded Text values, it all works fine. If I use one of the ranged picklist values, the Green will calculate correct, but if it should be Yellow or Red, I get "#Error!" instead. Any help identifying where I have gone wrong is greatly appreciated.

IF(
NOT(
  OR(
    ISPICKVAL(Account__r.Rank__c, "Text1"),
    ISPICKVAL(Account__r.Rank__c, "Text2"),
    ISPICKVAL(Account__r.Rank__c, "Text3"),
    ISPICKVAL(Account__r.Rank__c, "Text4"),
    ISPICKVAL(Account__r.Rank__c, "Text5"),
    ISBLANK(TEXT(Account__r.Rank__c))
  )
),
IF(
  OR(
    AND(
     ISPICKVAL(Account__r.Rank__c, "201 - 300"),
      OR(
        SalesNumber1__c >=5000,
        SalesNumber2__c >=15000
      )
    ),
    AND(
      OR(
       ISPICKVAL(Account__r.Rank__c, "101 - 150"),
       ISPICKVAL(Account__r.Rank__c, "151 - 200")
      ),
      OR(
        SalesNumber1__c >=10000,
        SalesNumber2__c >=30000
      )
    ),
    AND(
     OR(
       ISPICKVAL(Account__r.Rank__c, "51 - 75"),
       ISPICKVAL(Account__r.Rank__c, "76 - 100")
      ),
      OR(
        SalesNumber1__c >=20000,
        SalesNumber2__c >=60000
      )
    ),
    AND(
      VALUE(TEXT(Account__r.Rank__c)) <=50,
      OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
      )
    )
  ), 
  "Green",
  IF(
    OR(
      SalesNumber1__c >0,
      SalesNumber2__c >0
    ),
    "Yellow",
    "Red"
  )
),
"N/A"

)

Edited to include fix

AND(
      **VALUE(TEXT(Account__r.Rank__c)) <=50, **was trying to convert a non numerical value to a number, changed line to
      NOT(CONTAINS(TEXT(Account__r.Rank__c), "-")), 
      OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
      )
    )
  • You might have better luck if you try to break this out into a couple separate formulas. It might increase your compile size slightly, but will allow you to check subsections of the formula independently. – Adrian Larson May 18 '16 at 20:32
  • Could you provide the logic on when each color has to be set, so we can help? – o-lexi May 18 '16 at 20:53
  • What Rank__c input values should yield "Yellow" and "Red"? Can you clarify your expectations in that regard? Have you verified you get "Green" for numeric values between 1 and 50? – Adrian Larson May 18 '16 at 22:46
  • The fix below actually solved the problem. I realized when comparing them what Oleksiy was pointing out. The problem was that it was trying to convert the ranged values to a number, and that was what was throwing the error, not the Yellow/Red section like I had thought. Stared at it so long I couldn't see it anymore. – De'Anna May 18 '16 at 23:00
2

You are getting error in this formula VALUE(TEXT(Account__r.Rank__c)) when Account__r.Rank__c is a range.

Replace this block

AND(
    VALUE(TEXT(Account__r.Rank__c)) <=50,
    OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
    )
)

with the following:

AND(
    NOT(CONTAINS(TEXT(Account__r.Rank__c), "-")),
    OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
    )
)

Or you can have some more advanced logic.

| improve this answer | |
  • I think you need to keep the VALUE(TEXT(...)) bit. It just needs to come after the - check. – Adrian Larson May 18 '16 at 21:28
  • This was the reason I included "... you can have some more advanced logic" at the end - it's not clear whether there are some other Account__r.Rank__c picklist values that has to be not considered. But when you will include VALUE(TEXT(...)) it will be giving the same #Error! error, unless when it would be like IF( NOT(CONTAINS(TEXT(Account__r.Rank__c), "-")), VALUE(TEXT(Account__r.Rank__c)) <=50, False), but I haven't tested this, and there might be some other values that will result in #Error! – o-lexi May 18 '16 at 21:37
  • Perhaps ISNUMBER would be appropriate here... – Adrian Larson May 18 '16 at 21:46
  • Thank you for your response. That's actually not where the error is coming from, it's the statement below that. The one that determined Yellow or Red. All it is supposed to do is check to see if either of the two Sales Number fields are greater than 0. The problem is, it still seems to be looking at the Rank picklist, and it shouldn't be. – De'Anna May 18 '16 at 22:35
  • @De'Anna You are wrong. Create another formula field of Number type and have it as VALUE(TEXT(Account__r.Rank__c)). Open any record that has Account__r.Rank__c value as a range and you'll see. – o-lexi May 18 '16 at 22:39

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.