VNA of Texas – Meals on Wheels: Logistics / ETL Integration
2024
Meals on Wheels – North Texas
Established in 1934, VNA is a nonprofit 501(c)(3) organization that helps older adults live with dignity and independence at home. VNA is the community’s most trusted provider of quality healthcare services in the home and is the oldest, most experienced Hospice Care provider in Texas. VNA offers Meals on Wheels in Dallas County, and VNA Hospice and Private Care in Collin, Cooke, Dallas, Denton, Ellis, Grayson, Henderson, Hunt, Kaufman, Navarro, Rockwall, Tarrant and Van Zandt counties. Call 1-800-CALL-VNA for more information.
The Challenge:
VNA Meals on Wheels (MOW) program delivers hot, frozen and shelf-stable meals to homebound and needy elderly and disabled throughout Dallas County. Meals are prepared in VNA’s kitchen and delivered by nearly 4,000 volunteers and some paid drivers. Daily census of meals is scheduled and tracked through Netsmart, a medical records database system.
VNA’s MOW also added Omnitracs RoadNet, a logistics routing system produced by United Parcel Service (UPS), to assist volunteers and drivers on their daily routes. To do so, data needed to flow between Netsmart and Netsmart in a variety of ways. An automated system was needed to pull data from Netsmart on a daily basis for regular daily meals delivery. A holiday system was needed to address managing special delivery days, such as Thanksgiving.
Additionally, some elements of data are not in Netsmart and yet are needed to supplement the RoadNet data. Besides the routing, VNA also needs reports so the kitchen can prepare the appropriate number and types of meals. These reports are used to tag meals containers for drivers to improve the efficiency of delivery.
Manual data entry
Manual driver routing
Manual meal assignments
The Solution:
B3 Software created a Microsoft SQL Server and .NET Framework application to handle management of data exchange between systems and reporting for the kitchen. B3 Software consulted with VNA to discover the required use cases, each handled with the end-user in mind. The application consists of a command line tool which is used in the nightly task, and a desktop application which is used by VNA personnel.
The application was also designed using TDD (test-driven development). Though not 100% comprehensive, the tests inherently document each unit of the code’s functionality. An intermediate SQL Server database stores the extra data for RoadNet and provides historical information about meal orders. The desktop application manipulates data in this database before sending it on to RoadNet.
The Results:
VNA can efficiently manage fairly complex delivery situations without having to re-key thousands of records that already exist in Netsmart. The system is reliable and easy to update to accommodate new challenges because of the TDD design.