One of my clients has to report on all active projects for this year and last year.
An active project in a year is defined as one that started during that year, or in a previous year and the end date was in that year or beyond. In addition, the Opportunity Stage had to be Active or Completed.
There were dozens of reports using this filter criteria, and each new year my client had to manually update all of them for the new year.
This kind of reporting uses absolute dates, as the dates don’t change over time.
As an alternative, relative dates change depending on what is today’s date. They could be written as “this year” and “last year”.
So we created two formula fields of type checkbox called “Active This Year” and “Active Last Year”. Once the formulas were sorted out, my client could use these checkboxes in all reports.
The advantages of this approach are:
- A single and centralized definition of what constituents an active project
- Zero chance of accidentally getting a report’s filter criteria wrong
- No need for extra manual work year over year
The takeaway
The simple change of using relative dates instead of absolute dates had a profound impact. Further, business logic shouldn’t be splattered all over your Salesforce instance. The more you can centralize it, the less technical debt you’ll have.