0

I seem to always be struggling with resolving syntax errors on my IF code. I have used VS editor so it's easier to read and I don't think it's a parenthisis issue. Getting the error message "The formula expression is invalid: Syntax error" and I'm out of ideas to try. Would appreciate any help!

IF(
    AND(
        TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) ="netWORKS",
        ISPICKVAL({!loopthroughEnrollments.Exit_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
    ), 
    {!loopthroughEnrollments.EnrollmentDate__c} + 180, 
    IF(
        AND(
            NOT(TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) = "netWORKS"), 
            ISPICKVAL({!loopthroughEnrollments.Exit_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
        ), 
        {!loopthroughEnrollments.Cohort__r.EndDate__c}, 
        IF(
            AND(
                TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) ="netWORKS",
                ISPICKVAL({!loopthroughEnrollments.Follow_up_1_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
            ), 
            {!loopthroughEnrollments.Cohort__r.EndDate__c} + 270, 
            IF(
                AND(
                    NOT(TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) = "netWORKS"), 
                    ISPICKVAL({!loopthroughEnrollments.Follow_up_1_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
                ), 
                {!loopthroughEnrollments.Cohort__r.EndDate__c} + 90, 
                IF(
                    AND(
                        TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) ="netWORKS", 
                        ISPICKVAL({!loopthroughEnrollments.Follow_up_2_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
                    ), 
                    {!loopthroughEnrollments.Cohort__r.EndDate__c} + 450, 
                    IF(
                        AND(
                        NOT(TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) = "netWORKS"),
                        ISPICKVAL({!loopthroughEnrollments.Follow_up_2_Survey_Status_v2__c}, “Survey invitation email not yet sent”)
                        ), 
                        {!loopthroughEnrollments.Cohort__r.EndDate__c} + 270,
                        null
                    )
                )
            )
        )
    )
)
3
  • It's easy to get lost in a sea of parenthesis, which is why the first step in debugging formulas is to format them as if they were code. In a nutshell, every time you see an open paren you should add a newline and indent another level. I'll work on formatting your formula in that manner.
    – Derek F
    Apr 18 at 19:15
  • In the course of doing so, I've noticed you have some "smart" quotes. That's probably the issue (formulas need you to use only single or double quotes)
    – Derek F
    Apr 18 at 19:19
  • Thanks @DerekF- I'll be sure to format it as you suggested going forward! When I paste the updated code into my Salesforce Flow formula, it still gives me the same error message. I've also pasted the same code using only 'single quotes' and still no luck :/
    – Samridhi
    Apr 18 at 19:26

2 Answers 2

1

"Smart" quotes (i.e. and ) are not allowed in formulas, and you have them in each use of ISPICKVAL().

Replace them with simple double quotes (") to be consistent with the rest of your formula, and it should work.

That said, you do have a lot of duplication in this formula, making it longer and more complex than it probably needs to be.

Since formulas can't have variables, some amount of duplication is to be expected, but there does seem to be room for improvement. As an example, it looks like the TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) ="netWORKS" bit appears to just add either 0 or 180 days to your end date

{!loopthroughEnrollments.Cohort__r.EndDate__c}
+
IF(
    TEXT({!loopthroughEnrollments.Cohort__r.Program__r.Funding_Stream__r.Name}) ="netWORKS",
    180,
    0
)
+
IF(
    ISPICKVAL({!loopthroughEnrollments.Exit_Survey_Status_v2__c}, "Survey invitation email not yet sent")
    0,
    IF(
        ISPICKVAL({!loopthroughEnrollments.Follow_up_1_Survey_Status_v2__c}, "Survey invitation email not yet sent"),
        90,
        IF(
            ISPICKVAL({!loopthroughEnrollments.Follow_up_2_Survey_Status_v2__c}, "Survey invitation email not yet sent"),
            270,
            0
        )
    )
)

This might not exactly meet your requirement (it'll give you the end date instead of null if you get to the end of the chain), and the Exit_Survey_Status_v2__c bit may be unnecessary since it doesn't add any time to the date on its own.

My point here was that a bit of understanding about the situation can lead to smaller formulas, and less typing generally means less chance to make a mistake.

0

It seems the culprit is

“Survey invitation email not yet sent”

Try

"Survey invitation email not yet sent"

instead.

3
  • Amazing - thank you! This did the trick!
    – Samridhi
    Apr 18 at 19:40
  • Please upvote it and mark it as the best answer. Apr 18 at 19:48
  • a.k.a. the thing that I said in the comments
    – Derek F
    Apr 18 at 19:51

You must log in to answer this question.

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