+ Start a Discussion
Kyle DudleyKyle Dudley 

Greater than OR Less Than with ISPICKVAL

A lot of Picklists in our Org are sorted in the order in which a give process is completed. Is there a way to use formulas based on the sort order of a given picklist. I know this meta-data is available because if you sort by a non-alphabetical sorted picklist in a list view, it gives results in the defined order of the picklist values. So is there any way in a Validation Rule for example where you can have it equal true if you go past some picklist value OR have yet to reach a picklist value without explicitly covering all pick values before or after the picklist value in question. I will give a brief example below.

I have a date field: Date_Field_c
I have a Picklist field: Picklist_Field_c 
which has values: "Stage 1", Stage 2", "Stage 3", "Stage 4", "Stage 5"
with Stage 1, being first and Stage 5 being last in the defined sort order. 

I want the date field to be populated for Stages 3, 4, and 5.

Instead of writing a formula like:
AND(ISBLANK(Date_Field_c),
    OR(
       ISPICKVAL(Picklist_Field_c,"Stage 3"),
       ISPICKVAL(Picklist_Field_c,"Stage 4"),
       ISPICKVAL(Picklist_Field_c,"Stage 5")
    )
)

OR a formula like: 
AND(ISBLANK(Date_Field_c),
       NOT(ISPICKVAL(Picklist_Field_c,"Stage 1")),
       NOT(ISPICKVAL(Picklist_Field_c,"Stage 2"))
)

I would prefer to write something like:
AND(ISBLANK(Date_Field_c),Picklist_Field_c>ISPICKVAL(Picklist_Field_c,"Stage 2")

We have a lot of picklists with a large number of values. Otherwise, this would not be a big deal to write the other 2 ways, but I can think of several use cases where something like I am looking for would be handy. I understand there be some additional requirement to define the record type since the sort order is defined at the record type level. But I could just add $RecordType.DeveloperName = "Record Type X" to account for that. 
Nikhil_KhetanNikhil_Khetan
Hi Kyle,

Please mark this as best answer if you find my answer's helpful.

Picklist fields are text values. They cannot be compared with with mathematical operators.

It could be possible only if all the values in your picklist was numberic like 1,2,3,4,5
In this case you can convert that into number and try the formula you wrote above.

But unfortunately the Alphanumric values cannot be compared using mathematical operators like < or >.

Thanks,
Nikhil