VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources]

Consolidating Multiple Related Files

A database solution with one or more smaller databases (such as lookup tables for Zip codes) is a prime candidate to be combined into one file with several related tables. You can convert the largest (main) database using the conversion procedures discussed earlier. Once you've done that, you create new tables within the main database that can hold imported data from the old lookup files.

In this example we add a Zip code lookup table to a sales database and import the records from the old file.

To consolidate related files into one new database

1 .

Convert the main database file to be recreated into FileMaker 7.

2 .

In the converted file, choose File > Define > Database (Control+Shift+D/Command+Shift+D). In the Define Database dialog box, click the Tables tab.

3 .In the Table Name box, type a name for the new table. (We use Zip Codes.). Click Create (Figure 15.13).

4 .Click the Fields tab. Type the first field name for the new table. (Use the same field names as the old file.) Click Create (Figure 15.14).

5 .

Repeat step 4 for all of the fields to match the old database. Be sure to match field options such as indexing, auto-enter, and validation. Click OK to finish.

6 .

Choose View > Layout Mode (Control+L/ Command+L). Choose Layouts > New Layout/Report (Control+N/Command+N).

7 .In the New Layout/Report dialog box, choose the new table in the "Show records from" drop-down list (Figure 15.15).

8 .Type a name for the layout and choose a layout type. In this case we'll choose Columnar list/report. Click Next (Figure 15.16).

9 .

In the Choose Report Layout, leave the setting at Columnar list/report. Click Next.

10 .

In the Specify Fields dialog box, click Move All to place all of the fields on the layout. Click Next (Figure 15.17).

11 .In Sort Records, choose the sort order desired by double-clicking on the fields on the left. (You can skip this step if sorting doesn't matter to you.) Click Next.

12 .In Select a Theme, choose a layout theme or set it to Default. Click Next.

13 .

In the Header and Footer Information dialog box, you can just click Next, since neither is necessary for this task.

14 .In Create a Script for this Report, leave "Do not create a script" selected. Click Next. Under "You are finished," choose View thereport in Preview Mode and click Finish.

15 .Choose File > Import Records > File. In the Open File dialog box, choose the converted file with the data for this new table.

16 .In the Import Field Mapping dialog box, match the field names from both files. (If you created the new table with the same names, they should be correctly aligned by default.) Click Import (Figure 15.18).

Once you have recreated an external file in a new table, you must update any relationships that used the old file to use the new table.

To manually change converted files

1 .Choose Define > Database (Control+Shift+D/Command+Shift+D). In the Define Database dialog box, click the Relationships tab.

2 .

Double-click the = icon that connects the table from the older external file. In the Specify Table dialog box, choose Current File from the File: drop-down list if it is not already selected. Click the new table in the list, and provide a name in the Name of Table Occurrence. Click OK (Figure 15.19).

All references (like lookup fields) that had been using this external file relationship are now updated to use the new table.

If your old database had scripts with steps that called scripts to navigateto another database that you have recreated as a table, you'll have to change the script to go to another layout in the current database.

To update scripts in converted files

1 .

In the main database, choose Scripts > ScriptMaker (Control+Shift+S/ Command+Shift+S).

2 .In the Define Scripts dialog box, double-click the script that calls the old external script.

3 .In the Edit Script dialog box, click the step that calls the external file. Click Clear (Figure 15.20).

4 .In the script step list on the left, double-click Go to Layout.

5 .

In Script Step Options, choose the layout for the internal table from the Specify drop-down list (Figure 15.21). Click OK twice to finish.

Tips

To save time when converting large files, skip index creation. The conversion will go considerably faster. Indexes will be recreated automatically as users access data with Finds.

Once you've replaced all old relationships to external files with new relationships to internal tables, it's a good idea to delete references to the old external file in File > Define > File References. Cleaning up makes it easier to see and troubleshoot relationships and helps to uncover hidden references to the old file.

Status Functions to Get Functions

What were called Status and Text functions in previous versions of FileMaker are now called Get functions. When converting a database, all instances of old Status and Text functions are updated to the corresponding Get function.

Here are some of the major old functions with their new syntax.

Old

New

TextTo Date

GetAsDate

TextToNum

GetAsNumber

TextToTime

GetAsTime

NumToText

GetAsText

DateToText

GetAsText

TimeToText

GetAsText

Status(CurrentDate)

Get(CurrentDate)

Status(CurrentTime)

Get(CurrentTime)

Status(CurrentError)

Get(LastError)

Status(CurrentMessageChoice)

Get(LastMessageChoice)

Status(CurrentMode)

Get(WindowMode)