0

Hoping someone can help with the logic to meet the following.

I have 4 picklist fields {Initial_Results_Review_Complete__c, Portal_QA_Tests_Complete__c , Data_Vol_Config__c , Model_Settings_Confirmed__c }

There is then an overall status field ( Onboard_Status ) that provides a status at a glance based on the combination of my 4 picklist values.

  • All 4 are set to 'yes' - Onboard_Status would be set to 'Completed'

  • If any of the 4 are 'No' ( or None ) - Onboard_Status would be set to 'In Progress - OK'

  • If any of the 4 are 'Open Issue' - Onboard_Status would be set to 'In Progress - Issue Detected'

I tried using CASE statements based on a combination IF /OR and IF/AND but it was getting unweildy and to capture all the permutations I think I would blow the field limit.

My feeling is this should be quite simple but am struggling to get my head around it.

This was the start of what I had but there must be a better/simpler way.

IF( ISPICKVAL( Initial_Results_Review_Complete__c , "Yes") && ISPICKVAL(  Portal_QA_Tests_Complete__c , "Yes") && ISPICKVAL(  Data_Vol_Config__c  , "Yes"), 

CASE( Model_Settings_Confirmed__c,
"Yes","Completed",
"No","In Progress - OK",
"Open Issue","In Progress - Issue Detected",
""),

IF( ISPICKVAL( Model_Settings_Confirmed__c, "Yes") && ISPICKVAL(  Portal_QA_Tests_Complete__c , "Yes") && ISPICKVAL(  Data_Vol_Config__c  , "Yes"), 

CASE( Initial_Results_Review_Complete__c,
"Yes","Completed",
"No","In Progress - OK",
"Open Issue","In Progress - Issue Detected",
""),

etc..

1 Answer 1

1

I guess you want to priorize this cases:

If some of the picklist values are "Oppen Issue" then Onboard_Status is set to "In Progress - Issue Detected", doesn't matter what the other values are.

After of this evaluation the same for "No", then Onboard_Status is set to "In Progress - OK"

After of this evaluation the same for "Yes", then Onboard_Status is set to "Completed"

Finally if some of your Picklists is empty or has other values your formula is set to null

If this are your requirements then here is your solution, hope that it helps :)

IF(OR(ISPICKVAL(Initial_Results_Review_Complete__c, "Open Issue"), ISPICKVAL(Portal_QA_Tests_Complete__c , "Open Issue"), ISPICKVAL(Data_Vol_Config__c , "Open Issue"), ISPICKVAL(Model_Settings_Confirmed__c , "Open Issue")) , "In Progress - Issue Detected", IF(OR(ISPICKVAL(Initial_Results_Review_Complete__c, "No"), ISPICKVAL(Portal_QA_Tests_Complete__c , "No"), ISPICKVAL(Data_Vol_Config__c , "No"), ISPICKVAL(Model_Settings_Confirmed__c , "No")) , "In Progress - OK", IF(AND(ISPICKVAL(Initial_Results_Review_Complete__c, "Yes"), ISPICKVAL(Portal_QA_Tests_Complete__c , "Yes"), ISPICKVAL(Data_Vol_Config__c , "Yes"), ISPICKVAL(Model_Settings_Confirmed__c , "Yes")) , "Completed", "")))
1
  • Perfect - this gives me exactly what I want.
    – DaveS
    Commented Jul 19, 2016 at 8:59

You must log in to answer this question.

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