It’s time to upgrade to purchase a new case management system or upgrade your current one, but you don’t want to lose the data that you’ve been gathering for years about your clients. To do this, you must convert your data from the old system to the new. Like translating poetry from one language to another, this is much harder to do than it seems.

This article spans the range from very simple conversions to very detailed conversions. Learn how to clean up your existing data or see how a full blown conversion occurs.

Part I: Understanding Conversions

What could be difficult about transferring data? Everything! When doing a conversion, the person performing the conversion has to understand both the Clients Case Management (Clients) database as well as the office’s current database system. The problems that occur during a conversion revolve around five areas concerning your old database:

A. What format is the information stored in?

B. What is the relational setup?

C. How were the fields constructed?

D. Did you gather sufficient information?

E. How accurate was data entry, how many errors are there, and did you have technical problems such as a network crash?

These areas do not exist separately. They are interrelated and this interrelation can cause geometric increases in the difficulty of doing a conversion. For example, if you have client information and case information stored separately, a network crash can destroy the linkage between the clients and their case information.

A. What format is your information stored in?

Databases store their files in different formats, like different languages. In some cases, Microsoft Access can read the file that stores your data directly and treat it almost exactly as if it was created in Access. This is true for Dbase (III, IV, V) files, Paradox, ASCII, FoxPro, and several others. It is possible to get an ODBC driver (Open Database Connectivity) that allows you to read other databases from Access. This greatly simplifies things.

If none of the above work, it is usually because of two possible reasons.

1. The data resides on a computer that can’t run Access, such as a very old machine or a Mini-Computer. It is then necessary to transfer the data to a media (floppy disk, CD, etc.) that can be read by a PC.

2. The format of your old database is not recognized by Access and can’t be exported into a readable format. A software program called Data Junction is very useful in the situation where the format is not readable by Access. It was designed to convert the data in databases from one format to another, and it does so extremely well for a large number of formats. If this program doesn’t work, you probably need a specialist’s help. In some cases, exporting and translating the data might be fairly easy, but very obscure. Unless the operator has done it before, it can be time consuming to figure out how to read or convert the data.

B. What is the relational setup of your old program?

A relational database stores data in multiple tables (file drawers). This is to prevent information duplication. For example, if you run a private bar program, it is easier and more efficient to store your private attorney information in one table and your client information in another. Then when a client is referred to a particular attorney, you simply place the attorney’s number on the client record rather than their full name, address, phone, etc. There are disadvantages to this method. The database is more complex, it is more difficult to transfer a whole client intake, and it is more subject to breakage.

This becomes very complex where the client information is stored in multiple tables and the structure of the database is not clear. For example, some databases store client information in one table, the case information in another, the closure information in a third, the case notes in a fourth, etc. In some situations, it has been necessary to use up to six or seven tables to make up one client intake sheet.

The relational design of the database can also cause the information to become totally or partially unusable. Imagine a database where the client and case is stored separately and there is data bleed across the network. This can result in clients without cases and cases without clients. Unless the paper file is retrieved and the missing linkage recovered, all case records without a valid client number are totally useless.

C. How were the fields constructed?

Above, we learned that information about a case can be stored in multiple tables spread throughout the database. How the information is stored in the blanks or fields on the intake sheet can further complicate a conversion. It is easier to combine fields to make a larger field than it is to take a combined field and try to separate it into its component parts.

For example, it is better to have a database that stores the area code and telephone number in two fields rather than one. The problem of a single field telephone number conversion pales in comparison to a situation where the adverse party is stored in one field. It is very difficult to break up a single field into an adverse party first name and last name. For example: Joe’s Auto Parts needs to go only into the adverse party’s last name, but what about John Jones, John P. Jones, John P. Jones Jr., John P. Jones, Jr., and Jones, John P. It is nearly impossible to break these up into two fields, since the last name is stored in different places in the field. It becomes even more difficult when the data is entered inconsistently. For example, a field where City, State, and Zip Code are stored in one field and some are filled completely, where others have one or more pieces of information missing.

D. Did you gather sufficient information?

You can’t convert or report on information you didn’t gather. This depends on the design of the database. For example, some databases don’t store a date on which the case was. It is therefore impossible to tell how many days the case has been open, etc. In this example, the date could be faked by using the file number if it contained the year and month when the file was opened and then using the first day of the month as the date opened.

Clients always has some fields for which the old database does not have an equivalent value. This is usually true for fields of lesser importance, such as marital status or living arrangements. In those fields, the information can be entered as unknown, left blank, or given a default value. For example, intake type field is a letter representing telephone, in-house, outreach, or letter. Where the old database didn’t gather this information, it is defaulted to in-house, unless a different request is made.

There are always some fields that do not convert from the old database to Clients. Sometimes this information can be placed in the memo field. Other times it can be put in one of the three user-defined fields, which are explicitly designed for this purpose. Occasionally, it is discarded when not needed for reports.

In some situations, your database stored the information in a different manner than in Clients. Some systems store age as a range rather than a birth date or an exact age. If the age range is 0-18, do you convert the clients age to 10 or 17? It’s important to give specific advice for these fields.

E. How accurate was data entry, how many errors are there, and did you have technical problems such as a network crash?

Of all the problems, this is usually the worst. The rule of thumb is that for every record, there are at least five errors, sometimes as many as ten. Thus, if you have 100,000 cases, you could have 1,000,000 errors. Sometimes the information is not critical. Other times, it’s indispensable. For example, imagine one out of eight cases missing a sex for the client (required for the age/race report). You might be able to use first name to guess for some of these records. How about one out of ten cases with an invalid or missing problem code ( required for the CSR report). Perhaps you give the case the miscellaneous code 99.

Kemp’s Case Works (KCW) has converted approximately 3.5 million clients records. The primary problem with conversions is that the data usually has many errors. A number of these errors can be corrected by using global replace. However, this is a tedious process best done in the office where the data can be checked and decisions about what has to be saved and what doesn’t can be made.

Be sure to take a look at the error checking list that KCW provides. However, this list is merely the tip of the iceberg for checking for errors. Where you have combined fields, for example problem code, reason closed, and office in one field, the chances for errors become even greater.

Part II: Preparing for the Conversion

Once you have decided to have your data converted, you are faced with several issues.

1. Is your data good enough?

You may find that your data is in such bad shape that it is better to start over than to spend time cleaning up a database that is clearly a lost cause. This is a difficult decision to make, in light of the importance of these cases to conflict checking, reports, etc. However, you can always do a partial conversion and keep your old database running on an old computer, if you ever need it. Remember though, a database with wrong or missing data is worse than useless, it’s misleading.

2. What type of conversion do you want to do?

There are four levels of data conversion:

i. No Conversion (start fresh) - This is when there is no current computer database or the data is so bad as to be useless.

ii. Convert for Conflicts - When the data is in very bad shape, but the client name, adverse party name, and the case number can be salvaged.

iii. Conflicts and Open Cases - The only reason to do this rather than a complete conversion is that the data is in very bad shape and it isn’t worth cleaning up anything but the currently open cases.

iv. Complete Conversion - This is the most common choice, where we preserve the institutional memory of the office.

Besides these main levels, there are a multitude of variations, such as converting all the regular cases but excluding the rejected ones.

KCW usually recommend against converting the time keeping information because of the difficulty of equating the different codes. An alternative is to group the time spent on cases and add them as a single record, if needed. For example, ten slips could be grouped into one with 10.5 hours and the comment section could say, "Time carried over from old database."

3. When do you shut down and go to paper?

There has to be a cut off point after which the data that is put into the computer and gathered from the clients will no longer be converted. It is almost impossible to do a conversion for the information about clients who came in after the data was sent for conversion. This is especially true where the conversion was extremely complex or required an enormous amount of error checking.

4. Error Checking the Database

KCW has an extensive list of the errors to check for in the database. Be sure to get a copy of this list and use it. There are three basic steps for checking for errors correctly:

i. What are the possible errors?

ii. How do I find them?

iii. How do I correct them once found?

One of the first things to do with the old data is to run it through any error checking built into your old system. Unfortunately, this is often missing from old case management systems.

