Articles > FileMaker Pro
By Kevin Hammond, Database Development Services, Inc
Posted on Dec 31, 2007
Converting and consolidating a FileMaker Pro solution from .fp3 or .fp5 format to .fp7 format can be a challenging task for complex solutions. Many organizations are faced with the decision.
- Upgrade to the latest version of FileMaker as is
- Upgrade to the latest version of FileMaker Pro and consolidate files. (rewire)
- Rebuild from scratch in the latest version of FileMaker
- Evaluate other technologies/solutions.
What should you do, there is no simple answer but we have prepared a guide for migrating your FileMaker .fp5 solution to the FileMaker .fp7 format while consolidating your files to take advantage of the new features of FileMaker Pro. This guide is for those who wish to explore the consolidation path (#2 above) which we believe to be the least costly, provides the most benefits, and the least amount of risk.
NOTE: If you have an existing FileMaker Pro 7 or greater solution and wish to consolidate the files then you can skip to tips on consolidating FileMaker files.
Making the jump from FileMaker 3, 4, 5, 5.5, or 6 to FileMaker 7, 8, 8.5, or-9+ involves proper planning. Depending on the complexity of your FileMaker .fp5 system, this can take a few days to weeks depending on the number of files, scripts, layouts, relationships, value lists in your solution.
The Vision
This guide is to take a multi file .fp5 solution and consolidate the files in the latest FileMaker version while maintaining the same functionality. Feature enhancements will naturally follow you once in the latest version.
Imagine no layouts visible to the end users in your new consolidated FileMaker solution. Imagine no scripts visible in the Scripts menu and those needed to be moved into NEW custom menus (ie Report Menu). The end result will be a multi- file .fp5 solution that is now a single .fp7 solution (you may have a few files depending on your consolidation design).
Tools
Some tools we highly recommend to effectively accomplish the conversion process.
- New Millennium's MetadataMagic ($200-$700) to clean up file references, determine errors present in the .fp5 format, and generate a Conversion Issues Report to reference.
- FileMaker Advanced ($499)
- BeesWax's Inspector ($400) or Goya's Base Elements ($500) to clean up solutions in .fp7 format.
- 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.
This can really add up, if you wish to have us help, fill out a project request.
Read about our FileMaker Conversion and Migration Analysis services.
1. Preparing for Conversion to FileMaker .fp7
Work to be done in .fp5 (FileMaker 5, 5.5, 6)
Know your solution, the files and their primary relationships. Many times you can eliminate files that are no longer used.
We always create an ERD (Entity Relationship Diagram) when working on a solution to understand the main components of the system. The ERD is the cornerstone throughout development.
NOTE: If your the average user this can be skipped but know your solution to ensure the application is properly designed.
Create a DDR in FileMaker 6 Developer, this report of the entire system at the file level will be referenced throughout the process. The DDR will help us determine whether to use the hub and spoke methodology OR to rewrite the solution (though we find this more costly up front and involving more risk.)
NOTE: If you do not have FileMaker 6 Developer this can be skipped, but know your solution to ensure the application is properly designed.
Passwords
Ensure all the passwords in all the files are identical in case.
Also ensure all the files have the same full access password.
Cleaning up your existing solution
New Millennium's MetadataMagic
Run your existing solution through MetadataMagic consolidating File References.
Why? In FileMaker 5, 5.5, 6 each time another file is referenced in a script, relationship, or value list behind the scenes FileMaker assigns the file reference a unique value. You want to consolidate these file references before converting. You'll save yourself many hours consolidating them manually in FileMaker 7+.
In addition MetadataMagic will produce an errors report. We recommend you fix these errors NOW before moving forward. Typical errors include fields, layouts, relationships, value lists, and scripts.
MetadataMagic also produces a Conversion Issues Report. Save this report for future reference. The report can tell you the number of possible issues you need to address and will be very useful once the solution is converted to the latest version of FileMaker.
When you plan to consolidate files into a single .fp7 file you need to ensure unique names for layouts, scripts, value lists, and relationships. So take the time up front and ensure unique names in all files for relationships, scripts, layouts, and value lists (very critical later when consolidating the files in .fp7 format).
TIP: If your solution is in production and you don't want to do this on a live copy we understand, take the latest backup and do this work and the remainder of the conversion from the backup.
TIP:
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.
Relationship Naming (ensuring unique relationship names)
You can read our FileMaker Naming Conventions and Development Standards document to further understand the naming conventions. Name things in FileMaker 5-6 now and save yourself time later. In the relationships we recommend using a standard convention such as adding a suffix to each relationship that have sorts and/or cascade create/delete rules (ie clients_INVOICES_sort_cre_del).
Layout Naming (ensuring unique layout names)
Ensure UNIQUE layout names in all files (will be crucial later on).
TIP: Generate list of duplicate layout names and only rename/add prefix to duplicate layout names.
Document all layouts visible to user, you want to ensure that their is navigation to the visible layouts in the interface or chose to do the navigation in the new consolidated FileMaker solution in the interface or custom menus.
Script Naming (ensuring unique script names)
Ensure UNIQUE script names in all files (will be crucial later on).
TIP: Generate a list of duplicate script and only rename/add prefix those duplicate script names.
Document all scripts visible to user, you want to ensure that any scripts in the Scripts menu currently used are in the new consolidated FileMaker solution either via layouts or menus. Review with end users.
WARNING: External Script References and the Problems they Create
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 call 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 not in the external file did 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 (ensuring unique value list names)
Ensure UNIQUE value list names in all files (will be crucial later on).
TIP: Add prefix based on file name/table to each value list, this will allow you to easily know what file the value list originated in .fp5 format.
Fields
Update field names to a standard naming convention. We use lowerCamelCase, key fields begin with an underscore, no spaces, no non-SQL compatible field names, and no user editable fields, derived fields begin with 'z', etc. Read our FileMaker Pro Naming Conventions and Development Standards document for more information.
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.
Purging (recommended, but optional)
The time to eliminate/purge items no longer used is sooner rather than later and carrying around the luggage throughout the conversion/migration. We recommend you run the solution through MetaDataMagic each time you purge items to ensure you didn't create any errors and worse case scenario allow you to easily go to a backup.
2. CONVERTING TO .fp7 (FileMaker 7, 8, 8.5, 9+)
So you finished all of the steps above, now its time to convert all files to .fp7 (FileMaker 7 format) together.
TIP:
We recommend you do the conversion 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+ Advanced.
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.
CONSOLIDATING FILEMAKER FILES
TABLES FIRST
Start the consolidation process by putting all the tables into a single file first. Import all tables from each file into the single .fp7 file solution (requires FileMaker Advanced to copy/paste tables, use FileMaker 8 Advanced or greater). You may wish to add fields for creation and modification timestamp's as they are now available in FileMaker 7+. You may also wish to create a script to import records from the .fp7 files into the main file.
You will have an import.log file created while pasting your tables. Save this log as you will need to fix any errors from lookups to auto-enter calculations that utilize relationships that are not currently in the solution.
ADD RELATIONSHIPS
Create all Relationships (TOGs) from the individual files you are integrating into your new consolidated file, naming the relationships exactly as the individual files relationships are named. Ensure to keep any relationship rules (sort, del, cre) previously present.
IMPORT.LOG
Review the import.log file and Edit fields of all Tables imported that generated errors. You'll find commented out fields (ie. calculations etc). and broken lookups.
ADD LAYOUT NAMES
Create layouts with the exact name of the layout in the individual files you are integrating into NEW single file solution. DON’T COPY/PASTE LAYOUTS YET and name layouts identically to those in FM6. Remember to set layout type (form or list), fixed print margins in pixels to ensure identical layout setups.
ADD VALUELISTS
Add ALL Value lists used in .fp5 files to single .fp7 file. NOTE: You cannot import value lists in FileMaker as of version 9, that would save us time here!
IMPORT SCRIPTS
Import all Scripts from each file. Look for import errors (they will be saved to the import.log file), typically from identical script names or relationship names not being identical. Rename the scripts as needed, ensure the relationship names are correct and then delete the scripts you imported with errors and repeat the import until ZERO errors are present upon import.
COPY AND PASTE LAYOUTS
Copy and Paste layouts from each file into the appropriate layout. Make sure any buttons on the layout are now linked to the correct scripts and fields are linked to the correct tables/relationships. THIS IS WHERE IT ALL COMES TOGETHER and why it is so critical to ensure unique names for relationships and scripts. The only bad news is tab order will have to be reset for these layouts.
Tab Orders
Set tab order for each layout that was copied/pasted into the consolidated file(s). Update Date Fields to use drop down calendar.
EXTERNAL SCRIPTS NOW INTERNAL SCRIPTS
Consolidate all external script references into correct file and select the same script name. The MetadataMagic Conversion Issue Report will help you as well.
Review MDM Conversions Issues Report
-Review/Update Go to Related Record calls to ensure you are in the correct context/layout.
-Review all New Record, Duplicate Record, and Set Field calls to ensure a commit record request is present to prevent record locking.
-Any field validation may need to be reviewed as in .fp7 field validation occurs when any field is modified and the field may not be present on the layout.
TEST, TEST, TEST
Script consolidation rewrite and testing of solutions is majority of development (ie Print Scripts, consolidating navigation). 80% of the WORK is REWRITING/TESTING.
Custom Menus (Optional)
Create Custom Menus, removing Delete All Records from default menu set. Customize a menu set for each general area, renaming the New Record to New Contact for example and then reassigning each layout to their appropriate custom menu.
DDRs and Third Party Developer Tools (Optional)
Run DDRs and tools like Inspector or Goya's Base Elements to fix any errors that exist.
Their are many different ways to upgrade a FileMaker 5 solution to the latest version of FileMaker. The consolidation approach above we find to be least costly and presents the least amount of risk to implement for all parties. The true question is now that your in the latest version what do you plan to do with your solution? There is a world of possibilities that exist in FileMaker 9+ that were not present in FileMaker 5.
You may also wish to read FileMaker's Upgrading to FileMaker 8 article.
|
