function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Claire NicolayClaire Nicolay 

Formula field for status based on 3 criteria

Can somebody help with a formula field called "status" as follows

if field "requested" is ticked, then "status"  equals "ongoing"
if field "date approved" contains a date, then "status" equals "approved"
if field "date rejected" contains a date, then the "status" equals "rejected"

many thanks!
Best Answer chosen by Claire Nicolay
SwarnaSankhaSinghSwarnaSankhaSingh
Hi Claire,

The suggestion from Ram and Akhil will both work and solve your problem; however, it with generate a syntax error for a missing parenthesis.

The modfied version which would work is as follows: 
IF
(
    Requested__c = TRUE,
    "Ongoing",
    IF
    (
        NOT(ISBLANK(Date_Approved__c)),
        "Approved",
        IF
        (
            NOT(ISBLANK(Date_Rejected__c)),
            "Rejected",
            ""
        )
    )
)
Now based on the limited information, I am guessing what you are trying to achieve here is to set the Status value on a record as Approved or Rejected or In-Review using three different fields. I am also guessing that you are using the OOTB Approval Process engine to manage the approval processes in SFDC. Based on these two assumptions, I am curious as to what is your rationale behind this solution design?

Another approach would have been to do the following:
  1. Create a picklist field named Status with 4 values namely Draft, In-Review, Approved and Rejected. You may also add a fifth value as Recalled.
  2. Set the default value for Status field to Draft.
  3. Create a Date field named "Approval/Rejection Date".
  4. Use the Approval process to control the value of the Status field.
  5. As part of the initial Submission actions, set the value of the Status field as "In-Review".
  6. As part of the Final Approval/Rejection action, set the value of the Status field as "Approved" or "Rejected".
  7. As part of the Final Approval/Rejection action, set the value of the "Approval/Rejection Date" as TODAY.
  8. In case you do want to allow requests to be Recalled then set the value of the Status field as "Recalled" in the Recall actions.
This would be a better and more efficient approach to handle the kind of functionality you are building.

Please excuse my interjection as I know you had simply asked for the correct syntax but looking at the names of the fields you were planning to use, I could not help but ask about the reasoning behind your approach.

Kind Regards,
Swarna.

All Answers

ram kiran verma gottumukklaram kiran verma gottumukkla
IF({!CheckboxField}, value_if_true, value_if_false)  ex:  IF(Requested_Download__C,50,1)
IF(NOT(ISBLANK(date approved field)), value_if_true, value_if_false )
IF(NOT(ISBLANK(date rejected field)), value_if_true, value_if_false )

 
Akhil AnilAkhil Anil
Hi Claire,

You can use a formula like this
  
IF(Requested__c = TRUE,"Ongoing",
  IF(NOT(ISBLANK(Date_Approved__c)),"Approved",
     IF(NOT(ISBLANK(Date_Rejected__c),"Rejected","")
	 )
  )
)

Hope that helps !
SwarnaSankhaSinghSwarnaSankhaSingh
Hi Claire,

The suggestion from Ram and Akhil will both work and solve your problem; however, it with generate a syntax error for a missing parenthesis.

The modfied version which would work is as follows: 
IF
(
    Requested__c = TRUE,
    "Ongoing",
    IF
    (
        NOT(ISBLANK(Date_Approved__c)),
        "Approved",
        IF
        (
            NOT(ISBLANK(Date_Rejected__c)),
            "Rejected",
            ""
        )
    )
)
Now based on the limited information, I am guessing what you are trying to achieve here is to set the Status value on a record as Approved or Rejected or In-Review using three different fields. I am also guessing that you are using the OOTB Approval Process engine to manage the approval processes in SFDC. Based on these two assumptions, I am curious as to what is your rationale behind this solution design?

Another approach would have been to do the following:
  1. Create a picklist field named Status with 4 values namely Draft, In-Review, Approved and Rejected. You may also add a fifth value as Recalled.
  2. Set the default value for Status field to Draft.
  3. Create a Date field named "Approval/Rejection Date".
  4. Use the Approval process to control the value of the Status field.
  5. As part of the initial Submission actions, set the value of the Status field as "In-Review".
  6. As part of the Final Approval/Rejection action, set the value of the Status field as "Approved" or "Rejected".
  7. As part of the Final Approval/Rejection action, set the value of the "Approval/Rejection Date" as TODAY.
  8. In case you do want to allow requests to be Recalled then set the value of the Status field as "Recalled" in the Recall actions.
This would be a better and more efficient approach to handle the kind of functionality you are building.

Please excuse my interjection as I know you had simply asked for the correct syntax but looking at the names of the fields you were planning to use, I could not help but ask about the reasoning behind your approach.

Kind Regards,
Swarna.
This was selected as the best answer
Claire NicolayClaire Nicolay
Thanks Swarna.You guessed well on the first part (status value to be set depending on a value of different field) but I am not using the approval process here because it is not something we approve, we just need to reflect in our tool whay has been approved or rejected by a third party.
Thanks for the formula anyway :-)
SwarnaSankhaSinghSwarnaSankhaSingh
Hi Claire,

If you are not using the Approval processes then it makes sense.

Kind Regards,
Swarna.