Consolidating Tables in FileMaker 8

1. Consolidating Tables in FileMaker 8 MIT FileMaker Users Group 3 November 2006 Kevin Cunningham

2. Why Consolidate? • Efficiencies (scripts, etc.) • Allow for consolidated data design (similar tables -> single) • Security (single location) • Work within Server limits • Easier to distribute/maintain

3. Why Not Consolidate? • Risk of breaking interface • Takes a good deal of time(e.g., 16-32 hours for 12 files) • Extremely tedious manual work • Really need to? (future plans)

4. Preparing to Consolidate 1 • Get FileMaker 8 Pro Advanced • Create initial backup • Normalize naming: • UNIQUE names for all elements • Put TABLE identity in names • Eliminate unused elements: • Esp. layouts, but also fields, scripts, etc. • Streamline where possible: • Use new FM functions (GetNthRecord) • Use modern logic (Case, Else If)

5. Preparing to Consolidate 2 • Gather Data on State of Databases • Run DDR on all files • Print PDFs of Relationship Graphs • Get Design data in useful format • Standardize an account/password set • Reduce hassle of opening files multiple times • Make backup copy of revised set • May want to obtain QuicKeys on Mac to automate steps

6. Preparing to Consolidate 3 • Set order of elements to import • decide up front order of layouts, scripts, value lists, tables, etc. • Decide relationship approach • E.g., use anchor-buoy • Select File to Use as Core • Use file with most layouts and/or relationships (minimize handwork) — see the DDR for guidance • Prepare Working Directories

7. Consolidation Steps 1 • Create Value Lists in core file • Manual (QuicKeys can automate some) • Can supply custom values now • Can’t create field-based values yet • Create Tables/Fields in core file • FM import, cut/paste • Issue: core context of newly created tables • Issue: if custom functions used, do them first? • Option 1: create table names manually, then copy/paste entire field sets • Option 2: import whole tables

8. Consolidation Steps 2 • Create Table Occurrences/Relationships • Manual (no automation) • Create new TOs • Link TOs to form relationships • Optional: Import Data Now • FM import • Can do now or later (useful if value lists are based on fields) • Can consider imported data as temporary (if users still using dbs) or permanent (if offline)

9. Consolidation Steps 3 • Repair Field Definitions • Manual (no automation) • Fix defs based on relationships (lookups, calcs) • Make sure validation by value lists works • Repair Value Lists • Manual (no automation) • Fix value lists based on field data, related data • (Could hold off until VL consolidation phase)

10. Consolidation Steps 4 • Create Layouts with Appropriate Names • Manual (QuicKeys can automate some) • Need to create layouts by name • Don’t need to set layout parts/contents • May want to create standard dummy layout and duplicate/rename, etc., via automation (see later layout steps) • Import Scripts • FM Import • Requires fields/layouts name already in place • Repair scripts that use literal strings • Address scripts for new contexts (can’t assume layout is in “current file”, consider windows) • Address “On open” and “On close” scripts

11. Consolidation Steps 5 • Create Layout Parts • Manual (QuicKeys can automate some) • Create proper parts for each layout • Suggestion: use vertical view of part names • Define part characteristics (e.g., size, bgcolor) • Define layout characteristics (columns, view) • Import Layout Elements • Manual (QuicKeys can automate some) • Prepare original layouts (e.g., upper-left icon) • Copy/paste elements for each layout • Clean up pasted layouts (remove icon)

12. Consolidation Steps 6 • Streamline Scripts • Manual (no automation) • Use parameters, etc. • Consolidate same functions into single script • Streamline Layout Interface Elements • Manual (no automation) • Parameterize buttons to use revised scripts • Find efficiencies across multiple layouts (e.g., graphics elements – do you have a SETTINGS table?) • Consider revised navigation schemes • Consider standardization of look-and-feel

13. Consolidation Steps 7 • Streamline Value Lists • Manual (no automation) • Plan list consolidation • Repoint lists on layouts, field defs, etc. • Consolidate same lists into single list • Eliminate lists from “Other File” (use native value list) • Other cleanup • Manual (no automation) • Repoint file references to current file • Consider how to address context in field defs

14. Consolidation Steps 8 • Implement Security/Access Scheme • Manual (no automation) • Plan account/privset consolidation • Especially consider how to restrict access if previous scheme used “different file” as access control • More important to limit database architecture changes (e.g., have admin password for changes) • Other cleanup • Manual (no automation) • Custom function fixes?

15. Moral • Main Points • Sequence is important (what you do earlier plays out later) • Some steps are more able to be automated than others (some are just manual, it can’t be helped) • No matter how you do it, there is drudgery involved!

16. Thank You