Replacing Excel-Based Referral Tracking with a Salesforce-Native Workflow Platform for a Specialty Home Pharmacy
HomeFree Pharmacy Services ran every stage of its patient referral lifecycle on separate spreadsheets. We built the Salesforce workflows, queue management, and Azure data pipeline that replaced them.
Client
HomeFree Pharmacy Services
Industry
Healthcare
Service
App Modernization
Engagement
Active
Team
5
01 Challenge
HomeFree Pharmacy Services coordinated every stage of its patient referral lifecycle from enrollment through insurance clearance to production and delivery across separate Excel files with no shared system of record, no live status between teams, and no audit trail when a handoff was missed.
02 Solution
Ideas2IT built Salesforce-native workflows that replaced the spreadsheet layer: admission objects with attached referral, patient, clinician, and Rx data; queues for each workflow stage; and task tracking across all teams involved in the referral lifecycle. An Azure Cloud Functions pipeline then connected Salesforce to Pioneer, preprocessing patient records in five days and eliminating duplicate entries with a hashing-based unique ID.
03 Outcome
Pioneer data was preprocessed, deduplicated, and standardised in five days, with zero missing patient records after pipeline cutover. Admissions moved off Excel into a Salesforce-native queue with live visibility for all teams across the referral lifecycle.
Phase 01
Salesforce workflow platform: replacing spreadsheet-tracked admissions with a single orchestrated referral lifecycle
The constraint the Admissions team was working under was structural: every stage of the patient referral lifecycle had its own spreadsheet, and no spreadsheet knew what state any other was in. A patient who cleared insurance in one file had no programmatic path to the Production team in another. The first engineering decision was to model the full lifecycle inside Salesforce rather than alongside it.
Ideas2IT built
- Admission objects with attached referral, patient, clinician, forms, Rx, and additional information.
- Queues were created for each workflow stage so the Admissions team could manage workload rather than chase status across files. Task and activity tracking gave all teams a shared operational view.
- Reports and analytics dashboards, built in the style of existing MedRec dashboards, surfaced admissions data on the Salesforce home page.
This Phase Produced
- Admission object model (Referral, patient, clinician, forms, Rx, and additional info attached)
- Salesforce workflow automation (End-to-end referral lifecycle replacing Excel-based tracking)
- Queue configuration (Per-stage queues for admissions workflow management)
- Task and activity tracking (Shared operational view across Admissions, Production, and Delivery teams)
- MedRec-style analytics dashboards (Admissions reports built to existing dashboard conventions)
- Salesforce home page admissions view (Live admissions visible on application home page)
Phase 02
Azure data pipeline: connecting Salesforce to Pioneer with zero data loss and a unified patient identifier
The second problem sat at the boundary between Salesforce and Pioneer: a large pool of patient records that needed to move between systems, be accessible with a unique identifier in both, and arrive without duplicates, format inconsistencies, or data loss.
Ideas2IT built
- a hashing mechanism on Azure Cloud Functions that generated a unique ID from mandatory patient fields, then ran the Pioneer dataset through a preprocessing stage in five days deduplication, date-of-birth standardisation, name format normalisation, and phone number normalisation.
- An Azure Pipeline triggered on Salesforce activity sends data to Pioneer with the Unique ID and returns a patient status response.
- New records entering Pioneer generate a Unique ID through the same mechanism, so identity remains consistent as the dataset grows. No patient records were missing after the pipeline went live.
This Phase Produced
- Azure Cloud Functions hashing mechanism (Unique patient ID generated from mandatory fields)
- Pioneer data preprocessing run (Dedup, DOB, name, and phone standardisation completed in 5 days)
- Salesforce-to-Pioneer Azure Pipeline (Triggered transfer with Unique ID and patient status response)
- Pioneer Unique ID extension (New Pioneer records generate ID through same mechanism)
- Salesforce pipeline extension (Existing pipeline extended to carry Unique ID on transfer)
- Patient data visualisation (Visualisation of patient data post-migration)
The Outcome
From batch-file reporting and siloed spreadsheets to a live, auditable referral platform
The referral lifecycle improvements followed directly from replacing a spreadsheet-per-stage model with a single Salesforce object that all teams read from and write to. Data integrity across the Salesforce-to-Pioneer boundary was the result of building the preprocessing and hashing mechanism before any records moved, not as a remediation step after. A five-day preprocessing run with zero missing records is what happens when the deduplication and standardisation logic is built into the pipeline rather than assumed of the source data.