Apex Code Development You need to sign in to do that
Don't have an account?
Marco Pinder How do you compare several Date fields on an object?
Hi,
I have seven different date fields on the contact object that I want to compare in order to find the most recent and then insert that into another custom field.
My current approach is not working as I am using several formula fields where each one consists of an IF statement that compares the previous formula field with one of the seven date fields and returns the greatest value.
For example:
Formula Field 1 = Comparing Date Field 1 with Date Field 2
Formula Field 2 = Comparing Formula Field 1 with Date Field 3
Formula Field 3 = Comparing Formula Field 2 with Date Field 4
And so on...
However I am now reaching a character limit on the compiled code (I'm guessing because it is going back through all of the previous IF statements and it becomes too large).
I have tried using the CASE() function but I still reach character limits.
Unfortunately the MAX() function only works with numbers, not date fields.
Does anyone have any advice how I could achieve my goal of finding the most recent date from a list of 7 fields?
Thanks,
Marco
I have actually just been able to solve this problem and thought I would share it in case anyone has a similar issue in future.
Firstly I created a custom formula field (called Days__c) of 'Number' data type. This field contained a MIN() function to find the smallest numerical value of "TODAY() - Date Field 1, TODAY() - Date Field 2, etc..." This would indicate which of the seven date fields is the most recent.
Then I created a second custom formula field of 'Date' data type. This field contained a CASE() function as follows:
"CASE(TODAY() - Days__c, Date Field 1, Date Field 1, Date Field 2, Date Field 2, etc...)" This would insert whichever of the seven date fields is the most recent.
All seems to work well and saves the hassle of APEX coding (of which I have limited knowledge). Phew!
Thanks,
Marco
All Answers
Hi,
I am not sure, but if we anyhow parse the date and get the day, month, year in numeric format, add those numbers, more the sum, recent is the date...
Thanks,
Shailesh Patil
Do you mean something like:
1st January 2011 = 01 + 01 + 2011 = 2013
4th March 2011 = 04 + 03 + 2011 = 2018
In this scenario, 2018 is greater than 2013, therefore it is the most recent date?
That wouldn't work because, 30th January 2011 = 30 + 01 + 2011 = 2042
2042 is greater than 2018, but 30th January 2011 is not more recent than 4th March 2011.
Or did I misunderstand your idea?
Thanks for the feedback anyway,
Marco
With a formula field, it wont actually work due to limit on character,
I think, a trigger can work in this case.
I have actually just been able to solve this problem and thought I would share it in case anyone has a similar issue in future.
Firstly I created a custom formula field (called Days__c) of 'Number' data type. This field contained a MIN() function to find the smallest numerical value of "TODAY() - Date Field 1, TODAY() - Date Field 2, etc..." This would indicate which of the seven date fields is the most recent.
Then I created a second custom formula field of 'Date' data type. This field contained a CASE() function as follows:
"CASE(TODAY() - Days__c, Date Field 1, Date Field 1, Date Field 2, Date Field 2, etc...)" This would insert whichever of the seven date fields is the most recent.
All seems to work well and saves the hassle of APEX coding (of which I have limited knowledge). Phew!
Thanks,
Marco
I love this one!
However, I am having issues with blank dates. What if the other 6 dates are not filled out but still we need to pull up the most recent date between the 7 date fields. So technically the 7th date field needs to be displayed?
any ideas?
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c,
TODAY() - X3rd_Payment_Submitted__c,
TODAY() - X4th_Payment_Submitted__c,
TODAY() - X5th_Payment_Submitted__c,
TODAY() - X6th_Payment_Submitted__c,
TODAY() - X7th_Payment_Submitted__c),
IF(NOT(ISBLANK(X6th_Payment_Submitted__c)),
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c,
TODAY() - X3rd_Payment_Submitted__c,
TODAY() - X4th_Payment_Submitted__c,
TODAY() - X5th_Payment_Submitted__c,
TODAY() - X6th_Payment_Submitted__c),
IF(NOT(ISBLANK(X5th_Payment_Submitted__c)),
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c,
TODAY() - X3rd_Payment_Submitted__c,
TODAY() - X4th_Payment_Submitted__c,
TODAY() - X5th_Payment_Submitted__c),
IF(NOT(ISBLANK(X4th_Payment_Submitted__c)),
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c,
TODAY() - X3rd_Payment_Submitted__c,
TODAY() - X4th_Payment_Submitted__c),
IF(NOT(ISBLANK(X3rd_Payment_Submitted__c)),
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c,
TODAY() - X3rd_Payment_Submitted__c),
IF(NOT(ISBLANK(X2nd_Payment_Submitted__c)),
Min(
TODAY() - X1st_Payment_Submitted__c,
TODAY() - X2nd_Payment_Submitted__c),TODAY() - X1st_Payment_Submitted__c))))))
MIN(
TODAY() - IF( NOT(ISBLANK(Date_1)), Date_1, DATE(1900,1,1)) ,
TODAY() - IF( NOT(ISBLANK(Date_2)), Date_2, DATE(1900,1,1)) ,
TODAY() - IF( NOT(ISBLANK(Date_3)), Date_3, DATE(1900,1,1)),
TODAY() - IF( NOT(ISBLANK(Date_4)), Date_4, DATE(1900,1,1))
)