An Airtable KPI dashboard can display a precise percentage calculated from incomplete data. Before asking an AI model to explain a trend, define what each number means, where it came from, and when its source was last checked. A stale value should remain visibly stale.
This article adapts AI Automation Blueprint's Airtable dashboard topic into a data-contract exercise. It includes a small JavaScript calculation you can test locally. It does not deploy an Airtable integration, promise real-time synchronization, or estimate a vendor bill.
What belongs in a metric record?
Start with a written definition for one operational metric. For a count of completed support tickets, specify the reporting period, time zone, included queues, and the specific event that counts as completion. Decide how reopened tickets affect the result before a chart starts circulating.
Store the reporting window separately from the time the data arrived. A successful sync today can still contain last week's records. Displaying an ingestion timestamp as the reporting period makes old evidence look current. And that misleads stakeholders.
| Field | Purpose |
|---|---|
| Metric key | Stable name for the agreed definition |
| Window start and end | Period covered by the underlying events |
| Source checked at | Time the source was inspected |
| Current and previous counts | Inputs calculated under the same definition |
Airtable's formula field introduction describes deriving values from fields. A formula performs arithmetic. It cannot establish that the source events were complete or that two reporting windows use the same definition.
How should missing values affect the calculation?
Do not silently convert a missing count to zero. Zero completed tickets and an unavailable ticket feed are different conditions. Retain an explicit unavailable state so the dashboard distinguishes between them.
The function below accepts non-negative safe integer counts. It rejects numeric strings, missing values, and fractions. This contract is intentionally narrow: currency and rates need their own definitions and validation rules.
function compareCounts(current, previous) {
const valid = value => Number.isSafeInteger(value) && value >= 0;
if (!valid(current) || !valid(previous)) {
return { status: "unavailable", delta: null, percentChange: null };
}
if (previous === 0) {
return { status: "no_baseline", delta: current, percentChange: null };
}
return {
status: "comparable",
delta: current - previous,
percentChange: ((current - previous) / previous) * 100,
};
}
The code has been checked with local JavaScript fixtures. A zero baseline deliberately produces no percentage, even when both counts are zero. That is an intentional reporting convention, not a mandate for every organization. Document your convention before comparing outputs across dashboards.
Which timestamp tells you whether data is fresh?
Airtable's last-modified field documentation explains that this field tracks edits to non-computed fields. It is not a reliable signal that dashboard inputs were revalidated. Rollup recalculations can slip past unnoticed.
Track a dedicated source-check timestamp inside the ingestion process. When a refresh fails, record the ingestion failure instead of overwriting the previous success timestamp with the failed attempt time. Set a hard staleness threshold for warnings. Choose a limit that matches how the metric is used; an incident display may need a stricter limit than a daily report.
How do you introduce an AI summary safely?
- Validate a single metric against the underlying source records.
- Test missing inputs, a zero baseline, and a failed refresh.
- Display the accepted numbers and their reporting window without generated commentary.
- Add an optional summary that reads those accepted values and preserves unavailable states.
Keep calculation outside the language model. Summaries can describe measured changes or suggest triage questions. They should never patch missing numbers, redefine reporting windows, or trigger customer alerts simply because the prose sounds authoritative.
Is this a no-code build?
The surrounding dashboard may use visual tools, but this example relies on custom code. Be honest about that boundary. If you replace the script with native Airtable formulas or connector automations, test the exact same missing-value and zero-baseline edge cases before claiming parity.
Top comments (0)