Unduplicated Demographic Reporting Dashboard Across Four Programs

Summary

The requester wants to consolidate demographic data (age ranges, race) from four programs (Linkage & Nav, Housing, Transportation, Non-Emergency Medical Rides) into a unified reporting view. Currently, demographic forms are scanned PDFs uploaded to Smartsheet, with inconsistent data entry (handwritten race fields, checkboxes, DOB vs. age ranges). The goal is to count individuals once per program (but they can appear in multiple programs) and produce HIPAA-compliant reports showing totals by age bracket and race.

Problem

Demographic data is trapped in scanned PDFs and inconsistently recorded across programs. No way to quickly see aggregated counts by age bracket or race without manual review of hundreds of attachments and spreadsheet rows.

Users affected

Program staff who need to report on demographics, leadership tracking service gaps, compliance officers ensuring HIPAA adherence, and potentially funders requiring demographic breakdowns.

Current process

Staff collect paper demographic forms from clients. Forms are scanned and uploaded as PDF attachments to Smartsheet rows. Some programs have date of birth in structured fields (Linkage & Nav), others rely entirely on scanned ROI or similar documents. Race is collected via checkbox on some forms but often handwritten. Age brackets and totals are manually reviewed or counted by scrolling through rows.

Desired process

Staff continue collecting paper forms, but the solution extracts demographic data from PDFs or structured Smartsheet fields, deduplicates individuals within each program, and produces a dashboard or report showing counts by age bracket and race for each program. The system flags incomplete or inconsistent data and maintains HIPAA compliance.

Systems involved

Smartsheet (Forms Manager, row attachments, structured fields), potentially OCR or form extraction tool if PDFs need to be parsed, HIPAA-compliant data storage/processing environment.

Inputs

Scanned PDF attachments in Smartsheet rows (containing demographic checkboxes and handwritten race), structured date of birth field in Linkage & Nav program, checkbox responses for race in Forms Manager, manual entries in other program sheets.

Outputs

Demographic dashboard or report showing unduplicated counts per program by age bracket (18-25, 26-35, etc.) and race. Ability to filter by program and export summary tables. Alerts for data quality issues (missing fields, multiple checkbox selections, illegible handwriting).

Must-haves

HIPAA compliance, unduplicated counts within each program, ability to count same person across multiple programs, extraction or parsing of existing PDF attachments, age bracket reporting.

Nice-to-haves

Automated OCR to read handwritten race data, real-time dashboard with filtering, data quality scoring, integration with existing Smartsheet workflows without requiring staff to change paper forms.

Risks / Unknowns

Unclear if the current forms are standardized across all four programs or if each has different layouts. Handwritten race data may be illegible or inconsistent, requiring manual cleanup. HIPAA compliance requirements (encryption, access controls, BAA with vendors) not yet defined. Unknown if Smartsheet is already HIPAA-configured or if a separate secure database is needed. Deduplication logic unclear—what field identifies the same person across programs (name, DOB, client ID)?

Feasibility

Complex but achievable. The main challenges are: (1) extracting structured data from scanned PDFs (OCR quality depends on form legibility), (2) implementing cross-program deduplication without a clear unique identifier, (3) ensuring end-to-end HIPAA compliance (Smartsheet may require a BAA and configuration). Straightforward if Linkage & Nav's DOB field can be used as a template and forms are standardized; unclear/risky if each program uses wildly different formats or if deduplication relies on fuzzy name matching.

Recommended next step

Request access to the 'HT demographic info' sheet in Forms Manager and examples of the actual scanned PDFs from each program. Review current form layouts to confirm data fields, identify a unique identifier (or define deduplication rules), and assess HIPAA compliance posture of existing Smartsheet environment.