Hey folks! Dave Parrish here, and today I’m going to take you through a project I’ve been working on that’s pretty close to my heart. It’s a solution I built for a trade association representing farmers—yes, farmers!—who have to periodically fill out reports for their membership dues.
Now, you might think, "How hard can it be to create a report?" But when you throw in the complexity of scattered schedules (meaning some farmers fill out reports monthly, others quarterly, or based on their own custom months), the solution starts to get a little more… interesting.
Let’s dive in and I’ll show you how I created an "engine" to handle this complexity and automate the entire process.
The Challenge: Scattered Report Schedules
At first glance, it sounds simple enough: farmers need to complete a report and make a payment based on production. But here’s the catch: the reports don’t follow a neat, monthly schedule. Each farmer has a "terms code," which is like a custom schedule for when they need to complete their report.
For example:
Code 1: January through March
Code 2: February through April
Some do quarterly, and some have to report every month.
To make matters even more complex, these codes are stored as plain text in the system—so when the code says “1,” the system doesn’t know that “1” means January. It’s just a number with no inherent meaning.
Breaking Down the Problem
So, how do we turn this jumbled schedule into a functional, automated process? Simple: we need to map these codes to actual months and then trigger tasks based on those months. The result? A reliable engine that handles all the different scenarios and generates the proper reports at the correct times.
Step 1: Mapping Terms Codes to Months
The first thing I needed to do was create a system where each farmer’s terms code was linked to the specific months they need to report. For instance, Code 1 should be tied to the months January, February, and March.
In Knack (the platform I’m using for this project), we can set up a list of all the possible terms codes and what months they represent. This is key, because it allows us to automate the report generation based on what the terms code is tied to. I created a separate field that lists the months for each code, and used checkboxes so that we could associate multiple months with each code.
Now, instead of just the term code being “1” or “2” or “3,” it’s actually tied to the months of the year, and the system knows that Code 1 means January-March, Code 2 means February-April, and so on.
Step 2: Calculating the Current Month
Next up, we need to know what the current month is. This might seem basic, but it's essential for knowing which reports need to be triggered each month.
In my system, I created a field that pulls the current date and calculates the month using a formula. This allows the engine to know, for instance, if it’s currently January, and then determine which reports need to be generated for that month.
Step 3: Building the Report Generation Engine
Now that we have the mapping in place and the current month calculated, it’s time to build the core engine.
I use Knack's task automation feature to run monthly tasks that check each farmer’s terms code and compare it to the current month. If the farmer’s terms code corresponds to the current month, the engine generates the report and assigns it to the farmer.
Here's the process:
Task Trigger: Every day at 3:00 AM, the system checks and updates the current date.
Task for Report Creation: Each month, on the 20th, the system runs a task that checks whether a farmer’s terms code contains the current month. If it does, a report is generated and linked to that farmer.
Email Notification: At 9:30 AM, the system sends an email to the farmer, notifying them that their report is ready and prompting them to fill it out.
By automating these tasks, I’ve created a system that continuously generates and sends reports to farmers without manual intervention.
Step 4: Handling Multiple Tasks and Time Staggering
When building automation tasks like this, timing is crucial. I didn’t want multiple tasks running at the same time, as it could lead to system slowdowns or errors. So, I carefully staggered my tasks throughout the day.
Report Creation: Runs at 9:00 AM.
Email Notification: Sent at 9:30 AM.
This ensures that the system has enough time to generate the report before notifying the farmer.
The Final Engine: A Seamless Process
Now that everything is set up, the system runs smoothly. The report generation engine pulls the correct report for each farmer, based on their terms code and the current month, and sends them a notification email reminding them to fill out their report. The entire process is automated and runs without me having to manually intervene.
A Few Final Thoughts on the System
This approach works well, but I’ve been thinking about whether there’s a way to improve it. Maybe there’s a more elegant solution using custom code or another automation platform like Make (formerly Integromat). If you’re a custom code aficionado, I’d love to hear your thoughts. Would you approach this differently? Or maybe you’ve had experience with a similar situation and have a better way of handling it?
I’m also curious to hear from the automation crowd—do you think this could be better done with a Make scenario, or does this Knack-based engine do the job?
For now, though, I’m happy with the solution I’ve built. It’s efficient, scalable, and ensures that farmers get their reports when they need them.
Wrapping It Up
In the end, creating a report-generating engine for scattered schedules wasn’t as straightforward as I initially thought, but the results have been worth it. By mapping terms codes to specific months, using tasks to automate report creation, and sending email notifications, I’ve built a system that can handle even the most complicated reporting schedules with ease.
If you’re working on something similar, hopefully this post gives you a bit of inspiration and insight into how you can tackle complex scheduling challenges in your own projects.
Thanks for reading, and I’d love to hear your thoughts or feedback!
Interested in my Knack database services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave