I have custom fiscal year starting May 1st to April 30th. I want to find a clever way to select a picklist value based on opportunity closed date.

Picklist values 2010 through 2020

Here is the scenario, If the close date on or after May 1 2014, select 2015 from the picklist if it is March 2014, select 2014 from picklist.. etc. any help greatly appreciated

PS, I am trying to do this in a custom controller.

  • 1
    are you trying to do this in an apex trigger ? or you want a workflow rule field update to select the picklist ? or can you work with a formula text field to show the fiscal year ? Aug 10 '14 at 9:19
  • Is it real custom fiscal year (does the Period table have any entries) or is it standard Gregorian with shifted month start? Are you looking into doing this as a workflow (which is pain to set picklist values based on conditions like that) or are you thinking about apex controller/trigger?
    – eyescream
    Aug 10 '14 at 12:36
  • I am doing this in a trigger handler. The fiscal year itself isn't being referenced in the code. Aug 10 '14 at 14:20
  • A trigger doesn't pause and wait for input. If you're doing this via Visual Force, you could use a controller that has a JS based picklist of some kind (or std one I suppose, but that's not necessary) which returns a value to it. I really think you need to post some code and tell us where you're stuck. Either that or tell us if you're asking for suggestions on how to do this. Otherwise, your question is unclear to me where you're starting from and what type solution you're looking for.
    – crmprogdev
    Aug 10 '14 at 14:46

You didn't answer my comment about "is it real custom fiscal year (does the Period table have any entries) or is it standard Gregorian with shifted month start".

Either way I would discourage you from hardcoding the "May 1st" boundary date. Here's how to get the reference data if it's a real custom fiscal year (my company uses 5-4-4 calendar)

SELECT FiscalYearSettings.Name, Type, StartDate, EndDate 
FROM Period
WHERE Type = 'Year'

enter image description here

As you can see the dates jump like crazy - no way I'm hardcoding them anywhere, I need to fetch them from this reference table (bonus points for the fact that you can fetch "FY2015" straight from the table if that's how they called the year label).

And if it's a standard Gregorian fiscal year but shifted - you should query the start month from Org settings:

SELECT FiscalYearStartMonth FROM Organization


For the first case I'd probably fetch them all once and loop through them until I find a record where my date falls between the start & end date. Simple, boring, generic solution - sometimes these are best.

For the second case I'd reuse the suggestion I've given to sfdc_ninja:

Integer startMonth = 5; // don't hardcode it

/*  trigger: before insert, before update
    for(Opportunity o : trigger.new)
    etc etc

Date d = System.today(); // o.CloseDate
Integer year = d.year() + (d.month() >= startMonth ? 1 : 0);

String yearAsString = 'FY' + String.valueOf(year);
System.debug(yearAsString); // outputs "FY2015"
  • We are using a fiscal year with shifted month start. so in other words, it will always remain May 1st to April 30th. A better question is how do I determine what fiscal year closedate falls under? Thanks for your help Aug 11 '14 at 0:20
  • I think I've answered your question? Use that code snippet, just call it in a loop and with opportunity.CloseDate rather than today's date?
    – eyescream
    Aug 11 '14 at 23:02

If you want to keep it native (no code), then I would suggest a formula field rather than a picklist. You could simply do something like this. Mine was actually much longer, credit for the consolidated pretty look to @eyescreme

YEAR(DATEVALUE(CloseDate)) + IF(MONTH(DATEVALUE(CloseDate)) >= 5, 1, 0)

If the use case is as straight forward as it seems, this should work. If there are other nuances, you could the route of WFR's or even Apex Triggers.

  • That's quite long. Why not something like YEAR(DATEVALUE(CloseDate)) + IF(MONTH(DATEVALUE(CloseDate)) >= 5, 1, 0)?
    – eyescream
    Aug 10 '14 at 12:34
  • Even better. Only had 1 cup of coffee this morning, still warming up :) Added this to the answer as you are right, much better. Aug 10 '14 at 12:55
  • Unfortunately, the org is setup in the way I described where we have to use code to determine where the closedate fall between those dates in order to select the right picklist value. It's not very efficient but that's how it is. Aug 10 '14 at 14:22
  • is it possible to query fiscalYear.name, enddate, startDate? Aug 11 '14 at 1:07

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.