0

The purpose of this formula is to assign numeric values to some picklists and average them. This is a second draft which returns syntax error Missing ":

( CASE ( Mission_Aligned__c,

"No elements aligned",0,

"Few elements aligned",1,

"Some elements aligned",2,

"Most elements aligned",3,

"All elements aligned",4,0)

+

CASE ( Quality_Strength__c,

"Not research based",0,

"Some research based",1,

"Majority research based",2,

"Evidence informed",3,

"Evidence based",4,0)

+

CASE ( Participant_Impact__c,

"Low",0,

"Low to medium",1,

"Medium",2,

"Medium to high",3,

"High",4,0)

+

CASE ( Staff_Capacity_to_Deliver__c,

"None",0,

"People in training",1,

"Minimum required staff to deliver",2,

"Minimum extra staff on hand to deliver",3,

"Numerous staff on hand to deliver”,4,0)

+

CASE ( Funding_Availability__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Content_Updated__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Funding_Recency_Implementation__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Marketing_Materials_Developed_and_Utiliz__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Demand_for_languages_other_than_English__c,

"4 or more requests in last year",0,

"3 requests in last year",1,

"2 requests in last year",2,

"1 request in last year",3,

"No request in last year",4,0)

+

CASE ( Relates_to_Community_Trends_Needs_Fund__c,

"No community stakeholder trend identification",0,

"1-2 community stakeholder trend identification”,1,

"3-4 community stakeholder trend identification”,2,

"4-5 community stakeholder trend identification”,3,

"6+ community stakeholder trend identification”,4,0)

+

CASE ( Family_Interest_and_Needs__c,

"29% or less people completed the class",0,

"30%-44% people completed the class”,1,

"45%-49% people completed the class”,2,

"60$-84% people completed the class”,3,

"85% or more people completed the class”,4,0)

+

CASE ( Supports_Partnerships__c,

"0 partnerships involved",0,

"1 partnership involved”,1,

"2 partnerships involved”,2,

"3 partnerships involved”,3,

"4+ partnerships involved”,4,0))

/

(IF (ISBLANK(TEXT( Mission_Aligned__c)),0,1)

+

IF (ISBLANK(TEXT ( Quality_Strength__c)),0,1)

+

IF (ISBLANK(TEXT ( Participant_Impact__c)),0,1)

+

IF (ISBLANK(TEXT ( Staff_Capacity_to_Deliver__c)),0,1)

+

IF (ISBLANK(TEXT ( Funding_Availability__c)),0,1)

+

IF (ISBLANK(TEXT ( Content_Updated__c)),0,1)

+

IF (ISBLANK(TEXT ( Funding_Recency_Implementation__c)),0,1)

+

IF (ISBLANK(TEXT ( Marketing_Materials_Developed_and_Utiliz__c)),0,1)

+

IF (ISBLANK(TEXT ( Demand_for_languages_other_than_English__c)),0,1)

+

IF (ISBLANK(TEXT ( Relates_to_Community_Trends_Needs_Fund__c)),0,1)

+

IF (ISBLANK(TEXT ( Family_Interest_and_Needs__c)),0,1)

+

IF (ISBLANK(TEXT ( Supports_Partnerships__c)),0,1))
  • Looks like you have some smart quotes mixed in with the regular quotes for some values in the CASE functions for Staff_Capacity_to_Deliver__c, Funding_Availability__c, Content_Updated__c, Funding_Recency_Implementation__c, Marketing_Materials_Developed_and_Utiliz__c, Relates_to_Community_Trends_Needs_Fund__c, Family_Interest_and_Needs__c, and Supports_Partnerships__c`. Are those intentional, or was your editor/ide trying to be "helpful"? That might be what's throwing you off (UTF-8 code point E2 80 9D vs a "normal" quote, which is code point 22) – Derek F Aug 14 at 2:18
  • Thank you Derek! Looks like that was the cause of that error! However I have unfortunately run into: Error: Compiled formula is too big to execute (6,967 characters). Maximum size is 5,000 characters – Milad Mohammadi Aug 14 at 2:53
  • It's bedtime for me, so I'll turn my comment into a proper answer after I wake up. The unfortunate bit is that I don't think you'll be able to shave enough off of the compiled size to make this work. You may want to consider using another tool like Process Builder or do this work in Apex instead. – Derek F Aug 14 at 3:20
  • Makes sense, thanks for that! I am curious what can be achieved with process builder. – Milad Mohammadi Aug 14 at 3:26
0

Unfortunately, you will hit SF Formula Field Limits and Restrictions with that formula, it's simply too big.

If you do not want to go for APEX, you can use two (or even more) formula fields and split your logic and an additional field, that holds the result, e.g.:

  1. numeric values to some pick-lists (top of your formula)--> in one formula field "A"
  2. If Statements (bottom of your formula) --> in a different formula field "B"
  3. Additionally, you need a workflow rule and a field update to calculate the average "A" / "B". the result needs to be stored in a new result field.

This WF rule should fire hitting following criteria:

OR(
    ISCHANGED(Formula_A__c),
    ISCHANGED(Formula_B__c)
    )

Field update should be something like this

    Formula_A__c / Formula_B__c

Be careful that you do not divide by zero.

Hope, this is helpful! Best, Stefan

  • Thanks @Stefan! I have now done 1 and 2. For 3, any idea of best way to do this? I presume I choose rule criteria as "formula evaluates to true". Not sure how to build the correct formula. – Milad Mohammadi Aug 15 at 1:05
  • @Milad, I added WF criteria at my previous post. – Stefan Aug 16 at 7:48
1

Looks like you're missing a + sign:

"6+ community stakeholder trend identification”,4,0)
+
CASE ( Family_Interest_and_Needs__c,
  • Great catch and added! Looks like that didn't seem to fix the syntax error however. – Milad Mohammadi Aug 14 at 1:06
0

The problem here seems to be due to some "smart" quotes that worked their way into your formula.

Example:
"Within past 2 years”

The normal double-quote character, the one Salesforce is looking for to both start and end string literals, is ". It's part of the ASCII character set, hex 22

The "smart" end quote is found somewhere else in the UTF-8 encoding, and the code point is hex 201D

Replacing all of the smart quotes with plain double quotes should get you over this error.

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.