Apex Code Development You need to sign in to do that
Don't have an account?
Complete novice How do I combine these two formulas?
Hi, can anyone help me combine these two formulas on a date field please?
IF the Portfolio is Interfaces and the Status is Agreed and the EA Action is one of 3, then the date should be Expected Submission Date -28, +14 or +42.
PLUS
IF the Portfolio is Interfaces and the Status is Invited and the EA Action is First Invitation Chaser, then the date should be Invite email sent + 28 days
I have them both working on separate fields, but can't get them into one formula.
Formula 1:
IF( AND( Journal_for_Article_use__r.Portfolio__c = "Interfaces",
ISPICKVAL( Status__c , "AGR - Agreed to submit")
),
CASE( TEXT( EA_Action__c ),
"Submission Reminder", (Expected_Submission_Date__c - 28),
"First Submission Chaser", (Expected_Submission_Date__c + 14),
"Second Submission Chaser", (Expected_Submission_Date__c + 42),
Null
),
Null
)
Formula 2:
IF( AND(
Journal_for_Article_use__r.Portfolio__c = "Interfaces",
ISPICKVAL( Status__c , "INV - Invited")
),
CASE( TEXT( EA_Action__c ),
"First Invitation Chaser", (Invite_email_sent__c +28),
Null),
Null)
IF the Portfolio is Interfaces and the Status is Agreed and the EA Action is one of 3, then the date should be Expected Submission Date -28, +14 or +42.
PLUS
IF the Portfolio is Interfaces and the Status is Invited and the EA Action is First Invitation Chaser, then the date should be Invite email sent + 28 days
I have them both working on separate fields, but can't get them into one formula.
Formula 1:
IF( AND( Journal_for_Article_use__r.Portfolio__c = "Interfaces",
ISPICKVAL( Status__c , "AGR - Agreed to submit")
),
CASE( TEXT( EA_Action__c ),
"Submission Reminder", (Expected_Submission_Date__c - 28),
"First Submission Chaser", (Expected_Submission_Date__c + 14),
"Second Submission Chaser", (Expected_Submission_Date__c + 42),
Null
),
Null
)
Formula 2:
IF( AND(
Journal_for_Article_use__r.Portfolio__c = "Interfaces",
ISPICKVAL( Status__c , "INV - Invited")
),
CASE( TEXT( EA_Action__c ),
"First Invitation Chaser", (Invite_email_sent__c +28),
Null),
Null)
Hi @Complete novice
I have combined the above formula.
IF( Journal_for_Article_use__r.Portfolio__c = "Interfaces",
IF( ISPICKVAL(Status__c , "AGR - Agreed to submit"),
CASE( TEXT( EA_Action__c ),
"Submission Reminder", (Expected_Submission_Date__c - 28),
"First Submission Chaser", (Expected_Submission_Date__c + 14),
"Second Submission Chaser", (Expected_Submission_Date__c + 42) , null),
IF( ISPICKVAL( Status__c , "INV - Invited"),
CASE( TEXT( EA_Action__c ),
"First Invitation Chaser", (Invite_email_sent__c +28), null), null)), null)
If your problem is solve based the above approach. Please mark as Best Answer.
Thanks,
Shrutika
All Answers
Hi @Complete novice
I have combined the above formula.
IF( Journal_for_Article_use__r.Portfolio__c = "Interfaces",
IF( ISPICKVAL(Status__c , "AGR - Agreed to submit"),
CASE( TEXT( EA_Action__c ),
"Submission Reminder", (Expected_Submission_Date__c - 28),
"First Submission Chaser", (Expected_Submission_Date__c + 14),
"Second Submission Chaser", (Expected_Submission_Date__c + 42) , null),
IF( ISPICKVAL( Status__c , "INV - Invited"),
CASE( TEXT( EA_Action__c ),
"First Invitation Chaser", (Invite_email_sent__c +28), null), null)), null)
If your problem is solve based the above approach. Please mark as Best Answer.
Thanks,
Shrutika
This combined formula checks if the following conditions are met for a given record:
The Portfolio is "Interfaces"
The Status is either "Agreed to submit" or "Invited"
If these conditions are satisfied, the formula calculates a date based on the specific scenario:
For "Agreed to submit" cases, the formula evaluates the EA_Action__c field and calculates the date based on whether it's "Submission Reminder," "First Submission Chaser," or "Second Submission Chaser." The calculated date is derived from the Expected_Submission_Date__c field to
Solar Panels (https://www.aferiy.com/).
For "Invited" cases, the formula calculates a date if the EA_Action__c field is "First Invitation Chaser." The calculated date is derived from the Invite_email_sent__c field.
If the conditions are not met, the formula returns a "Null" value.
It's important to customize this formula to match the actual field names, picklist values, and data types used in your Salesforce instance. This combined formula streamlines the calculation process, making it easier to manage both scenarios within a single formula.
@shrutika kesarkar, your solution worked perfectly, thank you.