function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Trevor BurnsTrevor Burns 

help with a Validation Rule to Align Subscription Start Date DAY to Opportunity Aactivation Date DAY

I am in need of some help,
this request has managed to stump Salesforce Developer help desk so thought id ask here.

I have a Subscription from Zuora that has a Start Date.
since we do not Prorate our deals our Activation date for service on later deals need to align to the same day of the month as when the subscription was origionaly started.

I want to create a VR to stop reps from entering an incorrect "account activation date" on their opps (based on matching day to subscription start date) this big issue is a lack of an 'End of month' function in Salesforce to account for situations like subscription starting on a day that does not exist in the current month.

Example:
Subscription start date - 12-31-14
Upgrade OpportunityActivation Date - 2-28-15

It works fine for days 1-28 of any month but as you can see I need to have the VR be able to handle the fact that sometimes the subscription start date and the current month do not have the same number of days and that if the subscription was started at teh end of the month then the activation date should also be the end of the month.


Below is the data Salesforce Support build befor they gave up. any ideas?

Feild: Account Activation Date Standard
This field shows the normal Account Activation Date date for the month and year of the Account Activation Date field and the day of month of the Subscription Start Date field on the related Subscription record

IF( ISBLANK( Contract_Start_Date__c ) , 
NULL , 

DATE( 
YEAR( Contract_Start_Date__c ) , 
MONTH( Contract_Start_Date__c ) , 

IF( DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) , 

/* Use the day of month from the Subscription date */ 
DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) , 

/* Use the maximum day of month from the Opportunity date */ 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) 
)))


VR: Billing_Period_and_Activation_Days_2 
VR to keep billing period day in Z and opportunity Account Activation Dates in alignment.

/* WIP */ 


/* Check if the Opportunity has a (parent) Simply Measured Plan */ 
! ISBLANK( Simply_Measured_Plan__c ) 

/* Check if the Opportunity has a (grandparent) Zuora Subscription */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__c ) 

/* Check if there is a Subscription Start Date */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 

/* Check if days don't match and Subscription date < Oppty Date */ 
&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<> DAY( Contract_Start_Date__c ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) = 0 , 
29 , 
28 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 

CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 
28 , 20 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 )




 
K_McRaeK_McRae
Trevor,

Some thoughts on your problem...

There seems to be 2 valid scenarios:
1) DAY_IN_MONTH(Upgrade OpportunityActivation Date) matches DAY_IN_MONTH(Subscription start date)
Subscription start date - 12-31-14 (DAY_IN_MONTH = 31)
Upgrade OpportunityActivation Date - 1-1-15 (DAY_IN_MONTH = 1)
*** FAIL ***

Subscription start date - 12-15-14 (DAY_IN_MONTH = 15)
Upgrade OpportunityActivation Date - 1-15-15 (DAY_IN_MONTH = 15)
*** PASS ***
 
2) Upgrade OpportunityActivation Date is the last day of the month && DAY_IN_MONTH(Upgrade OpportunityActivation Date) < DAY_IN_MONTH(Subscription start date)
Subscription start date - 12-31-14 (DAY_IN_MONTH = 31)
Upgrade OpportunityActivation Date - 09-30-15 (DAY_IN_MONTH = 30) && 30th is last day of September
*** PASS ***

So to me, if I have understood your problem, the key is to know if the Upgrade OpportunityActivation Date is the last day of the month or not.

In this basis a formula returning a boolean value detailing whether or not the Upgrade OpportunityActivation Date is the last day of the month may help?

Keith
Trevor BurnsTrevor Burns
yes that seems like a good direction but what about situations where Not last day in month but still does not have same number of days? Example: Jan 30th was activation day (Not last day) Upgrade on Feb 28th (last day)
K_McRaeK_McRae
Trevor,

Apologies if I have things the wrong way round but I thought the issue was more related to the Upgrade date.

In the scenrio of:
Activation: 30th (not last day of month)
Upgrade: 28th (last day of month)

Check 1 (Basic check):
Upgrade (28) = Activation (30): FALSE
Else check 2 (checks for the special scenario where the Upgrade date is the last day of the month):
Upgrade is last day of month: TRUE
Upgrade (28) < Activation (30): TRUE

So the Upgrade date is valid.

Is the reverse scenario true?

Activation: 28th (last day of month)
Upgrade: 30th (not last day of month)

Is this valid/allowed? 

If so, then you need to know if both the Activation and Upgrade dates are the last day of the month and extend the if statement.

If (Upgrade = Activation) {
OK
} else if (UpgradeIsLastDay && Upgrade < Activation} {
OK
} else if (ActivationIsLastDay && Upgrade > Activation) {
OK
} else {
Not OK


Keith
Trevor BurnsTrevor Burns
ya, if the activation day was the last day of the month then the upgrade would also need to be set to the last day of the month, so i think the extension of the if statement will be required. thank you for the help. -Trevor