WA Housing Finance Commission: Agency Database Application

Client

Washington State Housing Finance Commission

Industry

State agencies

Technologies

SQL Server, Access, Word, Excel, MapPoint

Overview

Project highlights

150+ related tablesReferential integrity enforced, with no orphaned records.
Fast by designPanels load only when needed, one record at a time.
Document automationComplex Word merges produce documents like a full IRS tax form.
Email or mailAutomated mailings reach every contact by whichever method is on file.

Before

The challenge

The Washington State Housing Finance Commission serves Washington homebuyers. In 1999, the Commission consulted J Street to implement a new centralized database and several front-end applications to track financing operations and manage compliance.

They needed a centralized database application to improve business processes across the agency.

After

What we built

J Street built a centralized application on a SQL Server database containing over 150 related tables, with every relationship carefully defined and referential integrity enforced so there are no orphaned records. It has been in production since 2001.

Users select, sort, and find project records, then drill down for details, using client/server techniques for fast response without burdening the database. Our standard panel interface loads each area of information only when it's needed, and complex screens bring together multiple related subforms with different levels of calculated totals.

The system links each project to its electronic documents, connects with Excel workbooks, produces complex Word merges such as a full IRS tax form, and sends automated mailings by email or physical mail, depending on the information on file for each contact.

Inside the system

A closer look

Find, then drill down

This is a good example of J Street's standardized approach to desktop application design. Users can select, sort and find project records until they see the projects they want to work with, then they can drill down for more details. This approach helps the user get their job done quickly, and uses client/server techniques for fast response time while not burdening the SQL Server database.

Fast panel interface

Once the user has drilled down to a specific Project, they can view and edit many areas of information. J Street has a standard panel interface that we developed, similar to a left navigation area on a web page. It allows the user quickly to switch between different types of information about the record they are editing. These panels are only loaded when they are needed (unlike the default behavior of traditional tabs), so they are very quick. We also provide our own custom navigation buttons at the bottom, which use client/server principles by only loading one record at a time rather than a large updatable recordset.

Complex screens

We can build complex screens. This one has multiple related subforms of data, along with different levels of calculated totals.

Linked documents

We know how to integrate your database records with your electronic documents. On this screen, you can see multiple documents related to this project. Each one is a link to the location where the document resides on the network, and the documents can be opened, viewed, edited and printed right from this screen.

Excel integration

Often critical business data and calculations can be found in Excel spreadsheets. We can link, import and export your data with Excel workbooks.

Complex Word merges

We do complex Word merges. This is an example of a full IRS tax form that our system can create and populate with all the relevant data, saving our client many hours each time.

Automated mailings

We can automatically generate mailings. This system sends out automated emails to customizable lists based on criteria chosen by the user, using these powerful selection screens.

Email and physical mail

In addition, this system can do something that online email management companies don't - it can send a mix of email and physical mail, depending on the information you have for your contacts. This ensures that you can contact everyone regardless of method, and reduce your costs as you update your database with their email addresses.

A 150-table database

We build complex databases. This system uses a SQL Server database containing over 150 related tables. We carefully define all the relationships, and we always enforce referential integrity to ensure that your data is valid with no orphaned records.

“

Getting our centralized database up and running was no small feat, but everyone's hard work has really paid off. Having such a comprehensive database is an incredible asset. Since implementation, we are both saving a tremendous amount of staff time while also significantly reducing errors. For a public policy-driven program like ours, the greatest tool the integrated database provides is the ability to easily and comprehensively query our data. This has tremendous value for conducting policy analysis and program development.

Steve Walker

Director of Tax Credits, Washington State Housing Finance Commission

The outcome

How it turned out

In production since 2001, improving business processes across the agency.
In the Commission's words, saving a tremendous amount of staff time while also significantly reducing errors.
Staff can easily and comprehensively query their data for policy analysis and program development.
Word merges save many hours each time a tax form is produced.
J Street continues to provide database support, application maintenance, and technical consulting.

See more of our work for state agencies.