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

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", "")))
  • Perfect - this gives me exactly what I want. – DaveS Jul 19 '16 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.