1

I have seen several posts about this, but all posts manage to successfully calculcate which date is the MAX (the one closest to today) but do not then return this date, only the number of days between that day and today which is of no use to me...

Long story short I have 5 date fields on an obj - lets say date1__c, date2__c, date3__c, date4__c & date5__c.

When a record is inserted or updated, I need to record which date is the most recent out of the 5 and return this date. From an integer perspective the same issue would be solved by the MAX formula function but this does not support dates...

Can this be achieved? Preferably in a workflow or a formula field?

UPDATE:

Going off Eric's solution below, I was able to put together this workflow field update which after a bit of tweaking works perfectly. For future ref (and anyone who comes across this post):

CASE
(max(
(BLANKVALUE(Date_1__c, TODAY()-365) - TODAY()),
(BLANKVALUE(Date_2__c, TODAY()-365)- TODAY()),
(BLANKVALUE(Date_3__c, TODAY()-365)- TODAY()),
(BLANKVALUE(Date_4__c, TODAY()-365)- TODAY())
)
, (BLANKVALUE(Date_1__c, TODAY()-365)) - TODAY(), Date_1__c
, (BLANKVALUE(Date_2__c, TODAY()-365)) - TODAY(), Date_2__c
, (BLANKVALUE(Date_3__c, TODAY()-365)) - TODAY(), Date_3__c
, (BLANKVALUE(Date_4__c, TODAY()-365)) - TODAY(), Date_4__c
,null
)
4

Not sure what you mean by "return" the value as a trigger does not return anything but you can figure it out with a bit of logic

for(sObject r : trigger.new){
   date maxDate = date1__c;

   if(maxDate == null || (r.date2__c != null && r.date2__c > maxDate)) maxDate = r.date2__c;
   if(maxDate == null || (r.date3__c != null && r.date3__c > maxDate)) maxDate = r.date3__c;
   if(maxDate == null || (r.date4__c != null && r.date4__c > maxDate)) maxDate = r.date4__c;
   if(maxDate == null || (r.date5__c != null && r.date5__c > maxDate)) maxDate = r.date5__c;
}

you could most likely do the same thing in a formula using nested if's using the same logic

I found this posted as an example of a workflow field update:

CASE
    (max(
            (BLANKVALUE(date_0__c, now()-36500) - now()),
            (BLANKVALUE(date_1__c, now()-36500)- now()),
            (BLANKVALUE(date_2__c, now()-36500)- now()),
            (BLANKVALUE(date_3__c, now()-36500)- now()),
            (BLANKVALUE(date_4__c, now()-36500)- now()),
            (BLANKVALUE(date_5__c, now()-36500)- now()),
            (BLANKVALUE(date_6__c, now()-36500)- now()),
            (BLANKVALUE(date_7__c, now()-36500)- now())
        )
        , (BLANKVALUE(date_0__c, now()-36500)) - now(), date_0__c
        , (BLANKVALUE(date_1__c, now()-36500)) - now(), date_1__c
        , (BLANKVALUE(date_2__c, now()-36500)) - now(), date_2__c
        , (BLANKVALUE(date_3__c, now()-36500)) - now(), date_3__c
        , (BLANKVALUE(date_4__c, now()-36500)) - now(), date_4__c
        , (BLANKVALUE(date_5__c, now()-36500)) - now(), date_5__c
        , (BLANKVALUE(date_6__c, now()-36500)) - now(), date_6__c
        , (BLANKVALUE(date_7__c, now()-36500)) - now(), date_7__c
        ,null
    )

From this thread:

https://success.salesforce.com/answers?id=90630000000gj0dAAA

Some additional example / options here : https://success.salesforce.com/answers?id=90630000000gu73AAA

|improve this answer|||||
  • This doesn't work if date1__c is null. – Adrian Larson Feb 19 '15 at 0:05
  • I guess some if statements can be added to firstly check if any of the fields contain a value (if OR statement) and then use the above logic. Thanks this solution will work well - before I select it as my answer I want to see if anyone has a suggestion for how to do this in a workflow or a formula (no test classes/support required). If not I can tailor your example to fit my needs thanks you... FYI "return" would only work in a formula scenario ie if MATCHES CRITERIA return date – David Dawson Feb 19 '15 at 0:10
  • @AdrianLarson - Good catch - update to account for that possibility – Eric Feb 19 '15 at 1:14
  • @DavidDawson - Easy thing about the trigger as it is written is all you have to do to cover lines is insert/update a record. Simply inserting / updating a record will give you 100% coverage. then to properly test assert the correct value was obtained. Very easy test class to write. – Eric Feb 19 '15 at 1:59
  • yes you are correct - despite how simple it is I always follow best practice and use formulas/workflows over triggers as much as possible but your solution is spot on. Thank you very much – David Dawson Feb 19 '15 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.