Mobile Podiatry Practice

Automating Pre-Visit Patient Prioritization Across Multiple Care Facilities

Industry: Healthcare — Mobile Podiatric Care (SNF/LTC) Structure: Independent physician-owner, multiple skilled nursing and long-term care facilities Engagement: February – March 2026

---

The Problem

Before every facility visit, a mobile podiatrist needed to answer two questions: which existing patients were overdue, and which new residents he'd never seen.

He was doing it by hand — cross-referencing his own patient history files against each facility's current census, one patient at a time, across five rotating facilities. It consumed real time before every single visit. It was also unreliable: patients got missed, recently-seen patients surfaced as overdue, and the source data was inconsistent enough to make automation harder than it sounds.

His patient history lived across multiple Google Sheets — manually entered, with inconsistent date formats, name typos, and missing headers. The facility census came from PointClickCare exports, which had their own problems: room changes created duplicate rows, and inpatient and outpatient records were mixed together. Getting a clean priority list out of these two sources required real data engineering before any output logic could run.

---

The Work

Phase 1: Cross-Reference Engine

The first task was cleaning what existed. Patient history files were consolidated into a single dataset. PointClickCare census exports were downloaded for all five facilities, deduplicated for room changes, and filtered to separate inpatient from outpatient records.

From there, I built a matching engine that cross-referenced patient names from the history files against each facility's current census — with tolerance built in for the name variations and formatting inconsistencies across source systems. Matched patients got their room number, payer, and last-seen date. Unmatched residents were flagged as never-seen and populated with their admission date. Recency flags surfaced patients not seen in 3+ and 6+ months separately.

A logic error caught during QC — recently-seen patients surfacing incorrectly as overdue — was identified and corrected before the list reached the physician. The first working deliverable was in hand before his February 19 visits.

Output: an Excel workbook with two tabs. The Pivot List let him click a facility and see the top patients by urgency — overdue, long overdue, or never seen — with name, room number, and relevant date. The Full Resident List gave a consolidated view across all five facilities in one table.

Phase 2: Automation and Handoff

The manual refresh process was replaced with a Google Apps Script automation. A ribbon menu in Excel triggers the full report run — pulling updated data, applying all matching logic, and producing per-facility output pages — in under 30 seconds.

An automated email function sends the report as attached PDFs to any specified recipient. UI confirmation dialogs were designed so office staff could trigger the send without any technical knowledge.

Two Loom walkthroughs were produced: one covering how to download the updated census from PointClickCare and drop it into the correct Google Drive folder, and one covering how to run the report, review output, and print or email PDFs.

Data validation rules were added to the patient list templates to reduce entry errors going forward. An "Exclude from report" column was built in to suppress specific patients — transfers, hospice, etc. — from output.

---

Outcomes

Manual pre-visit prep replaced with a 30-second report run. A workflow repeated before every facility visit now runs automatically and produces clean, printable output in under half a minute.

Five facilities, one unified view. All patient history and current census data consolidated into a single cross-referenced report, with per-facility pages ready to print or hand to staff.

The workflow is fully delegable. Office staff can trigger and email the report with no technical knowledge. The physician is no longer involved in prep at all.

Data quality addressed at the source. Validation rules built into the patient list templates prevent the formatting and entry errors that made clean cross-referencing impossible before.

---

Engagement Lead: Michael Cohen Support: Developer (code review) Stack: PointClickCare (manual export) · Google Sheets · Google Drive · Excel · Power Query · Google Apps Script