FileMaker Pro Conversion, Upgrade, and Consolidation
Part 1 of 2 - Conversion

Articles > FileMaker Pro

By Kevin Hammond, Database Development Services, Inc

This is a two-part article tackling the challenge of converting and consolidating FileMaker Pro .fp3 and .fp5 file types to .fp7 and above. In part one, we cover the FileMaker conversion process. In part two, we cover the process of Consolidating FileMaker Files. If you are already using FileMaker 7 or above, go on to part two

Between the two parts, we will guide you through converting and consolidating a multi-file .fp5 solution while maintaining the functionality of your previous solution. And of course, you’ll be gaining all the latest FileMaker feature enhancements . Once you complete the conversion and consolidation process, no layouts will be visible to the end users, scripts will be hidden, and your menu will be custom-designed to clean up your interface and enhance the user experience (e.g. Report Menu). The end result will be a consolidated, clean, single .fp7 solution from what was once a multi-file .fp5 system. (Depending on your consolidation design, you may actually have a few files, but it will still be greatly improved.)

Planning the FileMaker Upgrade

1. Upgrade, convert, and consolidate files to the latest version of FileMaker
2. Rebuild from scratch in the latest version of FileMaker
3. Upgrade to the latest version of FileMaker as is
4. Evaluate other technologies/solutions.

While everyone has different needs, of the four options, we most often recommend going with the first option. We suggest this route because it costs the least long-term, carries the least amount of risk, and provides the most benefits. However, converting FileMaker Pro from .fp3 or .fp5 format to .fp7+ can be a complex task that poses challenging problems.

Scheduling and Planning
Making the jump from FileMaker 3, 4, 5, 5.5, or 6 to FileMaker 7, 8, 8.5, 9, or 10 involves proper planning. Depending on the complexity of your FileMaker .fp5 system, this can take anywhere from a few days to a few weeks, depending on the number of files, scripts, layouts, relationships, and value lists in your solution.

Tools
We highly recommend these tools to effectively convert your files:

• FileMaker Developer 6 (no longer supported/sold, used to create DDR). The DDR is the used to evaluate the solution. Sample FileMaker Pro Developer 6 DDR.
• New Millennium's MetadataMagic ($200-$700) to clean up file references, find errors in the .fp5 format, and generate a Conversion Issues Report to reference.
• FileMaker Pro Advanced ($499)
• Goya's Base Elements ($500) or BeesWax's Inspector ($400) to clean up solutions in .fp7 format.

Obviously, this can really add up money-wise. If you’d like help, fill out our FileMaker Project Request and we can work with you to get you the best possible solution. To learn more, read about our FileMaker Conversion and Migration Analysis.

1. Preparing for Conversion to FileMaker .fp7
Before you start converting your files, you need to carefully map out your solution. Mapping involves knowing all your files in your current system, their primary relationships, and how you would like to arrange them in your new FileMaker system. This not only leads to a better system architecture, oftentimes, mapping out the files highlights files that are no longer used and can be eliminated .

To understand the main components of any system and guide the solution, we always create an Entity Relationship Diagram (ERD). The ERD becomes the cornerstone of the development process. While the average user can usually skip this part, you must be sure to know your solution to ensure the application is properly designed.

Next, you need to create a DDR in FileMaker 6 Developer. The DDR is a report of the entire system at the file level and helps determine whether to use the hub-and-spoke methodology or to rewrite the entire system. The DDR will be referenced throughout the conversion process. If you do not have FileMaker 6 Developer, this can be skipped, but be sure to know your solution so the application is properly designed.

Passwords
Ensure all your files have the same full-access password. FileMaker 6 and prior is not case sensitive, while FileMaker 7 and higher is case sensitive.

Cleaning up your existing solution
Run your existing solution through MetadataMagic’s Developer Software. MetadataMagic will do three key things for you:

1. Consolidate File References - This is important because FileMaker 5, 5.5, and 6 assign a file reference a unique value each time a file is referenced in a script, relationship, or value list. In essence, here you’ll be consolidating each of these file references before converting. You'll save yourself many hours consolidating this way rather than consolidating manually in FileMaker 7+.
2. Produce an Errors Report - We highly recommend you fix these errors before moving forward. Typical errors include: Fields, layouts, relationships, value lists, and scripts.
3. Produces a Conversion Issues Report - The report tells you the number of possible issues you need to address. This will be very useful once the solution is converted to the latest version of FileMaker. Save this report for future reference

TIP: If your solution is in production and you don't want to do this on a live copy, take the latest backup and do this work and the remainder of the conversion from the backup.

FileMaker Naming Conventions – Best Practices
Ensuring unique names in all files for relationships, layouts, valuelists, and scripts before converting is absolutely essential to a smooth conversion that includes file consolidation. Do this now, not later. It will save you hours of time. For very large solutions, the easiest thing to do is add a prefix to all the relationships, scripts, value lists, and layouts based on file name.

Read more about relationship naming in DB Services’ article, FileMaker Naming Conventions and Development Standards

Relationship Naming
For relationships, we recommend using a standard convention such as adding a suffix to each relationship that have sorts and/or cascade create/delete rules (e.g. clients_INVOICES_sort_cre_del).

TIP: Expect to review all Go to Related steps in scripts or on layouts after conversion. These will be found in the Conversion Issues Report.

Layout Naming
Next, document all layouts visible to the user. This will help ensure you include navigation to the visible layouts either as part of the user interface or in custom menus.

TIP: Generate a list of duplicate layout names and only rename/add prefixes to duplicate layout names.

Script Naming
A good practice here is to generate a list of duplicate scripts and only rename/add prefixes to the duplicate script names. Next, document all scripts visible to the user. This will help ensure that any scripts in the current Scripts menu are included either in layouts or menus. Be sure to review the Scripts that were visible with end users.

Tip: In the pre-FileMaker 7 days, each file was a single table. When you called a script in another file, FileMaker assumed you were now in the context of the file you just called. So, if you called an external file script and the first line was New Record Request, then in the consolidated file in .fp7 format, you would be creating a new record based on where you are at and <em>not</em> in the external file in .fp5. For this same script to work appropriately in a consolidated file in .fp7 format, you need to change the context of the script to have a Go To Layout to the appropriate context before the New Record Request.

MetadataMagic's Conversion Issue Report provides a great starting point for all occurrences of scripts called externally. The external script must have a Go To Layout call present at the beginning of the script.  Also, scripts that call an external script and return must have a Go to Original Layout after executing the external script.

Value lists
Again, ensure unique value list names in all files. A good practice is adding a prefix based on file name/table to each value list. This will allow you to easily know what file the value list originated in in the .fp5 format.

Fields
Update field names to a standard naming convention. We use lowerCamelCase, where key fields begin with an underscore, no spaces in field names, no non-SQL compatible field names, and all non- user editable fields and derived fields begin with 'z', etc.

Best Practices

• Ensure housekeeping fields exist
• Creation/modification date and times
• Creator and modifier names.
• Ensure primary key fields exist, either UIDs or auto-increment fields
• Ensure the fields are using the same naming convention.
  

For more detailed information, please read our FileMaker Pro Naming Conventions and Development Standards document.

Purging (recommended, but optional) - Right now is a good time to eliminate/purge items no longer used rather than carrying around the luggage throughout the conversion . We recommend you run the solution through MetadataMagic each time you purge items to ensure you didn't create any errors. In a worse case scenario, you can easily go to a backup.

2. CONVERTING TO .fp7 (FileMaker 7, 8, 8.5, 9, 10+)
Now that you’ve finished all of the steps above, it’s time to convert all files to FileMaker 7+. That said, we recommend you do the conversion at least in FileMaker 8 or later as early versions of FileMaker 7 handle file conversions differently and do not provide the copy and paste functions now available in FileMaker 8+.

Using the Hub and Spoke method, we select the largest file to serve as the single .FP7 file (largest file of solution is typically based on number of scripts, layouts, relationships). This will save you a lot of time.

Continue to part 2: FileMaker File Consolidation

You may also wish to read FileMaker's Upgrading to FileMaker 8 article.