There is a way to do the error checking using Microsoft Access. The added bonus of doing this checking is that the information and skills you learn in the process will carry over into your using Clients. These steps assume that your database is in a format that can be read by Access, such as Dbase files ending in .dbf and you have Access on your computer.

To use these files, first back them up somewhere safe. Then open up Access and create a blank database. Name this database whatever you wish. What you will see is a database view (a set of tabs saying Tables, Queries, Forms, etc.). These will be blank. In order to use the old files, you must link or attach them. Go to the top-line menu and choose File --> Get External Data --> Link. When the dialog box appears, be sure to choose the file type you’ll be working with from the pull down, otherwise you won’t see a list of the appropriate files, e.g., choosing Dbase files displays files whose names end in a .dbf.

When you select a table and press Link, you will be asked to choose an index. Choose Cancel, and you should get a message that you linked successfully. Repeat this for each of your important tables. When looking at the tables tab, you’ll see a list of what you attached, each table having an arrow next to it (meaning its attached rather than inside the Access database).

Once linked to Access, you can manipulate this data the same as if it was in Access. However, you will not be able to add additional fields. You might want to do this if you have fields that combine information, such as problem code and office, and you want to break them down into two fields. To do this, you would have to choose Import off the Get External Data menu rather than Link.

i. What are the possible errors?

Use the Database Error Checking list provided by KCW. If you eliminated all of these errors, it would be extremely helpful.

ii. How do I find them?

There are numerous ways to find errors in your database. These range from simple to extremely complex. What follows is a list of techniques for checking databases:

a. Sort each column in a table from A to Z and then from Z to A.

Double click on the table you’re checking on the Table tab. This displays it in a spreadsheet-like view. Click on any value under the column you want to error check on, such as the case number. Then press the A to Z button, which sorts the database on the column chosen. It will list all the cases with no case number first, then all the cases where someone hit a space bar before entering the case number, then all the case numbers beginning with numbers, and then those that begin with a letter (upper case first then lower case). Problems usually occur at the beginning or end of the list, or at a border between two values. Repeat this for each column. Type the changes in directly, or use an update query and write a formula to correct a numerous problem type.

b. Group.

Go to the Query tab and press New. Choose Design View and Add the table name you want to use. This gives you a grid with the table and all of its fields located above. Choose Race (or your equivalent value) and double click on it to put it in the grid (or you can put your cursor in the box next to Field and type the name in). Then double click on the field that holds the unique case number (or type the name in). Next press the Group button on the top of the screen (It’s a sigma sign. If you hold your cursor on it, the Tool Tip says Group). Change the pull-down under the case number field to read Count rather than Group. Then press the red exclamation mark button on the top of the screen (Run) to run the query. This gives you a list of each of the values entered for race and the number of times it occurred. Correct any improper values by finding and correcting the row or running an Update query to globally correct a value. You can do this by putting the wrong value on the Criteria line under Race and changing the query back into a normal select query by pressing the Group button. This gives you all the case numbers with that value entered in Race.

c. Run a duplicate report.

Go to the Query tab. Choose New and then Find Duplicates on the Wizard box. Choose the table you want to check, usually the table that holds the client intake sheet. When prompted, enter in the field name for case number for the unduplicated field and choose the next box. Include the client’s name, problem code, date opened, closed and reason closed. The query runs automatically when you complete it. It will find two types of duplicates:

95-1001 John Jones 32 1/1/95 12/31/95 B

95-1001 John Jones 32 1/1/95 12/31/95 B

These are the same cases and you need to delete one.

95-1001 John Jones 32 1/1/95 12/31/95 B

95-1001 Sally Smith 63 8/8/95 10/22/95 N

These are two different cases. Either find the correct case number for the incorrect one or add a D to the end of one of the cases to show it was a duplicate. This will make it different enough not to cause problems (unless case number is used to link to other tables based on case number, then you have a whole world of troubles).

d. Find values not in an existing list.

Start a query using your table as described above. You may need to link to one of Clients tables such as subPcode (this requires Clients tables to be on your computer or network where you can reference them).

