0

We're moving to Flows and I need a bit of help on IF statements in Salesforce. I've tried a few variations but with the IF statement when creating a DATE formula but I keep getting “Syntax error. Missing ')'”

 
(IF(ISPICKVAL{!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
  "Cohort Start Date", 
  {!PASId.Enrollment__c.Cohorts__r.Start_Date__c}, 
IF(ISPICKVAL{!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
  "Cohort End Date", 
  {!PASId.Enrollment__c.Cohorts__r.End_Date__c}, 
IF(ISPICKVAL{!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
  "Momentum End Date", 
  {!PASId.Enrollment__c.Cohorts__r.Momentum_Module_End_Date__c}, null)))) + 
{!PASId.Program_SurveyAssessment__c.Days_from_Anchor_Date_to_Due_Date__c}

And when I try creating a Date formula using CASE, I get “Picklist fields are only supported in certain functions.” Can CASE and ISPICKVAL not go together?

CASE(ISPICKVAL(DATE({!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c})), "Cohort Start Date", 
{!PASId.Enrollment__c.Cohorts__r.Start_Date__c}, "Cohort End Date", 
{!PASId.Enrollment__c.Cohorts__r.End_Date__c}, "Momentum End Date", 
{!PASId.Enrollment__c.Cohorts__r.Momentum_Module_End_Date__c}, "") 
+ {!PASId.Program_SurveyAssessment__c.Days_from_Anchor_Date_to_Due_Date__c}

What I'd ideally like the formula to return is the Date of whatever the Due Date Picklist is Anchored to and add "number of days from Anchor Date to Due Date". Any help would be appreciated!

2
  • 1
    Are those copied/pasted correctly? Your first one has no parenthesis for your ISPICKVALs.
    – Moonpie
    Jul 15, 2022 at 22:12
  • e.g. ISPICKVAL(field,value)
    – cropredy
    Jul 15, 2022 at 22:29

1 Answer 1

2

Unless you copied/pasted your first formula incorrectly, you are missing () surrounding the ISPICKVAL expressions.

It really helps prevent bugs and to debug if you write your formulae like you do other indented code. Try this:

(
  IF(
    ISPICKVAL(
      {!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
      "Cohort Start Date"
    ), 
    {!PASId.Enrollment__c.Cohorts__r.Start_Date__c}, 
    IF(
      ISPICKVAL(
        {!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
        "Cohort End Date"
      ), 
      {!PASId.Enrollment__c.Cohorts__r.End_Date__c}, 
      IF(
        ISPICKVAL(
          {!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
          "Momentum End Date"
        ), 
        {!PASId.Enrollment__c.Cohorts__r.Momentum_Module_End_Date__c}, 
        null
      )
    )
  )
) + 
{!PASId.Program_SurveyAssessment__c.Days_from_Anchor_Date_to_Due_Date__c}

I think the error on your second formula is due to your using ISPICKVAL(DATE(....

Plus, the DATE signature looks like this...

DATE(year, month, day)

...and if seems from all your other uses that Due_Date_Anchored_To__c is a TEXT field with values like "Cohort Start Date", so DATE will not work the way you have it there.


I don't recall having ever used CASE with a Picklist field; but going by this documentation...

https://help.salesforce.com/s/articleView?id=sf.customize_functions_case.htm&type=5

...it seems that you do not use ISPICKVAL with CASE on a Picklist field.

So this might work (IT IS UNTESTED):

CASE(
  {!PASId.Program_SurveyAssessment__c.Due_Date_Anchored_To__c}, 
    "Cohort Start Date", {!PASId.Enrollment__c.Cohorts__r.Start_Date__c}, 
    "Cohort End Date", {!PASId.Enrollment__c.Cohorts__r.End_Date__c}, 
    "Momentum End Date", {!PASId.Enrollment__c.Cohorts__r.Momentum_Module_End_Date__c}, 
  ""
) 
+ {!PASId.Program_SurveyAssessment__c.Days_from_Anchor_Date_to_Due_Date__c}
UPDATE

If you return a "" from the CASE statement as your default, and this is a Date formula variable, you are likely to get an error; if not, you'll get a weird value. You should return a Date.

1
  • @Samridhi : I think I added my CASE formula after you already accepted this answer. Could you please try it out and see if it works as well? If not, I need to remove it and just leave the IF formula.
    – Moonpie
    Jul 15, 2022 at 22:44

You must log in to answer this question.

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