A nightly records mirror that staff can build on

The main scenario runs in one window a day, at the end of the clinic's day, and asks the records system only for that day's records (from and to both set to today), so each run is small and a re-run on the same day is safe. It reads a cached API token from a shared data store rather than logging in itself.

Each patient is found in Airtable by patient number and created only if absent; their latest doctor, department and last-visit date are updated either way. A visit is created only if none exists for that patient, date and doctor, so the existence check is the deduplication key.

Every visit then links itself to reference rows: each diagnosis code, each medication by local code, and billed items across ten categories from procedures and investigations to vaccines, dental and home healthcare. The native link update would overwrite, so the scenario rebuilds each link array by hand and appends only if the link is not already there.

A second nightly scenario feeds a separate base used by a team that phones patients after an acute illness. It picks visits not yet marked synced, within the last 30 days and capped at ten a night, parses vitals out of text (blood pressure split into systolic and diastolic), derives the specialty from the department, and creates a prescription row per drug with a medicine-class tag and an antibiotic flag.

A third scenario compares a drug-reference spreadsheet against the medication table in Airtable. Each unprocessed row is marked 'Update' if its code exists, with a flag set in Airtable, or 'NF' if not; the status column is both the marker and the queue, so a processed row drops out of the next read.