Relate the two tables by clicking on your value for problem code, drag it over, and drop it on top of the field named Pcodenum. A line forms between the two tables. Right click on the line so you see a menu that says join properties. Change it to say include all the values from your table and only those values from subPcode where their equal. Double click on Pcodenum so it appears on the grid. On the Criteria line under Pcodenum, put the word NULL (it means blank). Then double click on your field name for problem code and your field name for case number. Run the query by pressing the red exclamation mark button. This will give you a list of all the cases whose problem codes do not match the approved list.

iii. How do I correct them once found?

a. Correct them by typing over the value.

Most of the time you can just type over the old value or correct it on the screen. This is not the preferred way of making changes if there are a large number of changes or the value is dependant on other fields on the record. You also can’t usually do this if your query is based on two tables.

b. Do an Update Query and change the value based on another.

When you choose an Update query, the grid changes so it asks what is the value to update to under each column. If you wanted to change the reason closed from Z to K, under the reason closed column you would put "K" on the Update To line and "Z" on the Criteria line.

c. Do an Update Query and change the value based on a formula.

This consists of doing an Update query but using a formula on the Update To line rather than a value. For example, persons helped in Clients could be calculated by typing [adults] + [children].

d. Do an Update Query based on the values of another table.

In some instances, there are a large number of values that need to be changed and they vary from record to record. A good example of this is where your current database stores the advocate handling a case as initials, and Clients stores it as a staff number. In this instance, you can use an existing table, such as Smember, or construct your own table for other fields. First you join to the Smember table in the Clients database. Then you make sure your staff members are in it with their initials and their new numbers. Then you join the Smember table to your case table based on the linking value. In this example, the advocate field joins to the initial field in the Smember table. You then put the advocate field from your old table on the grid, change to an Update query, and put [Smember]![Snum] on the Update To line. This matches the initials on the record with a value in the Smember table and substitutes the staff members new number for their initials.

5. Mapping the Database

It is usually very helpful to do a database mapping when your database hasn’t been converted by KCW before or you store things in a peculiar way. When you map a database, you list your field name and the Clients field it will be stored in.

For example, what if you have a field called "Code"? What does it hold and where does it go? Is it an important field, and should it be placed in a user defined field? These are all questions that have to be answered. Some of the mapping is obvious. For example, a field called LASTNAME clearly maps to CLNAME in Clients (although in some database tables, last name stores the adverse party name). But what about CODE or TEAM, what do they do, should they be saved, and what is the equivalent field in Clients? How about the fields CALLEDON and INTAKEON. Which is treated as the DOPEN?

This doesn’t mean you need to do this in Access by doing an Append Query and saving the values. It’s as simple as writing a list of all your field names and next to them writing the equivalent Clients values or where else you’d like to store them. You can find the field names for Clients in the back of the Clients manual. This exercise is important for you to understand how Clients works.

6. Troubleshooting

No matter how careful you are, usually all client records do not go into the new database the first time. Usually this is caused by a key violation. This means there is either a duplicate value in a unique field, or a field has a value that violates a validation rule, such as the value Z in the reason closed field. These have to be weeded out before all the data can successfully be converted. In some instances, the information can be moved in and corrected afterwards. Sometimes individual records fail to move in. Some errors cause the whole conversion to fail.

If only part of the records move over, an extremely useful tool is a query that shows which records didn’t. This requires a query which uses your old table that stores case information and the new table, ClientsW, joined on their unique case number. You put the ClientsW field CASENUMBER on the grid and on the Criteria line you type NULL. You then double click on the old table name, and drag and drop all the fields from that table to the grid. When you run the query, only those cases that didn’t move over are displayed. You then go column by column and see if they share anything, such as a funding code, marital status, or problem code. If they do, you check and see if that value is permitted in the ClientsW database.

A Final Caveat

No matter how closely watched your database is and how much time you spent correcting errors, be prepared for the worst. In one instance, a database that was guaranteed to be straightened out took three days on site to convert. Most of the time was spent correcting errors. The advantage with this conversion is that the people sat and watched as KCW found the errors and corrected them so that they understood exactly what is meant when said the database was in bad shape.

Copyright © 2002 All rights reserved Kemp's Case Works, Inc.