function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AnonTestQuestAnonTestQuest 

Need help with an advanced formula.

I am trying to come up with an expiration date for a custom object's records. The only current fields I can use are the 'created date' of the record and there is a picklist with string values of: 'One Week', 'One Month', 'Three Months', ..... 

I was doing a new custom field on the object called 'Expiration Date' and using the type as date/time formula. Under advanced tab here, I was attempting 'if' statements to assign numbers to each picklist value but got stumped as to the logic and formatting of how this would look.

Can anyone guide me in the correct direction to work on this?

Thank you in advance!
Best Answer chosen by AnonTestQuest
AnonTestQuestAnonTestQuest
My final solution was to create the WHERE clause as Expiration_Date__c <= Next_N_Days:2. A lot more simple than I thought. Both are just date lookups.

All Answers

Jigar.LakhaniJigar.Lakhani
Hello,

Please try with below format.
And you need to create Date type of formula field instead of DateTime. 
IF(TEXT( Expiration_Period__c ) == 'One Week',  CreatedDate + 7 , 
IF(TEXT( Expiration_Period__c ) == 'One Month', CreatedDate + ( ( 365 / 12 ) * 1 ) , 
IF(TEXT( Expiration_Period__c ) == 'Three Months', CreatedDate + ( ( 365 / 12 ) * 3 ) ,'') 
) 
)

Thanks & Cheers,
Jigar (pateljb90@gmail.com)
Scott Bub 7Scott Bub 7
It would probably be best to leave out daylight savings too.

CASE(TEXT(Expiration_Period__c),
'One Week', CreatedDate + 7,
'One Month, CreatedDate + ((365.25/12)*1),
'Three Months', CreatedDate + ((365/12)*3),
'')

Just changing the compiled size. CASE Statement would be smaller.
AnonTestQuestAnonTestQuest
I tried these but get a syntax error with CreatedDate. CreatedDate is a Salesforce date/time, not a custom created field.
AnonTestQuestAnonTestQuest
Also there is a Standard field on the object also called 'CreatedDate' that is a lookup to a user. I think that's the one being used instead of the system date/time. Also it won't let me use the Expiration_Period__c because is throwing an error that I can't use picklist values.
Scott Bub 7Scott Bub 7
Maybe you have to convert the Created Date into a date field DATE(). I'm not sure why you are having difficulties. Try selecting the fields using the advanced formula builder 'Insert Field' function. When you select the CreatedDate make sure it is not going over to the user object and it's referencing the Created Date. Also, when you use the picklist field change it to text using the TEXT() function.
AnonTestQuestAnonTestQuest
I am in the advanced area of the formula builder and selecting CreatedDate from the builder and inserting it that way and it does say it is a date/time type so it's getting the right field but getting this: Error: Incorrect parameter type for function 'CASE()'. Expected DateTime, received Text

When I created this formula custom field, I chose Date because I need the output to be a date. Should I have chosen Text?
AnonTestQuestAnonTestQuest
And this is with me using the Case you posted above. Where the expiration period is a picklist of text values like 'one week'. However I want to assign those dates. Specifically the date it will expire. So using the created date and adding on the amount assigned to the picklist value selected. I don't know if that is verbatim of what I posted earlier. Just trying to explain it better.
AnonTestQuestAnonTestQuest
I used this for my case and there were no syntax errors or any other errors I'm just not 100% sure on how to test it to see it's output. I do not have access to the object other than in setup. Here's my altered case from what you wrote, Scott:

CASE(Duration__c,
'One Week', CreatedDate + 7,
'One Month', CreatedDate + ((365/12)*1),
'Three Months', CreatedDate + ((365/12)*3),
'Six Months', CreatedDate + ((365/12)*6),
'One Year', CreatedDate + ((365/12)*12),
NULL)
Scott Bub 7Scott Bub 7
You could get a dev org of your own and test it's output in an object that you create. There will always be a CreatedDate field and you could just create the other and test it there.

https://developer.salesforce.com/gettingstarted?state=id
AnonTestQuestAnonTestQuest
I've got a sandbox to work in so I'll give it a shot there. Just wasn't sure if there was an easier way like testing a SOQL query in the dev console.
AnonTestQuestAnonTestQuest
Everything works as expected. My issue now is my SOQL query in Apex in my batch. I'm testing my query in the dev console and keep getting parsing errors (I believe due to the format). My WHERE clause criteria is 'WHERE System.Today() <= Expiration_Date__c - 2'

Attempting to filter out all records except ones that are 2 or less days away from expiring. 
AnonTestQuestAnonTestQuest
My final solution was to create the WHERE clause as Expiration_Date__c <= Next_N_Days:2. A lot more simple than I thought. Both are just date lookups.
This was selected as the best answer