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
Marcie BilodeauMarcie Bilodeau 

Formula field based on picklist field and If statements

Hi everyone,

I have a formula field that aims to return the text - "Progressing Towards Expectations", "Meeting Expectation", or "Exceeding Expectations".

This field is dependant on two things - the picklist value (Age_Grade_c) that specifies an age group (i.e., "1-2 years") and a number field (Total_Social_Emotional_Score_c) that includes a raw score. Depending on the age and score, the formula should tell us whether the child is Progressing, Meeting, or Exceeding expectations. 

Any idea on how this should be adjusted? I'm getting a syntax error.

[This project is to build in Teaching Strategies Gold (TS Gold) scoring rubric into Salesforce.]

-----

AND(case(Age_Grade__c,

“Birth to year 1"),

IF(Total_Social_Emotional_Score__c <4, "Progressing Towards Expectations”,
IF(AND(Total_Social_Emotional_Score__c >3,(Total_Social_Emotional_Score__c <17)),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >16,"Exceeding Expectations",null),

“1 to 2 years”),

IF(Total_Social_Emotional_Score__c <14,"Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >13,(Total_Social_Emotional_Score__c <31)),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >30,"Exceeding Expectations",null),

"2 to 3 years”),

IF(Total_Social_Emotional_Score__c <24,"Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >23,(Total_Social_Emotional_Score__c <40)),
"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >39,"Exceeding Expectations",null),

“Preschool 3 class/grade”),

IF(Total_Social_Emotional_Score__c <34,"Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >33,(Total_Social_Emotional_Score__c <51)),
"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >50,"Exceeding Expectations",null),

“Pre-K 4 class/grade”),

IF(Total_Social_Emotional_Score__c <46,"Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >45,(Total_Social_Emotional_Score__c <62)),
"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >61,"Exceeding Expectations",null)

)
)
)
)
)
)
Maharajan CMaharajan C
Hi  Marcie, 

Please try the below formula:
  
case( Age_Grade__c,
"Birth to year 1",
IF(Total_Social_Emotional_Score__c <4, "Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >3,Total_Social_Emotional_Score__c <17),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >16,"Exceeding Expectations",null)
)
)
,
"1 to 2 years",
IF(Total_Social_Emotional_Score__c <14, "Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >13,Total_Social_Emotional_Score__c <31),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >30,"Exceeding Expectations",null)
)
)
,
"2 to 3 years",
IF(Total_Social_Emotional_Score__c <24, "Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >23,Total_Social_Emotional_Score__c <40),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >39,"Exceeding Expectations",null)
)
)
,
"Preschool 3 class/grade",
IF(Total_Social_Emotional_Score__c <34, "Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >33,Total_Social_Emotional_Score__c <51),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >50,"Exceeding Expectations",null)
)
)
,
"Pre-K 4 class/grade",
IF(Total_Social_Emotional_Score__c <46, "Progressing Towards Expectations",
IF(AND(Total_Social_Emotional_Score__c >45,Total_Social_Emotional_Score__c <62),"Meeting Expectations",
IF(Total_Social_Emotional_Score__c >61,"Exceeding Expectations",null)
)
)
,
""
)

In formula field Edit (below formula editor)  =>  Blank Field Handling Section   ==> Select  "Treat blank fields as blanks"

Thanks,
Maharajan.C
Marcie BilodeauMarcie Bilodeau
Hi Maharajan, Thank you for the quick response! This resolves the syntax error, but I'm hitting the character limit (formula returns approx. 6,200 of 5k). I'm currently looking to reduce the size. If you have any thoughts on that, that would also be greatly appreciated! Best, Marcie
Maharajan CMaharajan C
Hi Marcie, Is that Total_Social_Emotional_Score__c is a formula field or Number field?
Marcie BilodeauMarcie Bilodeau
It's a formula field (it sums the values from a handful of other fields) Marcie Bilodeau Database & Evaluation Manager United South End Settlements Uniting Community to Strengthen Families 48 Rutland Street Boston, MA 02118 d: 617-375-8142 m: 978-882-3179 www.uses.org Facebook | Instagram | Twitter | LinkedIn