+ Start a Discussion
DJTryingToLearnDJTryingToLearn 

Is cross-object date values possible? STILL trying to figure this out- Anyone know the answer?

Here is my dilemma, I want to populate a DATE value from the ACCOUNT record onto the OPPTY record, based on a "Lead Product Line" PL value selection.  Here is my formula so far, it seems like it should be simple, but all I get is errors!  Any help or direction would be GREATLY APPRECIATED!!

 

This formula is being entered on a Field Update workflow rule on the OPPTY object:

 

CASE (Lead_Product_Line__c,

"JD Edwards", DATEVALUE(Account.JDE_OneWorld_Renewal_Date__c),

"PeopleSoft", DATEVALUE(Account.PeopleSoft_Renewal_Date__c),

"SAP", DATEVALUE(Account.SAP_Renewal_Date__c),

"Siebel", DATEVALUE(Account.Siebel_Renewal_Date__c),

"Oracle E-Business", DATEVALUE(Account.Oracle_Renewal_Date__c),"")

Best Answer chosen by Admin (Salesforce Developers) 
jhurstjhurst

What is the error you are getting?

 

A couple of things to remember:

 

1. If you are populating a Date field on the Opportunity, then you will need to pull a Date field from the Account.  Date/Time fields will throw errors without parsing

2. If you are going Date to Date, then you do not need to use DATEVALUE().  That is only needed if you are taking a string and trying to push it to a Date

3. If you are pushing into a Date field on the Opportunity, you have to push a Date value in all cases.  In your formula you are pushing a null string ("") if the Lead_Product_Line__c does not match the CASE statement.  If you want to place a Null value if no matches, you would need to use DATEVALUE("")

 

I created a similar formula, and it worked for me:

 

Case(StageName,
"Closed Won", Account.Date__c,
"Closed Lost", Account.Date__c, DATEVALUE(""))

 

Another thing to note, is that if you are firing the workflow all the time, you can just use a formula field with the above formula and it works also.

 

Hope this helps.

Jay

All Answers

jhurstjhurst

What is the error you are getting?

 

A couple of things to remember:

 

1. If you are populating a Date field on the Opportunity, then you will need to pull a Date field from the Account.  Date/Time fields will throw errors without parsing

2. If you are going Date to Date, then you do not need to use DATEVALUE().  That is only needed if you are taking a string and trying to push it to a Date

3. If you are pushing into a Date field on the Opportunity, you have to push a Date value in all cases.  In your formula you are pushing a null string ("") if the Lead_Product_Line__c does not match the CASE statement.  If you want to place a Null value if no matches, you would need to use DATEVALUE("")

 

I created a similar formula, and it worked for me:

 

Case(StageName,
"Closed Won", Account.Date__c,
"Closed Lost", Account.Date__c, DATEVALUE(""))

 

Another thing to note, is that if you are firing the workflow all the time, you can just use a formula field with the above formula and it works also.

 

Hope this helps.

Jay

This was selected as the best answer
DJTryingToLearnDJTryingToLearn

Hooray!  Thank you so so much!  Your solution works, well....mostly! 

 

Here's my next issue:  If the Lead Product Line is edited to another product, the Contract Renewal Date doesn't update to the correct Acccount renewal date.  Even if I delete the date and save, the newly select PL date doesn't populate.

 

It does, however, if I create an oppty and not update the PL (leave as "Other"), and save/close.  Then reopen oppty and select a PL and it populates correctly.

 

ALSO! I just noticed that if I switch between PL's and edit opptys PL back to "Other", then edit again to new PL, it DOES update the correct date! 

 

Any more of your magic wand ideas? 

 

******MORE!  I just found that if I enter separate workflows for each, the field populates correctly.  Just would be nice to have all these in one workflow!  Oh well...this is better than nothing!!***************

 

Bowing to the formula King!