Help with Formula field IF

Question

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

Answer 1

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.

Answer 2

I think that part of your confusion lies in what a formula field is.

A formula field is something that is computed every time it is accessed from a persisted SObject (i.e. when you query this field via SOQL, or view the field on a Visualforce page, the field value is re-computed). There's nothing that needs to be "updated", it happens automatically.

Let's take the following example where we update a field value in Apex. Keep in mind that this is an example, and not meant to be able to be copy/pasted verbatim.

// We can directly assign a query result to a single SObject instance, but this isn't
//   safe.
// It only works when the query returns exactly 1 result
MyObject__c myObj = [SELECT Id, My_Formula__c, Status__c, OSHI_Status__c FROM MyObject__c LIMIT 1];

// For sake of example, let's say that Status__c == null, and OHSI_Status__c == 'Ok for Production'
System.debug(myObj.My_Formula__c); // This debug should then output 'B+'

// Now, let's update a field
myObj.OSHI_Status__c = 'Activate'

// This debug should output 'B+'
// We've updated the field value in memory only. The formula value is calculated on the database
//   side of things
System.debug(myObj.My_Formula__c);

// Save our changes
update myObj;

// Now that we've made a change to the stored value of one of the fields that
//   contributes to the formula field, we can ask for the same record again
//   (which will cause the formula field to be recalculated
MyObject__c updatedObj = [SELECT Id, My_Formula__c, Status__c, OSHI_Status__c FROM MyObject__c WHERE Id = :myObj.Id];

// Re-querying the record, after its data has been updated, causes the formula to be
//   re-calculated
System.debug(myObj.My_Formula__c); // This debug should output 'A'

Thus, the OR() you're trying to include is not necessary.