This lesson requires Colleagues.fp7 and FacultyStaff.fp7.

Many times data in older databases we must use has been stored in a flat file style of database. But relational databases provide expandability and more flexibility. So we are often charged with the task to convert older data solutions to new relational structures. This requires using import and export functions to import data from the flat file database to the new relational database often involving multiple procedures for different data groups.

1. Open the sample file Colleagues.fp7. Notice that in this database there is one record per person and a field for the home phone and a field for the work extension. If we wish to record a cell phone number or a fax number we would need to define a new field for each data type we wish to record. If this information is needed on existing report layouts we would need to edit each layout each time a new field is added. However, recording phone numbers in a related table with a field to specify the data type provides the ability to record an unlimited number of phone number each with their own type. So, we begin by creating a related table in the file FacultyStaff.fp7. Select File -> Define -> Database and select the Tables tab. Click on the New Tables icon and name the new table Phone Numbers. Now click on the Fields tab and create a text field for Employee ID, a text field for phone number, and a text field for phone type. Then click OK. To take the phone number information from the "flat" file Colleagues.fp7 and import it into the related table Phone Numbers in the FacultyStaff.fp7 file will require two import steps to transfer both types of phone numbers.

2. Select the layout Phone Numbers. Now choose File -> Import Records ->File and select the sample file "Colleagues.fp7". Select View by: Matching Names. This will automatically line up any field names that are the same in both databases.

3. Drag the field "Phone Number" in the right list of field names to a position directly opposite "Home Phone" and click the circle between the fields once to change the circle to a black arrow. Make sure Employee ID is directly across from the Employee ID field in the Colleagues file and a black arrow connects the fields in both files. Make sure all other "connection" indicators are just circles.

4. Click Import. Now place your cursor in the field "Phone Type" and type "Home Phone". With your cursor still in the "Phone Type" field, select Records -> Replace Records and click the button labeled "Replace".

5. Now select File -> Import Records again and once again select the sample file "Colleagues.fp7". Drag the field "Phone Number" in the right list of field names to a position directly opposite "Work Ext" and click the circle between the fields once to change the circle to a black arrow.

6. Click Import. Now place your cursor in the field "Phone Type" and type "Work Phone". With your cursor still in the "Phone Type" field, select Records -> Replace Records and click the button labeled "Replace". You now have two phone records for each individual.

7. Now Select File -> Define -> Database and the Relationship tab. Select the field "Employee ID" in the table Phone Numbers for the match field and drag it over on top of the field Employee ID in the FacultyStaff table. Double click the relationship line and Check the "Allow creation of records in this table" and "Delete related records in this table" checkbox and click "OK" and "OK".

8. Choose Layout #1 from the layout selection list and select View -> Layout Mode. Click on the Portal Tool and draw a rectangle beneath the fields on the default layout. In the Define Portal dialog box select the table Phone Numbers check the "Allow Deletion of Portal Records" and the "Show Vertical Scroll Bar" checkboxes and click OK. Then select the fields "Phone Number" and "Phone Type" from the related table "Phone Numbers".

9. Select View -> Browse Mode.

Now you see both phone numbers displayed in a portal that will also accept other entries like Emergency Phone, Contact Phone, Parents Phone, etc. without having to create a new field in the database for every phone type.

 

COE Techweb

COE Home

UO Home