In yesterday’s email, we discussed how to create custom fields to indicate which records were active this year and last year.
That works great for those two years, but what if you want to go further back? Like more than 10 years back, to the beginning of your organization.
Option 1 is to create more fields called “Active 2 Years Ago”, “Active 3 Years Ago”, etc. With this approach, you’ll need to remember to create a new field each new year. And it doesn’t help when you want to report on historical trends, since you’ll need to do math (i.e. was 6 years ago in 2017 or 2018?)
Option 2 is to create a snapshot report. That works well for reporting on data starting today and into the future. But what about the past?
Option 3 is to combine options 1 and 2. This means creating a custom object, say called “Historical Summary”. Add the custom fields “Year” of type Text(4) and “Count” of type Number, which is the number of active records for that year.
Then create a flow that populates records based on the formula created for “Active This Year”. Once you run that flow a few times, you’ll have all the historical data needed for some great reports and dashboards.
The takeaway
As usual, make sure your solution is fit-for-purpose. Just because it works in one scenario, doesn’t mean it’ll be effective in all scenarios.