You need to sign in to do that
Don't have an account?
Trevor 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 )
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 )
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)
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 ***
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
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) {
Keith