I have a field that is a picklist field. Then I have a field that is a formula field that calculates a score based upon the option that is chose from the picklist field. This formula is a Case formula and has 10 cases. All of the cases work with the exception of 1 and 8. I have checked everything and can't figure out why it isn't working. I have copied and pasted the picklist values into my Case formula so I know they are correct. Can anyone help?

"1- Exceptional. Well in excess of debt service. Coverage ratio much higher than policy guidelines. Access to public markets and ready access to other lenders.", 1, 
"2- Strong. Easily covers debt service. Coverage ratio higher than policy guidelines. Limited access to public markets.", 2, 
"3- Solid. Easily covers debt service. Coverage ratio generally meets or exceeds policy guidelines. Acceptable to other lenders.", 3, 
"4- Adequate to cover debt service. Coverage ratio below policy guidelines. Some inconsistency is evident but generally acceptable to other lenders.", 4,
"5- Inconsistent. May be marginally adequate to cover debt service at present, or slightly inadequate with improvement expected. Other financing alternatives may be questionable.", 5, 
"6- Same characteristics as a marginal credit; but Management has determined these loans require enhanced monitoring through Special Assets Committee.", 6, 
"7- Inadequate to cover debt service at present. Expected to be temporary.", 7, 
"8- Clearly inadequate to cover debt service. Uncertain or unfavorable outlook.", 8, 
"9- Clearly inadequate to cover debt service. Improvement highly unlikely.", 9, 
"10- Clearly inadequate to cover debt service, no reasonable chance for improvement.", 10, 
  • In your picklist, please check your Value vs the API Name. Are these identical? You may need to use the API name of the picklist value. You may have entered a value for the picklist then went back and edited the value to have the API Name stay the same.
    – ryangreene
    Oct 23, 2019 at 18:44
  • I used the API name when copying and pasting but when I was checking for the cause I looked at that and they are identical.
    – Jaimie
    Oct 23, 2019 at 18:54
  • Welcome to SFSE! Please log into your original account to make edits. If you have lost access to this account, please reach out to Stack Exchange support via the Contact link at the bottom of the page for assistance.
    – David Reed
    Oct 23, 2019 at 18:58
  • Please stop posting answers as conversation. See How to Answer. Use an edit to add information to this question, or a comment to request clarification.
    – David Reed
    Oct 23, 2019 at 21:40

1 Answer 1


I don't know whay it's not working as-is - we'd need to see the original field to confirm. But I propose this alternative approach:

VALUE(LEFT(TEXT(Cash_Flow_Repayment_Sources__c), FIND("-",TEXT(Cash_Flow_Repayment_Sources__c))-1))

This finds the first dash character in the value of the selected picklist item, takes the characters to the left of it, and then turns them back into a number.

You must log in to answer this question.

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