Use separate original and current field groups: Rate at transaction plus its source and effective timestamp are written once when the currency and amount fields are ready, while Current rate and its metadata are refreshed by a scheduled automation. That separation keeps a later market move from rewriting the historical value your transaction used.
Create the Transactions table
Create a table named Transactions with these fields: From (single line text), To (single line text), Amount (number), Rate at transaction (number), Transaction converted (number), Transaction source, Transaction market session, Transaction effective at, Archived (checkbox), Current rate (number), Current converted (number), Source, Market session, Effective at, Checked at, Sync status, and Sync error. Use date/time fields for Transaction effective at, Effective at, and Checked at; use single line text for the remaining metadata and status fields. Add a view named Needs FX refresh, filter Archived to unchecked, and sort Checked at oldest first with empty values first so successive runs rotate through the records.
The two rate groups have different meanings. The Transaction * fields are an accounting or audit snapshot. The unprefixed current fields are a new indicative observation used for a dashboard or estimate. Do not use the latter to restate the former.
Snapshot the original transaction rate
Create an Airtable automation with When record matches conditions as the trigger: From, To, and Amount are filled and Rate at transaction is empty. Add a Run a script action, set mode to snapshot, pass the triggering record’s recordId, and paste the downloadable script. Add an Airtable Automation secret named exchangerateApiKey; the script reads it through input.secret so editors without secret access can run the automation without seeing the value. The snapshot writes Rate at transaction, Transaction converted, Transaction source, Transaction market session, and Transaction effective at only when the original rate is empty. A retry sees the existing value and leaves every original field unchanged.
exchangerateApiKey in the script editor’s Secrets section for both automations. Keep its name unchanged. Do not put the value in a table field, URL, shared description, or code literal; Airtable limits editing of scripts that use secrets to people with access to those secrets.Refresh current estimates on a schedule
Create a second automation with At scheduled time as the trigger. Set mode to refresh, leave recordId empty to process the first 25 records in Needs FX refresh, and paste the complete Airtable script. Each run makes at most 25 HTTP requests, below Airtable’s 50-fetch limit, and writes in batches of at most 50 records. Keep this example to a small reporting base; larger tables need a planned queue and request grouping. At 25 distinct pairs once daily, budget up to 750 API requests per 30-day month, plus initial snapshots and retries.
Refresh updates only Current rate, Current converted, Source, Market session, Effective at, and Checked at. It never updates any Transaction * field. HTTP failures and malformed responses set Sync status to Error and retain the last successful current values.
Make failures visible
A 401 means the Automation secret needs attention; a 429 means the schedule or account limit needs attention; a missing symbol means the pair is invalid or unavailable. Keep the last successful value visible with its prior Checked at, and use Sync error for the operator-facing diagnosis. Never turn a failure into a zero-rate row.
Check Airtable plan limits
Airtable’s current Run a script documentation documents native Automation secrets and their editor protections. Its automation limits say Run a script is unavailable on the Free plan and that failed runs count too. A daily refresh is usually enough for a reporting table; it does not turn a daily-reference currency into an intraday quote.