[SalesForce] Help with Formula field IF

I am creating a Formula field that returns texts when:

  1. Picklist_Status = Activated, the return text should be A else null
  2. Picklist_Status = Sold, the return should be B else null
  3. Picklist_Status = ChangeofMind , the return should be C else null

So these are 2 picklist fields that would update the formula field if a OR b OR C is true.

My FORMULA:

OR (
IF ( ISPICKVAL ( Status__c , 'Verified Complete' ), 'B' ,
IF ( ISPICKVAL ( OHSI_Status__c , 'Ok for Production' ), 'B+' ,
IF ( ISPICKVAL ( OHSI_Status__c , 'Activate' ), 'A'
'null' ))))

Error: Incorrect parameter type for function 'OR()'. Expected Boolean, received Text

I do not know where to put the OR.. these 3 should be OR

Best Answer

Editing this answer to reflect a better understanding of what you want to happen. I believe the ask is this:

  1. When verified complete, update to 'B'
  2. When ok for production, update to 'B+'
  3. When activated, update to 'A'.

As mentioned by Derek, formula fields don't update anything; they're calculated dynamically every time that they are viewed, but no data is stored internally. You can however simulate the above behavior with a formula field, essentially by reversing the order of your comparisons -- checking last to first.

  1. If activated, display 'A'
  2. Else if ok for production, display 'B+'
  3. Else if verified complete, display 'B'
  4. Else display 'null'
CASE(OHSI_Status__c,
  "Activate",
    "A",
  "Ok for Production",
    "B+",
  CASE(Status__c,
    "Verified Complete",
      "B",
    "null"
  )
)

Alternatively, if you want to actually store the data on the record, you can create a picklist field instead of a formula field, and give it three values, 'B', 'B+', 'A'. Then, create three workflow rules that run every time the record is updated to match the criteria for their related statuses, and then perform a field update to the new picklist, with the appropriate value. There are several benefits / considerations to using a real picklist instead of a formula, including,

  • Formulas can be less efficient if you have to use them in report / SOQL filters (displaying them is fine)
  • Reports can provide users with the three options to filter on ('A', 'B+', 'B'), whereas report filters on formulas don't have preset values to choose from (the user has to know the three options ahead of time)

Two final notes, last I checked, formulas should use double quotes when defining string literals (Apex is single quotes). Lastly, I used case statements for the picklists above, as I find them a bit more readable than chained IFs, especially with a lot of values. But that's personal preference; both will work.

Related Topic