Apex Code Development You need to sign in to do that
Don't have an account?
geckogal Formula Field based on date range - Help
I need help updating my shipping on an oppty. This is a formula field that uses the close date to determine the shipping cost.
Example:
Overnight shipping is either $40, $45 or $50 depending on the close date.
Currently, I am using the following formula, but I now need to increase effective today (adding a third option):
IF (
CloseDate < DateValue("2008-08-20"),
IF (
Overnight__c, 40, IF(Overnight_Sat_Delivery__c, 45, IF(X2_Day__c, 25, IF( X3_Day__c, 23, IF( Ground__c, 15, IF(Canada__c, 60, IF(International__c, 75, IF(P_O_Box__c, 25, 0)))))))),
If (
Overnight__c, 45, IF(Overnight_Sat_Delivery__c, 50, IF(X2_Day__c, 30, IF( X3_Day__c, 28, IF( Ground__c, 20, IF(Canada__c, 65, IF(International__c, 80, IF(P_O_Box__c, 30, 0))))))))
)
CloseDate < DateValue("2008-08-20"),
IF (
Overnight__c, 40, IF(Overnight_Sat_Delivery__c, 45, IF(X2_Day__c, 25, IF( X3_Day__c, 23, IF( Ground__c, 15, IF(Canada__c, 60, IF(International__c, 75, IF(P_O_Box__c, 25, 0)))))))),
If (
Overnight__c, 45, IF(Overnight_Sat_Delivery__c, 50, IF(X2_Day__c, 30, IF( X3_Day__c, 28, IF( Ground__c, 20, IF(Canada__c, 65, IF(International__c, 80, IF(P_O_Box__c, 30, 0))))))))
)
Thank you for your help
i'd like to create both a custom field and API call to the carrier website to calculate shipping.
thanks,
david
1. create a trigger on the Opportunity that will do these calculations, and you can add new "dated" calculations as they come along.
2. formula: If you want to avoid a coding solution, you could consider creating formula fields for each "dated" calculations, then the actual shipping field is an if statement around the CloseDate, referring to the appropriate formula field.
Field name: Shipping Rule1 (the one for prior to Aug 20)
Formula: IF (Overnight__c, 40, IF(Overnight_Sat_Delivery__c, 45, IF(X2_Day__c, 25, IF( X3_Day__c, 23, IF( Ground__c, 15, IF(Canada__c, 60, IF(International__c, 75, IF(P_O_Box__c, 25, 0))))))))
Field name: Shipping Rule2 (the one for between Aug 20 and now)
Formula: If (Overnight__c, 45, IF(Overnight_Sat_Delivery__c, 50, IF(X2_Day__c, 30, IF( X3_Day__c, 28, IF( Ground__c, 20, IF(Canada__c, 65, IF(International__c, 80, IF(P_O_Box__c, 30, 0))))))))
Field name: Shipping Rule3 (the new one - I made up the numbers for demo purposes)
Formula: If (Overnight__c, 50, IF(Overnight_Sat_Delivery__c, 55, IF(X2_Day__c, 30, IF( X3_Day__c, 30, IF( Ground__c, 22, IF(Canada__c, 70, IF(International__c, 85, IF(P_O_Box__c, 35, 0))))))))
Then the Shipping field formula could read:
Formula: IF ( CloseDate < DateValue("2008-08-20"), ShippingRule1__c,
if( CloseDate < DateValue("2008-11-26"), ShippingRule2__c,
ShippingRule3__c))
This is a bit more expandable, you can continue to create new Shipping formula fields and add the new date criteria into the Shipping formula.
Hope that makes sense.
Niki
www.vankerksolutions.com