The @Salesforce Community Comes to the Rescue…. Again

I began my day a tad frustrated.

At the end of each month, I have to manually update the criteria in 4 list views so that I can send out a reminder email to users with a link to update records for the current fiscal period.

Background

Since a calendar month may not match a fiscal month exactly, we created a trigger to populate Fiscal Month and Fiscal Year (custom text fields) based on Opportunity Close Date and our custom fiscal year settings. Shamrock CRM developed this trigger for us and it’s working great!

However, I’m still left with the task of updating each list view manually like this:

After a year and a half of doing this, I decided that there had to be a better way. I tweeted my question.

Inspiration for a Solution

@hammnick, @knthornt and @michaelforce replied with suggestions.

I learned that you can filter based on Fiscal Quarter quite easily. Just use Close Date = THIS QUARTER. But, when it comes down to showing records for the current Fiscal Period, there is no date keyword to use. (Believe me, I tried. THIS PERIOD just gets an error.)

@michaelforce and @knthornt’s suggestions gave me an idea.

I create a new formula field that displays either “Current Fiscal Period”, “Previous Fiscal Period” or “Future Fiscal Period.” It compares the current date with 2 other custom fields that our trigger updates: Last_Day_of_Fiscal_Period__c and First_Day_of_Fiscal_Period__c. I created these fields and added them to the trigger, but hadn’t used them effectively in logic. I’m glad we have the data available though!

Here’s the formula.

And a closer look:

IF(
   AND(TODAY()>=First_Day_of_Fiscal_Period__c,TODAY()<=Last_Day_of_Fiscal_Period__c),"Current Fiscal Period",
      IF(TODAY()> Last_Day_of_Fiscal_Period__c,"Previous Fiscal Period",
         IF(TODAY()<Last_Day_of_Fiscal_Period__c,"Future Fiscal Period","unknown")
)
)

 

Side note: I believe that I covered all scenarios, but I added the “unknown” text just in case.

It works!

Now, I don’t have to manually update my list views each month. I just filter based on this formula which does the work for me.

And the complex criteria in this other list view is greatly simplified!

Before

After

This will save me a bit of time (and potential headaches) each month.

Thanks again guys! (Gotta love the Salesforce Twitter community.)

Jenna Baze is a rather geeky girl with average social skills and an affinity for: all things Google, Remember the Milk, Salesforce.com, Inbox Zero, Etsy, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips