Tech Docs: Importing Data

Importing Data Into Facil

Introduction

Importing existing data into Facil is not a simple task, but many organizations have done it successfully. Person data is the most often imported; it is the simplest and the data you are most likely to have in a suitable format. Some organizations have imported Equipment data, but this is much more complicated.

Requirements

- Importing data into Facil will require a copy of Microsoft Access.

- You must be familiar with Access functions including table definition, importing external data, linking external tables, and the Append query.

- The data to be imported must be in a format that can be read by Access. This includes Access, Excel, most other databases (dBase), "comma delimited" or "tab delimited" files, or a Microsoft Word table. You may need to export data from your current format into a common format before importing it. For example, Access will not read Filemaker data, but Filemaker can export to the dBase (.dbf) format, which Access can read.

- The Facil data file format is Access 2003 (Access 97 in Facil 4.5 and earlier) and can't be changed.  Facil will not work if the data file is converted to a later Access format.  It is possible to work with an older format Access data file with any newer version of Access; just be certain you do not allow Access to convert the data file to a newer format.  Always maintain a backup copy of your original data file.

Things to Consider

Many organizations have found that importing data is not the best choice. There are many things to consider...

- The time it will take to learn to use Access and to work out the many details of the import process.

- The amount of your data. If you only have a few hundred records, the time to import the data will almost certainly be greater than the time to reenter it directly into Facil.

- The quality of your data. This is the biggest issue. Without the controls built in to a system like Facil, data tends to be very inconsistent. Fields may be empty; only a few fields of data may be available compared to the more extensive set of fields in Facil; required data may be missing; the choices in fields may be inconsistent with typos, etc. Facil will NOT accept incomplete or inconsistent data.

For all these reasons, many organizations have determined that it is better to have staff, temp help, or volunteers reenter their data. This has the great additional benefit of reviewing all data for accuracy, bringing data up to date, and identifying missing data.

And a final point to consider - doing the import may delay your startup with Facil. It is often assumed that importing data will speed up getting started with Facil, but this may not be the case. One successful strategy for starting up with Facil is to simply begin using it and enter the required data as it comes up. In other words, simply enter each Person's data as that person comes into contact with your organization. Enter each Project when you first make a Reservation for it; each Organization when you enter a Person who is affiliated with it.

In this way you can start using Facil immediately. Your most commonly used data is entered first and obsolete data is ignored. The data is up to date because you acquire it fresh and is certain to meet all Facil requirements because it is entered through the Facil system.

The Consultant Option

If you have a lot of valid data (thousands of records) to import, it may be worthwhile to hire a professional consultant to do this for you. Generally the volume of the data in each area has little effect on the length of time the process takes, i.e. 10,000 Persons may not take much longer than 1,000. Therefore, the more data you have to import the more cost effective this choice is. You may be able to find a qualified Access consultant locally; possibly a Computer Science major at your local college would be able to assist you at a reasonable cost.

Becker Software may be able to assist you on a consulting basis. Contact us for availability and cost of consulting services.

Basic Concepts of Importing

In order to import the data, your source tables and the Facil tables must be available in the same database. Do not work directly with the Facil data file. Create a new, empty, Access database and link the desired Facil tables to it. Always use a copy of the Facil data file until you have worked out all details of your import and tested it successfully. Import your external data into your new database and work through the process of restructuring your data as necessary and designing your Append query. After you have successfully tested your process while linked to a copy of the Facil data file, then link to your actual "official" Facil data file and perform the final import.

You must match your data to the structure of the data in Facil. For example, in the Facil Person table you will find the following fields (along with others)...

Your data must match this structure. For example, if you have only one field for Name, the data must be divided into Last, First, Middle, Suffix, and Salutation as indicated before it can be imported. This is most easily done in Access. First import your data in its current form, then use Access to modify the structure to match. The field names do not have to match. It is not even necessary for the data types to match, if they can be converted. For example, dates or numbers which are stored as text in your data will be converted automatically by the Append query, IF they are VALID dates or numbers. It is not necessary to have data for every field in Facil. The required fields for Person and Equipment data are specified below in the discussion of those data tables.

After your data is prepared, use an Access Append query to append the data to the appropriate Facil table. The Append query allows you to select fields from your source table and append them to the selected fields in the target table. If the names match, Access identifies the target field automatically. If the names don't match, you can select the target field for each source field. You can ignore the source fields that do not have a matching target. Data types are converted where possible - text fields containing valid number, date, or currency values are converted as needed. You can also use internal Access functions to modify data as it is appended; for example, upper case can be converted to proper case, or substrings of a field can be selected to be appended.

Importing Person Data

The following list shows all the fields in the Person table (tblPerson), updated as of Facil version 5.0. It is not necessary to import data into every field. The required fields are PersonID, Status, PersonType, and LastName.

PersonID: This must be a number and it must be unique - you can't have two Person IDs the same and they can't be blank.

PersonType: Must match values in tlkpPersonType. These are values of your own definition and they can be entered in Facil in the Reference Lists. The values you want to import must exist in tlkpPersonType BEFORE you can import your data.

Suffix: Must be one of the following - Jr. II III Sr. IV

Phone numbers: For all three phone numbers, the number can be in several formats:
(###) ###-#### (complete)
###-#### (no area code)
########## (all digits with area code, no punctuation)
####### (all digits without area code)
Other, inconsistent, formats may not import correctly.

Status: Choices are "Active", "Inactive", and "Suspended". No other choices are allowed and the Status must be filled in. In an Append query, you may simply specify the data to be appended by entering it as the "Field" value in the query grid. For example, enter "Active" for the Field and "Status" as the "Append To" value. Access treats the string "Active" as an expression and sets the value of all Status fields to "Active".

Demographics: Demographic01-20 fields relate to user defined questions in table tlkpDemographicQuestion; values in these fields identify user defined answers listed in table tlkpDemographicAnswer.

Importing Equipment Data

The importing of Equipment data is more complex.

There are two Equipment tables - Equipment Types (tblEquipType) and Equipment Units (tblEquipUnit). The Types are the kinds of items you can reserve and the Units are the individual pieces of equipment. Every Unit is assigned to a Type. This relationship is explained in detail in the Facil Help file.

Every Equipment Unit record is related to an Equipment Type by the field EquipTypeID. The use of an ID (number) instead of referencing the Equipment Type name itself saves space, improves performance, and makes it possible to later revise the Equipment Type name without disturbing the relationship.

Four other fields in the Equipment Unit record are related to Reference Lists (other tables containing the choices). For the fields EquipOwner and EquipStatusCode, the actual value is stored in the Equipment Unit record. For the fields EquipLocID and VendorID, the ID number is stored in the Equipment Unit record.

You can't import your inventory records into the Equipment Unit table until you have determined the Equipment Type and the values for the other related fields. The records in the Reference lists must exist before the related records in Equipment Type can be imported.

Therefore the Equipment Types you want to use must be created in Facil before you enter the records into Equipment Units. However, you may find it easier to temporarily use a single Equipment Type for all Equipment Units. This Equipment Type might be called "Imported Units" and would have a Package inventory type. Then all imported Equipment Unit records would have the Equipment Type ID for "Imported Units". After you have completed the import, you can then use Facil to define your correct Equipment Types and then use the Move function on the Equipment Unit form to move each unit to its correct Type. Once all the units are moved out of the "Imported Units" Equipment Type, that temporary Equipment Type record can be deleted.

Listed below are the fields in tblEquipUnit. The required fields for an Equipment Unit record are Tag Number (TagNum), Description, Equipment Owner (EquipOwner), Equipment Location (EquipLocID), and Equipment Status (EquipStatusCode). For the fields which are related to another table, the name of the table and the linking field are given:

The EquipUnitID is an Autonumber field. This is a number field which is assigned automatically by Access to uniquely identify the record. When you add records to the table using the Append query, the ID numbers will be assigned automatically. These numbers are not reused, even if the record is subsequently deleted. For this reason, if you make several passes at the Append process, you will notice that the ID numbers are higher each time. If you empty the table after a trial run of Appending data, and then compact the database (Tools/Database Utilities/Compact Database), the empty table will be "reset" so that the Autonumbering again begins with 1.

The Tag Number (TagNum) is a required field in the Equipment Unit table. It is used to record the unique number which identifies the equipment in your inventory; often this is called an Asset Number or Property ID Number. The reason for providing this field in addition to the internally used EquipUnitID is that you may have previously existing numbers on your equipment and you will want to continue to use them.

The Tag Number must be filled in for every record and all Tag Numbers must be unique. It must be a number, with no text characters, punctuation, spaces, or leading zeros. If you don't have existing numbers to use for the Tag Number, just number everything in order from 1 up.