ID Card Project — Activity Tracking Application
Sixteen spreadsheets and fifty-plus users replaced by one Model-Driven Power App — turning a two-day manual reporting exercise into a thirty-minute one.
Screenshots of live humanitarian systems are not published. Where visuals appear, they are illustrative rebuilds created with synthetic data that reproduce the structure and design of the original work without exposing any operational or personal information — on data-protection grounds.
Context
Refugees become eligible for a UNHCR identity card at age 12. Tracking each issuance through its stages was being done in sixteen separate Excel workbooks maintained by more than fifty users — one set per activity, filled in across sites, day after day.
Every failure mode you would expect was present: missing entries, corrupted rows, the same record captured twice in two different sheets, and columns that drifted apart as people added their own. At the end of each day someone had to merge sixteen files, clean them, resolve the overlaps and only then build the Power BI report. A daily report to the manager took around two days to produce — which meant it was stale before it arrived, and effectively could not be produced daily at all.
The proposal to replace the spreadsheets with a proper application was mine. My Head of Unit backed it, and it was built.
Approach
I designed and built the tracking application as a Model-Driven Power App on Dataverse — moving the capture point from a file to a form.
- Capture at source, validated. Fifty-plus users now enter against a defined schema with required fields and controlled values, so the classes of error that dominated the spreadsheets — missing data, malformed rows, duplicated records — are rejected at entry rather than repaired at midnight.
- Why Model-Driven, not Canvas. This is identity-related workflow at multi-user scale. It needs a relational data model, role-based security and a native audit trail — all standard in Dataverse, and none of which should be hand-rolled.
- One record, no merge step. Because everyone writes to the same store, the nightly consolidation of sixteen workbooks simply stops existing. There is nothing left to reconcile.
- Modelling. Each pipeline stage is a tracked entity with its own timestamps and ownership, so throughput and bottlenecks became queryable facts rather than inferences.
- Reporting. A Power BI dashboard over Dataverse, refreshed on schedule instead of assembled by hand.
How it looks
Issuance pipeline by stage
Rebuilt with synthetic volumes — the stage structure is the real one
Outcome
2 days → 30 minutes
Reporting turnaround fell from two days to thirty minutes — roughly a 96× reduction — and sixteen workbooks collapsed into one system used by more than fifty people.
The turnaround figure understates the real change. The old process could not reliably produce a daily report at all, because consolidating and cleaning sixteen files took longer than a day. Management now has genuine stage-by-stage visibility of the issuance pipeline, and because the report is cheap to produce, it actually gets used.
What I’d do differently
The dashboard reports where records are. It does not yet report how long they have been sitting there. Stage-level ageing — a simple “days in current stage” measure with a threshold alert — would turn a status report into an exception report, which is the more useful thing.
Migrating fifty-plus people off spreadsheets they had used for years was the harder half of this project, and I under-planned for it. Structured entry is stricter than a blank cell by design, and that friction needs explaining before rollout rather than during it. Next time I would pilot with a single site first.