Validation Rule referencing Case Function Formula Field

Question

I am trying to create a validation rule on a custom object that prevents a specific status picklist value from being selected if a number field on the custom object is greater than a formula field on the Contact standard object. The formula field is a case function that looks at a separate picklist field and spits out a number like this but with more values:

CASE(TEXT(picklistfield__c),
     "A", 180,
     "B", 60,
     100)

The validation rule I have currently looks something like this:

AND(
    ISPICKVAL(status__c, "Value"),
    Number field > formula field
)

The problem I am running into is the formula field appears to always be set to 100 for the validation rule even if it's spitting out a different number for a given record. Is this intended? How do I rectify this?

Answer 1

First, your formula shouldn't be using TEXT() here. I'm not sure that's the problem, but CASE() doesn't need TEXT() to work.

CASE(PicklistField__c, 'A', 180, 'B', 60, 100)

Second, if this is a lookup relationship, and the lookup relationship is empty, you'll get the default value when it calculates.

That means you probably need something like:

AND(
  ISPICKVAL(Status__c, 'Value'), 
  NOT(ISBLANK(Contact__c)), 
  NumberField__c > Contact__r.FormulaField__c
)

Having genericized your question as you have, it's hard to tell if anything else is getting in the way, but these would be my first two suggestions.

Answer 2

Why dont you try this:

AND(
    ISPICKVAL(status__c, "Value"),
    Numberfield > CASE(Contact.PicklistField__c, 'A', 180, 'B', 60, 100)
)