Formula Help Needed - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Kate RussellKate Russell 

Formula Help Needed

I have a picklist field (StageName) that need to be translated into a single field. However if the Record Type is X then the values returned are one set of values, but if the Record Type is A, B, C, etc. then the returned values are a different set of returned values.

1) Is this the right way to write this formula?
2) How do I resolve the error message "Incorrect number of parameters for function 'CASE()'. Expected 20, received 21"

IF( RecordType.Name = 'GTF',

(CASE(StageName,
"In Progress","In Progress",
"Submitted","Submitted",
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Under Review",
"External Review","Under Review",
"Internal Review","Under Review",
"Recommended for Fellowship","Under Review",
"Approved","Approved",
"Declined","Declined",
"Closed","Closed")),

(CASE(StageName,
"In Progress","In Progress",
"Submitted","Submitted",
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Submitted",
"External Review","Submitted",
"Internal Review","Submitted",
"Recommended for Meeting","Submitted",
"Recommended for Funding","Submitted",
"Withdrawn by Applicant","Withdrawn",
"Withdrawn by NGS","Withdrawn",
"Holding","Submitted",
"Approved","Approved",
"Declined - Invited for Resubmission","Declined",
"Declined","Declined",
"Closed","Closed")))
Best Answer chosen by Kate Russell
Amnon KruviAmnon Kruvi
I think I may have forgot a bracket... 
IF( RecordType.Name = 'GTF',

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Under Review",
"External Review","Under Review",
"Internal Review","Under Review",
"Recommended for Fellowship","Under Review",
StageName),

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Submitted",
"External Review","Submitted",
"Internal Review","Submitted",
"Recommended for Meeting","Submitted",
"Recommended for Funding","Submitted",
"Withdrawn by Applicant","Withdrawn",
"Withdrawn by NGS","Withdrawn",
"Holding","Submitted",
"Declined - Invited for Resubmission","Declined",
StageName) 
)

All Answers

Amnon KruviAmnon Kruvi
Hi Kate, 

You have the right idea, but you've over engineered it a little by trying to map every single possible value instead of just the ones you want cbanged. The error you're seeing is because you forgot to add a default value for the case (what to return if it isn't any of the values listed).

Combine the two issues, and we can resolve both in one fell swoop by making the default value the same as the stage name, and removing the stages that don't need changing from the list. 

How about something like this:
IF( RecordType.Name = 'GTF',

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Under Review",
"External Review","Under Review",
"Internal Review","Under Review",
"Recommended for Fellowship","Under Review",
StageName),

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Submitted",
"External Review","Submitted",
"Internal Review","Submitted",
"Recommended for Meeting","Submitted",
"Recommended for Funding","Submitted",
"Withdrawn by Applicant","Withdrawn",
"Withdrawn by NGS","Withdrawn",
"Holding","Submitted",
"Declined - Invited for Resubmission","Declined",
StageName
)

 
Madan Lal BakoliyaMadan Lal Bakoliya
Hi Kate,

You just missed the else part on your formula with case function, have a look here 
IF( RecordType.Name = 'GTF',
	(CASE(StageName,
					"In Progress","In Progress",
					"Submitted","Submitted",
					"Requested to Resubmit","Resubmission Requested",
					"Recommended for Declination","Under Review",
					"External Review","Under Review",
					"Internal Review","Under Review",
					"Recommended for Fellowship","Under Review",
					"Approved","Approved",
					"Declined","Declined",
					"Closed","Closed","YOUR ELSE RESULT")
	),

	(CASE(StageName,
					"In Progress","In Progress",
					"Submitted","Submitted",
					"Requested to Resubmit","Resubmission Requested",
					"Recommended for Declination","Submitted",
					"External Review","Submitted",
					"Internal Review","Submitted",
					"Recommended for Meeting","Submitted",
					"Recommended for Funding","Submitted",
					"Withdrawn by Applicant","Withdrawn",
					"Withdrawn by NGS","Withdrawn",
					"Holding","Submitted",
					"Approved","Approved",
					"Declined - Invited for Resubmission","Declined",
					"Declined","Declined",
					"Closed","Closed","YOUR ELSE RESULT")
	)
)

Thanks,
Madan Bakoliya
Amnon KruviAmnon Kruvi
I think I may have forgot a bracket... 
IF( RecordType.Name = 'GTF',

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Under Review",
"External Review","Under Review",
"Internal Review","Under Review",
"Recommended for Fellowship","Under Review",
StageName),

CASE(StageName,
"Requested to Resubmit","Resubmission Requested",
"Recommended for Declination","Submitted",
"External Review","Submitted",
"Internal Review","Submitted",
"Recommended for Meeting","Submitted",
"Recommended for Funding","Submitted",
"Withdrawn by Applicant","Withdrawn",
"Withdrawn by NGS","Withdrawn",
"Holding","Submitted",
"Declined - Invited for Resubmission","Declined",
StageName) 
)
This was selected as the best answer
Kate RussellKate Russell
Thanks, Amnon & Madan! I ended up using Madan's solution b/c StageName is a picklist value and the formula didn't like translating that as the false value. 

I ran into a different issue with the following error message, "Unable to use this formula containing derived values because the field is included in the following roll-up summary field(s): Account.Open Awards. To continue, remove references to derived values such as current date, current user, or user-role. (Related field: Formula)"

The field Account.Open Awards referenced has been deleted as it's an old never-used field. Any ideas?
Amnon KruviAmnon Kruvi
You can change StageName to TEXT(StageName) to get past that error. Madan's solution will have to be updated for every change in the stage list that you will ever make, as it doesn't set any meaningful default value.

As for the current error, it probably means that your open awards field is in the deleted fields list - and not fully deleted. Switch to Classic and open the account field list. At the bottom you'll see a link that takes you to the deleted fields. 
Kate RussellKate Russell
Thanks, Amnon! I had tried the TEXT earlier and kept getting errors related to parentheses. I think I just over thought it. I updated it again and erased the field and that worked! THANK YOU!