FMPro7 Importing Records and Converting an Externally-Related Database to a new Internal Table

Importing Records and Converting an Externally-Related Database to the new Internal Tables Model

An organization often has data stored in other databases and file formats. You can use the Import Records command in the File menu to add data stored in many file formats to a Filemaker Pro database. Here at the University we have a wealth of information about our students and faculty stored in our Oracle-based Banner datawarehouse. But, program-specific information is often tracked in local databases created with Filemaker Pro. Using the Save Results function in our BI Query datawarehouse software, we can export data into a tab delimited data file with a .QRD extension. To learn how to export data from Banner, review my example at: http://interact.uoregon.edu/techweb/Filemakermail.html Filemaker Pro can open a .QRD file but, whether you have saved the column headings in BI Query or not, Filemaker typically ignores the column headings and assumes they are information in the first record. You can use the first record to help you match fields then check the Don't import first record checkbox to exclude the field names from the import process. An alternative to avoid this problem would be to open the .QRD file in Excel then save it as an Excel worksheet. Filemaker can then use the worksheet column headings as field names to match with database field names for import procedures.

1. Open the "Colleagues.fp7" sample file. Choose Records -> Show All Records. Note: This is an important step. Only the records in the found set will be updated. Select File -> Import Records -> File. Choose the sample file "Myaddressbook.xls".

2. In the "Import Field Mapping" dialog box, Select "Update Matching Records In The Current Found Set". Then select the "Last Name" field in the list of fields on the right and, holding your mouse button down, drag it to a position directly opposite the field "Last Name" in the field list on the left. Click twice on the black arrow until the symbol becomes a two sided arrow. This will set "Last Name" to be a match field.

3. Now select the "First Name" field in the list of fields on the right and, holding your mouse button down, drag it to a position directly opposite the field "First Name" in the field list on the left. Click twice on the black arrow until the symbol becomes a two sided arrow to make "First Name" a match field also.

4. Select "Email address" from the list of fields on the right and drag it to a position directly opposite the field "E-mail address" in the field list on the left. Leave the black one way arrow as is. Single click on the rest of the black arrows to deselect these fields from the import process.

If you wish to convert a Filemaker Pro 5 or 6 solution to the new internal table format of Filemaker Pro 7, you will need to determine which file you wish to use as the primary parent file. Then open each file in the solution and convert them to Filemaker Pro 7. This is necessary to enable you to import data from them into the new Filemaker Pro 7 parent file.

Then you must open the primary parent file and select File -> Define -> Database and choose the Tables tab. Next you would click on the New Table icon and name the table to match one of the original related files. Then you would click on the Fields tab and create the fields that were in the original related file.

You will need to create the fields by field type order because calculated fields and summary fields depend on text, number, or date fields for their calculations. Begin by creating all text, number, date, or container fields. Then create all calculated fields except fields using the GetSummary function like Percent calculations. Next, create all summary fields. Then, lastly create all calculated fields that use the GetSummary function. Be sure to name the fields exactly the same as they were in the original file to avoid having to remap fields on transferred layouts.

Filemaker Pro 7 automatically creates a layout with the table name containing all the fields in that table.

Next, click on the Relationships tab and click on the Match field and drag it over on top of the appropriate Match field in the Primary File Table. Double click on the relationship line or connection box and check the appropriate boxes to enable you to create records in the child table if you are going to be using a portal to create and access records in the child table from the parent table. Then check the delete related records checkbox if the records in the child table become orphaned and irrelevant if the master record in the parent table is deleted.

Next, check the original related file for any value lists. Recreate these value lists in the new parent file with the exact name as they had in the original file. This will ensure that field format will transfer correctly in copied layouts.

You are now ready to recreate any layouts that were needed from the original related file. Open the original related file, select View-> Layout Mode and choose Edit -> Select All then press Control-C (or click Edit-> Copy) on the first layout you wish to transfer. Then go back to your new parent file. Be sure you are on the default layout for the new related table (This will ensure that the correct table is assigned to the new layout) and in Layout Mode select Layout -> New Layout and choose a blank layout. . Size the header, body, and footer sections to approximate the original layout. If the original layout included any subsummary or grand summary parts, add those parts to the blank layout. You should now be ready to press Control-V (or Edit->Paste) to copy the original layout objects onto the new layout. As long as all the fields exist in the new related table the fields should automatically map to the new table's fields. As long as you have recreated all the original value lists with the same names the fields requiring drop-down lists, radio buttons, or checkboxes should also be properly formatted.

Once all of the layouts have been transferred you are ready to import any scripts that were in the original related file. This procedure is conducted last because scripts refer to both fields and layouts in their actions. In the new parent file, select Scripts->Scriptmaker and click the Import button. Browse to and select the original related file. Check the checkboxes in front of the scripts you wish to import and click Import.

To complete the conversion process, you would select the layout containing the new fields and select <b>File -> Import Records -> File </b> and select the converted related file. Then you would follow the import procedure described above to match fields and import data from the original related file.

When you create the new table, Filemaker Pro 7 automatically creates a layout with the table name containing all the fields in that table.

Then you would need to click on the Relationships tab and click on the Match field and drag it over on top of the appropriate Match field in the Primary File Table. To complete the conversion process, you would select the layout containing the new fields and select File -> Import Records -> File and select the converted related file. Then you would follow the import procedure described above to match fields and import data from the original related file.

Filemaker Pro 7 can function normally with externally-related files so why should you consider converting to an internal structure?

- Using internal tables enables you to create value lists only once and use them across all tables in the solution.

- Using the new bidirectional relationships between internal tables eliminates the need to create reciprocal relationships in external files.

- The new file architecture provides many different relationship types, including "equals", "greater/less than", etc.

- to take advantage of industrial-strength privilege sets, which define levels of access to a database file. A large number of options can be set to limit database access: which layouts are viewable, which menus are available, whether printing is allowed, which tables are accessible, or which records, or fields within a file.

If you have an RSS feed that would be of interest
to faculty and students at the College of Education
please contact Mary Harrsch.