2

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

2
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – David Reed
    Jan 28, 2020 at 19:43
  • Looks like you've accidentally created two accounts. Could you please reach out to Stack Exchange support through the Contact link at the bottom of every page to have your accounts merged? I'm going to close the question from the other account as a duplicate of this one.
    – David Reed
    Jan 28, 2020 at 19:44

2 Answers 2

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.

2
  • @Kasper Okay, So I have 2 picklist fields: OHSI_Status__c & Status__c .. When I change the Status__c to Verified Complete the formula field will return 'B' , then that picklist value stays on the record, second, I change the OHSI_Status__c to Ok for Production then the formula field STILL returns to 'B' and the picklist value stays on the record as well,, then when I change OHSI_Status__c to Activate,,the formula field is not updating to 'A'... it remains to B.
    – Xde
    Jan 28, 2020 at 19:23
  • @Xde I updated my answer based on the additional information in your comment above. If it helps to solve your problem, please remember to accept the answer as correct. Thanks! Jan 28, 2020 at 22:40
0

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.

You must log in to answer this question.

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