Welcome back to another SmartTips blog! My name is Gavin Brennen, and I am a product specialist here at SmartSuite. Today, we're diving deep into the world of historical reporting. More specifically, we'll be exploring how you can efficiently track and report status changes over time. So, gear up for a journey where we'll be using a trucking business as a prime example to bring our concepts to life.
Introduction: The Importance of Tracking History
Imagine managing a trucking business with a fleet of nine trucks. Each truck comes with its own make, model, and a driver, all with statuses subject to frequent changes. For instance, today the "Iron Stallion" might be in transit, yet by tomorrow it could be available. Being able to change statuses frequently is fantastic, but understanding the history of those changes can be even more vital.
Creating a Historical Log
The Challenge
In a fast-paced business, having up-to-date records is essential. However, what happens when you need a historical view of how things have changed over time? This is where creating a historical log comes in—a method to virtually take a snapshot of your current statuses, drivers, and any other pertinent details.
Imagine instantly knowing which trucks were at the depot or in transit on any given day, like December 11th, at a glance!
Building Your Tables
To get started, you need a structured setup of tables that track records over time. This includes adding key fields, such as formulas, that are necessary for automation:
- Base Table: This table captures the day-to-day operations.
- Fields: Add two essential fields:
- A Yes/No field to assist with automation control.
- A Formula field to house today's date, useful for automation logic.
- Duplication for Historical Logging:
- Duplicate your original table's structure (excluding records).
- Remove the Yes/No and Formula fields from this duplicate since it focuses on history.
- Add a link to the Summary Table required for organizing historical data.
Crafting the Summary Table
- Purpose: This table holds records that represent each date.
- Automation-Driven: Records are created automatically.
- Content: Links all historical logs from the same day, calculating how many trucks were at each status (e.g., depot or maintenance).
Setting Up Automations
This is the exciting part—setting up daily automations using just two simple processes.
Automation 1: Daily Trigger
- Activation: At 12:15 AM each day, automatically create a new record in the Summary Table. This record is labeled with the current date.
- Setup: Use today's date as the title of this summary record.
Automation 2: Historical Log Creation
- Task: For every truck, when the Yes/No field is marked 'Yes' by Automation 1, create an identical record in the historical log for preservation.
- Linking: Ensure each record connects back to the Summary Table using the current date, automating historical linkage.
Execution and Results
Now, every day, a new summary record is created. Here's how it unfolds:
- Records Update: Fields like status and driver details are recorded as-is.
- Link Creation: Automatic linkage to the newly created summary is established.
Flexibility and Functionality
This method, despite initially seeming complex, offers clear daily summaries and historical insights without manual effort! Adaptations and innovations are endless, enhancing historical reporting to suit your business needs. Until next week, keep exploring SmartSuite's SmartTips!
