This application was built to improve tracking process for kids with hearing loss. It had complex tracking logic with many possible cases. For example, if initial test fail (or missed or passed but with risk factor, etc.) the system should remind to send letter or call to PCP in certain number of days but if it received re-screen during particular time frame it shouldn't send original letter but another one instaed. Plus, I was finishing database (it was quite big - full Center of Disease Control dataset, about 80 tables) and application design but client couldn't finalize all rules yet. I decided to put entire logic into database which allowed us to adapt it to other states' needs with minimal efforts.

As you can see on fragment of database model below, each Patient can have one or more tests linked to one of the predefined (all possible combinations) ScreeningResult. Each screening result may be linked to an Event which may have one or more ActionTemplate (for example, 'Send letter #5 to PCP' or 'Call Parents'). Each 'letter type' action template may have letter template from which system generates actual letter putting real data for particular child. Now, what the smart follow-up logic component had to do is to link new test result with one of ScreeningResult, select action template(s) and create a follow-up action for particular child.

For this project I developed set of very complex reports using .NET Crystal Reports (cross tab, sub-reports, hundreds of lines of long queries with inner queries and unions, custom functions but without a single cursor) which I'll not publish here because it requires pack of good beer and entire database model.

This is how I store multiple addresses and phones and change history.

Here is a user interface for this application (client-server version, C#.NET). Idea was to show user necessary high level information without pop-up screens. Also, user can browse search results without switching between the screens or pop-ups. The users with low screen resolution can hide Search and Import panels. For this project I developed several controls in LL namespace ('tool window' type panel integrated with toggle button, flat button and drop down list).