This is a two-part article tackling the challenge of converting and consolidating FileMaker Pro .fp3 and .fp5 file types to .fp7 and .fmp12. In part one, we cover the FileMaker conversion process. In part two, we cover the process of FileMaker File Consolidation. If you are already using FileMaker 12 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 .fmp12 solution from what was once a multi-file .fp3, .fp5, or .fp7 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 .fmp12+ 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, 10, 11, or 12 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 used to evaluate the solution.

• 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 Base Elements ($499) or Beezwax Inspector ($495) to clean up solutions in .fp7 or .fmp12 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.

1. Preparing for Conversion to FileMaker .fp7 or .fmp12

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 better system architecture, but 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 are not case sensitive, while FileMaker 7 and higher versions are 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.

Purging

Right now is a good time to eliminate/purge items no longer used rather than carrying around the luggage throughout the conversion. Identifying the relationships to purge is a top priority as we like to rename the table occurrences to a single naming convention. We recommend you run the solution through MetadataMagic or BaseElements each time you purge items to ensure you didn't create any errors and identify new items to purge. In a worst case scenario, you can easily go to a backup. Do the purging before the renaming to reduce your workload. Make sure you get approval before purging layouts and scripts as sometimes they are used by developers only.

TIP: When purging you should also look for items used in indirection, referencing an item by name instead of by the element's internal id before purging.

Functions

• DatabaseNames Evaluate( ) FieldBounds( ) FieldComments( ) FieldIDs( ) FieldRepetitions( ) FieldStyle( ) FieldType( ) GetField( ) GetNextSerialValue( ) LayoutObjectNames( ) RelationInfo( ) ValueListItems( ) Lookup( ) ExecuteSQL( ) Get ( LayoutName ) Get ( LayoutTableName )

Script Steps

• Set Field by Name[ ] Go to Layout[ ] (By Name option) Go to Related Record[ ] (By Name option)

Read more about Indirection at Goyas' BaseElements, Unreferenced and Indirection.

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 Standards.

Relationship Naming

For relationships, we recommend using a standard convention such as adding a suffix to each relationship that has sorts and/or cascade create/delete rules (e.g. client__INVOICE__srt_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 are visible to 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 or .fmp12 format, you would be creating a new record based on where you are at and not in the external file in .fp5. For this same script to work appropriately in a consolidated file in .fp7 or .fmp12 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 the file name/table to each value list. This will allow you to easily know what file the value list originated 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.

2. Converting To .fp7 (FileMaker 7, 8, 8.5, 9, 10, 11)

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 in FileMaker 11 as early versions of FileMaker like version 7 handle file conversions differently and do not provide the copy and paste functions now available in FileMaker 8+.

3. Converting To .fmp12 (FileMaker 12, 13, 14, 15, 16, 17, 18, 19+)

Read the FileMaker's Knowledge-Based Article Converting older FileMaker Databases to the .fmp12 format. This article also includes FileMaker Trial Download Links that you will need to upgrade from the .fp5 format. Warning: It will be much easier to download and install the older versions on Windows vs Macs.

Are you looking to consolidate the files? We commonly use the Hub and Spoke method, to consolidate the files to the largest file to serve as the single.FMP12 file. The largest file of solution is typically based on the number of scripts, layouts, relationships. This will save you a lot of time.

Continue to Upgrading FileMaker Pro, Part 2 - File Consolidation.