I'm not sure where to start on this, can anyone help me please? I have a custom object called Invites with links to another custom object called Journals.
I have been asked to auto populate a Chase Date on the Invite object, field based on the Journal Portfolio, the Invite Expected Date, the Invite Status (Picklist) and Invite Action (Picklist) fields.
The first bit is static, we only want the date to populate for Invites with Journal Portfolio A and Status Agreed:
If the Journal Portfolio = A
AND the Status = Agreed
The rest changes:
AND the Action = Reminder
then Chase Date should be 28 days before the Expected Date
AND the Action = Chaser1
then Chase Date should be 14 days after the Expected Date
AND the Action = Chaser2
then Chase Date should be 42 days after the Expected Date
Can I do this in one formula field? I'm struggling with picklist values as I keep getting error messages telling me that the picklist can't be used in the formula, even if I use ISPICKVAL.
Hello @Sara Monksfield
Try this:
IF( AND( Journal_Portfolio_Field__c = "A",
ISPICKLIST(Invite_Status__c, "Agreed")
),
CASE( TEXT(Invite_Action__c),
"Reminder", (Invite_Expected_Date__c - 28),
"Chaser1", (Invite_Expected_Date__c + 14),
"Chaser2", (Invite_Expected_Date__c + 42),
Null
),
Null
)
Note: The return type of the formula is Date. Also, Replace the field API Name.
