Patient Visits and Medicine Stock for a Small Clinic

Two problems, side by side

Most small clinics run on two spreadsheets that grow in different directions. The patient sheet gets a new row for every visit, so the same person appears twenty times with slightly different spellings. The medicine sheet has one row per medicine, with the expiry date of whichever box someone remembered to type in. Here is how the everyday jobs compare.

Patient visits: one patient, many visits

Patients holds each person once: name, date of birth, Phone (tap to call) and any notes you keep. Visits holds one record per visit, linked to the patient with a One-to-Many relation, so a patient has many visits and each visit belongs to one patient. Each visit also links to a doctor from the Doctors table and records the date and time, the complaint and the outcome.

The treatments given in a visit go in Line Items, so one visit can hold a consultation, a wound dressing and an injection, each line with its own quantity or note. A Rollup on Visits adds up the treatment prices into a total for the visit.

Back on the patient, a Rollup counts their visits, and a Formula in plain words, "days since the patient's latest visit", shows how long it has been. Because the Formula is a property like any other, you can sort patients by it or filter for people who haven't come back in six months.

1. Add the patient once. At the front desk, staff tap + Add Data on Patients, or pick an existing patient so nobody is registered twice.
2. Record the visit. A custom form for the front desk can have the patient required, the doctor picked from a list and a default value for the visit type. Staff can add a photo or a referral letter as a Document.
3. Add the treatments. The doctor or nurse adds a line for each treatment, and the visit total adds itself up.
4. Check the history. Open the patient and every visit is there, with the visit count and the days since the last one at the top.

Medicine stock: one medicine, many batches

Medicines holds each medicine once: name, form (Single Select: tablet, syrup, injection) and the minimum you like to keep. Batches holds each delivery as its own record, linked to the medicine, with a batch code, quantity, received date and expiry date. A Rollup on Medicines adds up the quantity across its batches, so you see total stock without counting boxes.

A Formula on each batch gives the days to expiry and a status: Expired, Expiring soon or OK. Then save a view called "Expiring in 30 days" with the Relative to today filter on the expiry date (Next, 30, Days), sorted by expiry date. That list is what you use first, and what you move to the front of the shelf.

Try it here. Move Today forward and watch batches cross into Expiring soon. Switch to the Expiring in 30 days view to see only those.

If you run a pharmacy rather than a clinic, the Pharmacy template uses the same idea with Medicines, Batches and Sales.

Who sees patient data

Patient details are sensitive, so don't leave them open to everyone. Set the Patients database to Restricted and give access one person at a time: Editor for doctors and the front desk, Viewer for someone who only needs to look, and No access for staff who only handle stock. For tighter control, keep patient data in a private workspace that only chosen Members can open. Removing someone's access disconnects them straight away.

Staff join as Members with no seat cost and use the phone app with cards and forms. Only the Owner and Admins can export to XLSX; Members can't.

Questions people ask

Start from the Clinic template: Patients, Visits, Doctors and Treatments, already linked and ready for your own clinic.